Re: Early WIP/PoC for inlining CTEs

On Fri, Jul 27, 2018 at 8:10 PM, David Fetter <david(at)fetter(dot)org> wrote:
> On Fri, Jul 27, 2018 at 02:55:26PM +1200, Thomas Munro wrote:
>> On Thu, Jul 26, 2018 at 7:14 AM, David Fetter <david(at)fetter(dot)org> wrote:
>> > Please find attached the next version, which passes 'make check'.
>>
>> ... but not 'make check-world' (contrib/postgres_fdw's EXPLAIN is different).
>
> Please find attached a patch that does.
>
> It doesn't always pass make installcheck-world, but I need to sleep
> rather than investigate that at the moment.

One observation I wanted to share: CTE scans inhibit parallelism today
(something we might eventually want to fix with shared tuplestores).
This patch therefore allows parallelism in some WITH queries, which
seems like a very valuable thing. Example:

postgres=# create table foo as select generate_series(1, 1000000) i;
SELECT 1000000
postgres=# create table bar as select generate_series(1, 1000000) i;
SELECT 1000000
postgres=# create table baz as select generate_series(1, 1000000) i;
SELECT 1000000
postgres=# analyze;
ANALYZE

=== unpatched master ===

postgres=# explain analyze with cte as (select * from foo join bar
using (i)) select count(*) from cte join baz using (i);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=149531.00..149531.01 rows=1 width=8) (actual
time=4400.951..4400.951 rows=1 loops=1)
CTE cte
-> Hash Join (cost=30832.00..70728.00 rows=1000000 width=4)
(actual time=551.243..1961.319 rows=1000000 loops=1)
Hash Cond: (foo.i = bar.i)
-> Seq Scan on foo (cost=0.00..14425.00 rows=1000000
width=4) (actual time=0.048..219.238 rows=1000000 loops=1)
-> Hash (cost=14425.00..14425.00 rows=1000000 width=4)
(actual time=550.477..550.478 rows=1000000 loops=1)
Buckets: 131072 Batches: 16 Memory Usage: 3227kB
-> Seq Scan on bar (cost=0.00..14425.00
rows=1000000 width=4) (actual time=0.031..213.238 rows=1000000
loops=1)
-> Hash Join (cost=30832.00..76303.00 rows=1000000 width=0)
(actual time=1090.162..4279.945 rows=1000000 loops=1)
Hash Cond: (cte.i = baz.i)
-> CTE Scan on cte (cost=0.00..20000.00 rows=1000000
width=4) (actual time=551.247..2564.529 rows=1000000 loops=1)
-> Hash (cost=14425.00..14425.00 rows=1000000 width=4)
(actual time=538.833..538.833 rows=1000000 loops=1)
Buckets: 131072 Batches: 16 Memory Usage: 3227kB
-> Seq Scan on baz (cost=0.00..14425.00 rows=1000000
width=4) (actual time=0.039..208.658 rows=1000000 loops=1)
Planning Time: 0.291 ms
Execution Time: 4416.732 ms
(16 rows)

=== 0001-Inlining-CTEs-v0005.patch ===

postgres=# explain analyze with cte as (select * from foo join bar
using (i)) select count(*) from cte join baz using (i);

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=57854.78..57854.79 rows=1 width=8) (actual
time=1441.663..1441.664 rows=1 loops=1)
-> Gather (cost=57854.57..57854.78 rows=2 width=8) (actual
time=1440.506..1474.974 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=56854.57..56854.58 rows=1
width=8) (actual time=1435.017..1435.018 rows=1 loops=3)
-> Parallel Hash Join (cost=30856.01..55812.90
rows=416667 width=0) (actual time=1135.164..1393.437 rows=333333
loops=3)
Hash Cond: (foo.i = baz.i)
-> Parallel Hash Join (cost=15428.00..32202.28
rows=416667 width=8) (actual time=457.786..753.374 rows=333333
loops=3)
Hash Cond: (foo.i = bar.i)
-> Parallel Seq Scan on foo
(cost=0.00..8591.67 rows=416667 width=4) (actual time=0.094..87.666
rows=333333 loops=3)
-> Parallel Hash (cost=8591.67..8591.67
rows=416667 width=4) (actual time=217.222..217.222 rows=333333
loops=3)
Buckets: 131072 Batches: 16 Memory
Usage: 3520kB
-> Parallel Seq Scan on bar
(cost=0.00..8591.67 rows=416667 width=4) (actual time=0.061..84.631
rows=333333 loops=3)
-> Parallel Hash (cost=8591.67..8591.67
rows=416667 width=4) (actual time=227.240..227.241 rows=333333
loops=3)
Buckets: 131072 Batches: 16 Memory Usage: 3520kB
-> Parallel Seq Scan on baz
(cost=0.00..8591.67 rows=416667 width=4) (actual time=0.060..84.270
rows=333333 loops=3)
Planning Time: 0.407 ms
Execution Time: 1475.113 ms
(18 rows)

--
Thomas Munro
http://www.enterprisedb.com