Re: [PERFORM] Cheaper subquery scan not considered unless offset 0 - Mailing list pgsql-performance

It's not a modified postgres version. It's simply for my convenience that my tooling calculats "total" as "actual time"
multipliedby "loops". Looks like 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 HAVING
sum(amount)>= 1 
) c ON c.item = 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 = a."ID" WHERE c.stock >= 1

Query C:

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

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

Merge Join  (cost=34935.30..51701.59 rows=22285 width=344) (actual time=463.824..659.553 rows=15521 loops=1) Merge
Cond:(a."ID" = b.item) ->  Index Scan using "PK_items_ID" on items a  (cost=0.42..15592.23 rows=336083 width=332)
(actualtime=0.012..153.899 rows=336064 loops=1) ->  Sort  (cost=34934.87..34990.59 rows=22285 width=12) (actual
time=463.677..466.146rows=15521 loops=1)       Sort Key: b.item       Sort Method: quicksort  Memory: 1112kB       ->
FinalizeHashAggregate  (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  (cost=27865.65..32545.50 rows=44570 width=12) (actual time=343.715..407.243
rows=162152loops=1)                   Workers Planned: 2                   Workers Launched: 2                   ->
PartialHashAggregate  (cost=26865.65..27088.50 rows=22285 width=12) (actual time=336.416..348.105 rows=54051 loops=3)
                     Group Key: b.item                         ->  Parallel Seq Scan on stocktransactions b
(cost=0.00..23281.60rows=716810 width=12) (actual time=0.015..170.646 rows=579563 loops=3) 
Planning time: 0.277 ms
Execution time: 661.342 ms

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

Nested Loop  (cost=32768.77..41146.56 rows=7428 width=344) (actual time=456.611..525.395 rows=15521 loops=1) ->
SubqueryScan 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  (cost=27865.65..32545.50 rows=44570 width=12) (actual time=348.479..415.463 rows=162085
loops=1)                  Workers Planned: 2                   Workers Launched: 2                   ->  Partial
HashAggregate (cost=26865.65..27088.50 rows=22285 width=12) (actual time=343.952..355.912 rows=54028 loops=3)
             Group Key: b.item                         ->  Parallel Seq Scan on stocktransactions b
(cost=0.00..23281.60rows=716810 width=12) (actual time=0.015..172.235 rows=579563 loops=3) ->  Index Scan using
"PK_items_ID"on items a  (cost=0.42..1.05 rows=1 width=332) (actual time=0.003..0.003 rows=1 loops=15521)       Index
Cond:("ID" = c.item) 
Planning time: 0.223 ms
Execution time: 526.203 ms


========== Original ==========
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

>
>
> On 30 October 2017 at 00:24, Benjamin Coutu <ben.coutu@zeyos.com> wrote:
> >   ->  Index Scan using "PK_items_ID" on items a  (cost=0.42..1.05 rows=1 width=332) (actual time=0.003..0.003
rows=1loops=15521 total=46.563) 
>
> I've never seen EXPLAIN output like that before.
>
> Is this some modified version of PostgreSQL?
>



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