Date: Sun, 29 Oct 2017 14:17:19 +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: <20171029131721.45D535FB05@mx.zeyos.com>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

It's not a modified postgres version. It's simply for my convenience that m=
y tooling calculats "total" as "actual time" multiplied by "loops". Looks l=
ike I didn't properly strip that away when copy-pasting.

Here are the queries and original plans again, sorry for the confusion.

Query A:

SELECT * FROM items a INNER JOIN (
  SELECT item, sum(amount) stock FROM stocktransactions b GROUP BY item HAV=
ING sum(amount) >=3D 1
) c ON c.item =3D a."ID"

Query B:

SELECT * FROM items a INNER JOIN (
  SELECT item, sum(amount) stock FROM stocktransactions b GROUP BY item
) c ON c.item =3D a."ID" WHERE c.stock >=3D 1

Query C:

SELECT * FROM items a INNER JOIN (
  SELECT item, sum(amount) stock FROM stocktransactions b GROUP BY item OFF=
SET 0
) c ON c.item =3D a."ID" WHERE c.stock >=3D 1

Queries A + B generate the same plan and execute as follows:

Merge Join  (cost=3D34935.30..51701.59 rows=3D22285 width=3D344) (actual ti=
me=3D463.824..659.553 rows=3D15521 loops=3D1)
  Merge Cond: (a."ID" =3D b.item)
  ->  Index Scan using "PK_items_ID" on items a  (cost=3D0.42..15592.23 row=
s=3D336083 width=3D332) (actual time=3D0.012..153.899 rows=3D336064 loops=
=3D1)
  ->  Sort  (cost=3D34934.87..34990.59 rows=3D22285 width=3D12) (actual tim=
e=3D463.677..466.146 rows=3D15521 loops=3D1)
        Sort Key: b.item
        Sort Method: quicksort  Memory: 1112kB
        ->  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  (cost=3D27865.65..32545.50 rows=3D44570 width=3D1=
2) (actual time=3D343.715..407.243 rows=3D162152 loops=3D1)
                    Workers Planned: 2
                    Workers Launched: 2
                    ->  Partial HashAggregate  (cost=3D26865.65..27088.50 r=
ows=3D22285 width=3D12) (actual time=3D336.416..348.105 rows=3D54051 loops=
=3D3)
                          Group Key: b.item
                          ->  Parallel Seq Scan on stocktransactions b  (co=
st=3D0.00..23281.60 rows=3D716810 width=3D12) (actual time=3D0.015..170.646=
 rows=3D579563 loops=3D3)
Planning time: 0.277 ms
Execution time: 661.342 ms

Plan C though, thanks to the "offset optimization fence", executes the foll=
owing, more efficient plan:

Nested Loop  (cost=3D32768.77..41146.56 rows=3D7428 width=3D344) (actual ti=
me=3D456.611..525.395 rows=3D15521 loops=3D1)
  ->  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  (cost=3D27865.65..32545.50 rows=3D44570 width=3D1=
2) (actual time=3D348.479..415.463 rows=3D162085 loops=3D1)
                    Workers Planned: 2
                    Workers Launched: 2
                    ->  Partial HashAggregate  (cost=3D26865.65..27088.50 r=
ows=3D22285 width=3D12) (actual time=3D343.952..355.912 rows=3D54028 loops=
=3D3)
                          Group Key: b.item
                          ->  Parallel Seq Scan on stocktransactions b  (co=
st=3D0.00..23281.60 rows=3D716810 width=3D12) (actual time=3D0.015..172.235=
 rows=3D579563 loops=3D3)
  ->  Index Scan using "PK_items_ID" on items a  (cost=3D0.42..1.05 rows=3D=
1 width=3D332) (actual time=3D0.003..0.003 rows=3D1 loops=3D15521)
        Index Cond: ("ID" =3D c.item)
Planning time: 0.223 ms
Execution time: 526.203 ms


=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D Original =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
From: David Rowley <david.rowley@2ndquadrant.com>
To: Benjamin Coutu <ben.coutu@zeyos.com>
Date: Sun, 29 Oct 2017 12:46:42 +0100
Subject: Re: [PERFORM] Cheaper subquery scan not considered unless offset 0

>=20
>=20
> On 30 October 2017 at 00:24, Benjamin Coutu <ben.coutu@zeyos.com> wrote:
> >   ->  Index Scan using "PK_items_ID" on items a  (cost=3D0.42..1.05 row=
s=3D1 width=3D332) (actual time=3D0.003..0.003 rows=3D1 loops=3D15521 total=
=3D46.563)
>=20
> I've never seen EXPLAIN output like that before.
>=20
> Is this some modified version of PostgreSQL?
>


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