public inbox for [email protected]  

help / color / mirror / Atom feed

From: Benjamin Coutu <[email protected]>
To: David Rowley <[email protected]>
Cc: postgres performance list <[email protected]>
Cc: Tom Lane <[email protected]>
Subject: Re: Cheaper subquery scan not considered unless offset 0
Date: Sun, 29 Oct 2017 15:41:20 +0100
Message-ID: <[email protected]> (raw)
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgsql-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.



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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Cheaper subquery scan not considered unless offset 0
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox