On Thu, 30 May 2024 at 13:03, Darwin O'Connor <doconno@xxxxxxxxx> wrote: > Is there a PostgreSQL setting that can control how it judges plans? There's nothing like that, unfortunately. > Here is a recent example of a query that finds the last time at a stop filtered for a certain route it has to look up another table to find. PostgreSQL initially chose the plan that cost "37357.45..37357.45" rather than the one that cost "1.15..61088.32". > > transsee=# explain analyze select t.time+coalesce(t.timespent,0)*interval '1 second' from trackstopscurr t join tracktrip r on r.a=0 and r.route='501' and r.id=t.trackid and t.stopid='4514' order by t.time desc limit 1; > Limit (cost=37357.45..37357.45 rows=1 width=16) (actual time=2667.674..2694.047 rows=1 loops=1) > -> Nested Loop (cost=182.60..36357.34 rows=1 width=16) (actual time=381.913..2659.412 rows=1342 loops=3) > -> Parallel Bitmap Heap Scan on trackstopscurr t (cost=182.03..19048.63 rows=2014 width=14) (actual time=380.467..1231.788 rows=8097 loops=3) > Recheck Cond: ((stopid)::text = '4514'::text) > Heap Blocks: exact=8103 > -> Bitmap Index Scan on trackstopscurr_2 (cost=0.00..180.82 rows=4833 width=0) (actual time=382.653..382.653 rows=24379 loops=1) > Index Cond: ((stopid)::text = '4514'::text) > -> Index Scan using tracktrip_0 on tracktrip r (cost=0.57..8.59 rows=1 width=4) (actual time=0.175..0.175 rows=0 loops=24290) > Index Cond: (id = t.trackid) > Filter: ((a = 0) AND ((route)::text = '501'::text)) > Rows Removed by Filter: 1 > Planning Time: 0.228 ms > Execution Time: 2694.077 ms The problem here is primarily down to the poor estimates for the scan on tracktrip. You can see that the Nested Loop estimates 1 row, so therefore the LIMIT costing code thinks LIMIT 1 will require reading all rows, all 1 of them. In which case that's expected to cost 36357.34, which is cheaper than the other plan which costs 61088.32 to get one row. If you can fix the row estimate to even estimate 2 rows rather than 1, then it'll choose the other plan. An estimate of 2 rows would mean the total cost of the best path after the limit would be 61088.32 / 2 = 30544.16, which is cheaper than the 36357.34 of the bad plan. You could try ANALYZE on tracktrip, or perhaps increasing the statistics targets on the columns being queried here. If there's a correlation between the "a" and "route" columns then you might want to try CREATE STATISTICS: CREATE STATISTICS ON a,route FROM tracktrip; ANALYZE tracktrip; David
