Date: Sun, 29 Oct 2017 15:41:20 +0100
From: Benjamin Coutu <ben.coutu@zeyos.com>
Subject: Re: Cheaper subquery scan not considered unless offset 0
To: David Rowley <david.rowley@2ndquadrant.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>,
  	Tom Lane <tgl@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Message-Id: <20171029144121.7C7CE5FB08@mx.zeyos.com>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

There is actually another separate issue here apart from that the planner o=
bviously 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 est=
imating the number of returned rows when applied in different nodes.

Queries A/B:

 ->  Finalize HashAggregate  (cost=3D32879.78..33102.62 rows=3D22285 width=
=3D12) (actual time=3D450.724..458.667 rows=3D15521 loops=3D1)
              Group Key: b.item
              Filter: (sum(b.amount) >=3D '1'::double precision)
              Rows Removed by Filter: 48277
              ->  Gather ...

Query C:

 ->  Subquery Scan on c  (cost=3D32768.35..33269.76 rows=3D7428 width=3D12)=
 (actual time=3D456.591..475.204 rows=3D15521 loops=3D1)
        Filter: (c.stock >=3D '1'::double precision)
        Rows Removed by Filter: 48277
        ->  Finalize HashAggregate  (cost=3D32768.35..32991.20 rows=3D22285=
 width=3D12) (actual time=3D456.582..468.124 rows=3D63798 loops=3D1)
              Group Key: b.item
              ->  Gather ...

Interestingly enough the subquery scan with query C correctly accounts for =
the filter when estimating rows=3D7428, while A/B doesn't seem to account f=
or the filter in the HasAggregate node (estimated rows=3D22285). This looks=
 like a bug.


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