Re: Early WIP/PoC for inlining CTEs

On Fri, Jul 27, 2018 at 8:10 PM, David Fetter <david@fetter.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@fetter.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