Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT

[Rob Sargent <robjsargent@xxxxxxxxx>]

On 12/6/21 11:02 AM, Alan Hodgson wrote:

On Mon, 2021-12-06 at 10:19 -0700, Rob Sargent wrote:

To be clear, is it the devs or the ORM that's adding the ORDER  and the 

LIMIT?  I'm betting on devs.  Do they need the smallest id (first

occurrance?) or do they need data common to all 5096 entries (Name?) and

any record will do?.  For the former they might be better off asking for

just the attributes they need and for the latter you need to provide an

option which gets them that single record.  Of course, If they have the

"smallest id" in hand they should request that.

That assumes I could figure what bit of ORM code is generating this, talk to them, and then get them to actually think about what data they're looking for and it's impact on the database. :/ Given my 25 year track record with devs, I'm thinking of that as plan B. Hopefully though if they're looking for something common to all the records they would look at the parent table instead.

I do expect the dev actually specified the order/limit for some reason.

    Until you know what they're after it's hard to make the correct
    adjustment.  Another index is more over-head and may not be
    necessary or even the best solution for getting that one tuple
    they're after.

Thank you for the suggestions.

    Up thread you hoped for a general solution.  The devs writing better
    queries is your best bet.  
    If you cannot or choose
      not to talk with the devs or at least their manager you'll be chasing
    these for a long time.  Those dealing with ORM code are server-side:
    they are supposed to be the smart ones! Maybe they can be taught.