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

On 12/6/21 10:02, 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.

Maybe I'm silly, but why is asking them a Plan B?

> 
> Thank you for the suggestions.


-- 
Adrian Klaver
adrian.klaver@aklaver.com