From: | Benjamin Coutu <ben(dot)coutu(at)zeyos(dot)com> |
---|---|
To: | David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> |
Cc: | postgres performance list <pgsql-performance(at)postgresql(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Subject: | Re: Cheaper subquery scan not considered unless offset 0 |
Date: | 2017-10-29 14:41:20 |
Message-ID: | 20171029144121.7C7CE5FB08@mx.zeyos.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
There is actually another separate issue here apart from that the planner obviously choosing the wrong plan as originally described in my last message, a plan it knows to be more expensive based on cost estimates.
Take a look at the way the filter condition is treated differently when estimating the number of returned rows when applied in different nodes.
Queries A/B:
-> Finalize HashAggregate (cost=32879.78..33102.62 rows=22285 width=12) (actual time=450.724..458.667 rows=15521 loops=1)
Group Key: b.item
Filter: (sum(b.amount) >= '1'::double precision)
Rows Removed by Filter: 48277
-> Gather ...
Query C:
-> Subquery Scan on c (cost=32768.35..33269.76 rows=7428 width=12) (actual time=456.591..475.204 rows=15521 loops=1)
Filter: (c.stock >= '1'::double precision)
Rows Removed by Filter: 48277
-> Finalize HashAggregate (cost=32768.35..32991.20 rows=22285 width=12) (actual time=456.582..468.124 rows=63798 loops=1)
Group Key: b.item
-> Gather ...
Interestingly enough the subquery scan with query C correctly accounts for the filter when estimating rows=7428, while A/B doesn't seem to account for the filter in the HasAggregate node (estimated rows=22285). This looks like a bug.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2017-10-29 14:58:00 | Re: Cheaper subquery scan not considered unless offset 0 |
Previous Message | Benjamin Coutu | 2017-10-29 13:17:19 | Re: Cheaper subquery scan not considered unless offset 0 |