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

On 12/6/21 10:03 AM, Alan Hodgson wrote:
> I keep running into problems like these:
>
> Devs are using an ORM. It really likes to produce queries like:
>
> SELECT "shipment_import_records".* FROM "shipment_import_records" 
> WHERE shipment_import_records"."shipment_import_id" = 5090609 ORDER BY 
> "shipment_import_records"."id" ASC LIMIT 1;
>
> I don't know why they do this. Usually it's more like 50 for 
> pagination which make more sense. But for whatever reason this keeps 
> coming up.
>
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.