Re: [PERFORM] Cheaper subquery scan not considered unless offset 0
От | Benjamin Coutu |
---|---|
Тема | Re: [PERFORM] Cheaper subquery scan not considered unless offset 0 |
Дата | |
Msg-id | 20171029131721.45D535FB05@mx.zeyos.com обсуждение исходный текст |
Ответ на | [PERFORM] Cheaper subquery scan not considered unless offset 0 (Benjamin Coutu <ben.coutu@zeyos.com>) |
Список | 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
В списке pgsql-performance по дате отправления:
Предыдущее
От: David RowleyДата:
Сообщение: Re: [PERFORM] Cheaper subquery scan not considered unless offset 0
Следующее
От: Benjamin CoutuДата:
Сообщение: Re: [PERFORM] Cheaper subquery scan not considered unless offset 0