Re: [PERFORM] Cheaper subquery scan not considered unless offset 0

There is actually another separate issue here apart from that the planner obviously choosing the wrong plan as
originallydescribed 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
appliedin 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
Removedby 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.20rows=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/Bdoesn't seem to account for the filter in the HasAggregate node (estimated rows=22285). This looks like a bug. 



--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance