materialization blocks hash join - Mailing list pgsql-hackers

Hi

when I was in talk with Silvio Moioli, I found strange hash join. Hash was created from bigger table.

Now it looks so materialized CTE disallow hash

create table bigger(a int);

create table smaller(a int);

insert into bigger select random()* 10000 from generate_series(1,100000);

insert into smaller select i from generate_series(1,100000) g(i);

analyze bigger, smaller;

-- no problem

explain analyze select * from bigger b join smaller s on b.a = s.a;

postgres=# explain analyze select * from bigger b join smaller s on b.a = s.a;
                                                         QUERY PLAN                                                        
----------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=3084.00..7075.00 rows=100000 width=8) (actual time=32.937..87.276 rows=99994 loops=1)
   Hash Cond: (b.a = s.a)
   ->  Seq Scan on bigger b  (cost=0.00..1443.00 rows=100000 width=4) (actual time=0.028..8.546 rows=100000 loops=1)
   ->  Hash  (cost=1443.00..1443.00 rows=100000 width=4) (actual time=32.423..32.423 rows=100000 loops=1)
         Buckets: 131072  Batches: 2  Memory Usage: 2785kB
         ->  Seq Scan on smaller s  (cost=0.00..1443.00 rows=100000 width=4) (actual time=0.025..9.931 rows=100000 loops=1)
 Planning Time: 0.438 ms
 Execution Time: 91.193 ms
(8 rows)

but with materialized CTE

postgres=# explain analyze with b as materialized (select * from bigger), s as materialized (select * from smaller) select * from b join s on b.a = s.a;
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=23495.64..773995.64 rows=50000000 width=8) (actual time=141.242..193.375 rows=99994 loops=1)
   Merge Cond: (b.a = s.a)
   CTE b
     ->  Seq Scan on bigger  (cost=0.00..1443.00 rows=100000 width=4) (actual time=0.026..11.083 rows=100000 loops=1)
   CTE s
     ->  Seq Scan on smaller  (cost=0.00..1443.00 rows=100000 width=4) (actual time=0.015..9.161 rows=100000 loops=1)
   ->  Sort  (cost=10304.82..10554.82 rows=100000 width=4) (actual time=78.775..90.953 rows=100000 loops=1)
         Sort Key: b.a
         Sort Method: external merge  Disk: 1376kB
         ->  CTE Scan on b  (cost=0.00..2000.00 rows=100000 width=4) (actual time=0.033..39.274 rows=100000 loops=1)
   ->  Sort  (cost=10304.82..10554.82 rows=100000 width=4) (actual time=62.453..74.004 rows=99996 loops=1)
         Sort Key: s.a
         Sort Method: external sort  Disk: 1768kB
         ->  CTE Scan on s  (cost=0.00..2000.00 rows=100000 width=4) (actual time=0.018..31.669 rows=100000 loops=1)
 Planning Time: 0.303 ms
 Execution Time: 199.919 ms
(16 rows)

It doesn't use hash join - the estimations are perfect, but plan is suboptimal

Regards

Pavel