Date: Sun, 29 Oct 2017 12:24:20 +0100
From: Benjamin Coutu <ben.coutu@zeyos.com>
Subject: Cheaper subquery scan not considered unless offset 0
To: pgsql-performance@postgresql.org
Cc: 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: <20171029112420.8920B5FB05@mx.zeyos.com>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hello everyone,

Please consider the following three semantically equivalent, but differentl=
y written queries:

Query A:

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

Query B:

SELECT * FROM items a INNER JOIN (
  SELECT item, sum(amount) stock FROM stocktransactions GROUP BY item
) b ON b.item =3D a. "ID" WHERE b.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
) b ON b.item =3D a. "ID" WHERE b.stock >=3D 1

FYI: stocktransactions.item and stocktransactions.amount have not null cons=
traints and stocktransactions.item is a foreign key referencing items.ID, t=
he primary key of items.

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 total=3D525.395)
  ->  Subquery Scan on c  (cost=3D32768.35..33269.76 rows=3D7428 width=3D12=
) (actual time=3D456.591..475.204 rows=3D15521 loops=3D1 total=3D475.204)
        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 total=
=3D468.124)
              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 total=3D415.463)
                    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 total=3D1067.736)
                          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 total=3D516.705)
  ->  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 total=3D4=
6.563)
        Index Cond: ("ID" =3D c.item)
Planning time: 0.223 ms
Execution time: 526.203 ms


I'm wondering, given that Query C's plan has lower overall costs than Query=
 A/B's, why wouldn't the planner choose to execute that plan for queries A+=
B as well?
It has lower projected startup cost as well as lower total cost so apparent=
ly the optimzer does not consider such a plan with a subquery scan at all (=
otherwise it would choose it based on the lower cost estimates, right?) unl=
ess one forces it to via OFFSET 0.

Though I wouldn't necessarily consider this a bug, it is an issue that one =
has to explicitly work around with inadvisable optimization fences and it w=
ould be great if this could be fixed.

Thanks to the developer community for delivering this great product, I hope=
 this helps in enhancing it.

Cheers,

Benjamin

--=20

Bejamin Coutu
ben.coutu@zeyos.com

ZeyOS, Inc.
http://www.zeyos.com


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