From: | "Darwin O'Connor" <doconno(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Plan selection based on worst case scenario |
Date: | 2024-05-30 01:03:28 |
Message-ID: | CAAaOdMvZiqixW2Bb821BbQuLWu4+_qdbBvTtE4Ka5aQHb9+4Jw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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)
Indexes:
"trackstopscurr_0f" UNIQUE, btree (trackid, "time"), tablespace "new"
"trackstopscurr_1" btree (trackid, stopid), tablespace "new"
"trackstopscurr_2" btree (stopid, "time")
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2024-05-30 01:44:45 | Re: Plan selection based on worst case scenario |
Previous Message | Tom Lane | 2024-04-21 15:08:04 | Re: Extremely slow to establish connection when user has a high number of roles |