On Thu, 30 May 2024 at 13:03, Darwin O'Connor <doconno@gmail.com> 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
anothertable 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
tracktripr 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)
(actualtime=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.653rows=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.175rows=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
