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

On Mon, 6 Dec 2021 at 18:03, Alan Hodgson <ahodgson@lists.simkin.ca> wrote:
...
> The table has nearly 29 million records. 5069 of them match shipment_import_id = 5090609. There is an index on
shipment_import_id,which the planner happily uses without the LIMIT specifically. Yet with it the query planner will
alwaysdo something like:
 
>
> # explain 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;
 
>                                                         QUERY PLAN
>
-----------------------------------------------------------------------------------------------------------------------------
> Limit  (cost=0.44..873.35 rows=1 width=243)
>   ->  Index Scan using shipment_import_records_pkey on shipment_import_records  (cost=0.44..5122227.70 rows=5868
width=243)
>         Filter: (shipment_import_id = 5090609)
> .. which takes minutes.

Can you post an explain analyze? To me it seems like the planner
thinks shipment_import_id is randomly distributed and the table is
well correlated with it's PK, so scanning it for the first id should
be fast.

But from the names of the field you may have correlation between
shipment_import_id and id hidden somewhere ( like they are two serial
growing together, you query for the latest shipment ids and it scans
all the table ). An explain analyze should show that ( or three, one
for that shipment import id, one for 1, one for a really big one )

> Just wondering if there's a knob I can turn to make these more likely to work without constantly implementing
workarounds?

You may try a composite index.

Francisco Olarte.