I have been having an ongoing problem for years with PostgreSQL selecting very poor plans when running queries. It does things like doing a table scan of gigabyte size tables to generate a hash table rather than use a suitable index.
When I disable enough features that it generates a sensible plan I notice that the low range of the total cost is many orders of magnitude lower while the high range is higher then the slow plan it originally chose. This suggests PostgreSQL is choosing a plan based on the high end cost rather than the median cost.
Is there a PostgreSQL setting that can control how it judges plans?
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;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=37357.45..37357.45 rows=1 width=16) (actual time=2667.674..2694.047 rows=1 loops=1)
-> Sort (cost=37357.45..37357.45 rows=1 width=16) (actual time=2667.673..2694.045 rows=1 loops=1)
Sort Key: t."time" DESC
Sort Method: top-N heapsort Memory: 25kB
-> Gather (cost=1182.60..37357.44 rows=1 width=16) (actual time=387.266..2692.733 rows=4027 loops=1)
Workers Planned: 2
Workers Launched: 2
-> 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
(19 rows)
transsee=# set enable_sort TO false; SET
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;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1.15..61088.32 rows=1 width=16) (actual time=0.076..0.076 rows=1 loops=1)
-> Nested Loop (cost=1.15..61088.32 rows=1 width=16) (actual time=0.076..0.076 rows=1 loops=1)
-> Index Scan Backward using trackstopscurr_2 on trackstopscurr t (cost=0.57..19552.59 rows=4833 width=14) (actual time=0.021..0.032 rows=5 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.008..0.008 rows=0 loops=5)
Index Cond: (id = t.trackid)
Filter: ((a = 0) AND ((route)::text = '501'::text))
Rows Removed by Filter: 1
Planning Time: 0.229 ms
Execution Time: 0.091 ms
(10 rows)
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=37357.45..37357.45 rows=1 width=16) (actual time=2667.674..2694.047 rows=1 loops=1)
-> Sort (cost=37357.45..37357.45 rows=1 width=16) (actual time=2667.673..2694.045 rows=1 loops=1)
Sort Key: t."time" DESC
Sort Method: top-N heapsort Memory: 25kB
-> Gather (cost=1182.60..37357.44 rows=1 width=16) (actual time=387.266..2692.733 rows=4027 loops=1)
Workers Planned: 2
Workers Launched: 2
-> 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
(19 rows)
transsee=# set enable_sort TO false; SET
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;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1.15..61088.32 rows=1 width=16) (actual time=0.076..0.076 rows=1 loops=1)
-> Nested Loop (cost=1.15..61088.32 rows=1 width=16) (actual time=0.076..0.076 rows=1 loops=1)
-> Index Scan Backward using trackstopscurr_2 on trackstopscurr t (cost=0.57..19552.59 rows=4833 width=14) (actual time=0.021..0.032 rows=5 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.008..0.008 rows=0 loops=5)
Index Cond: (id = t.trackid)
Filter: ((a = 0) AND ((route)::text = '501'::text))
Rows Removed by Filter: 1
Planning Time: 0.229 ms
Execution Time: 0.091 ms
(10 rows)
Indexes:
"trackstopscurr_0f" UNIQUE, btree (trackid, "time"), tablespace "new"
"trackstopscurr_1" btree (trackid, stopid), tablespace "new"
"trackstopscurr_2" btree (stopid, "time")
"trackstopscurr_0f" UNIQUE, btree (trackid, "time"), tablespace "new"
"trackstopscurr_1" btree (trackid, stopid), tablespace "new"
"trackstopscurr_2" btree (stopid, "time")
