Re: d25ea01275 and partitionwise join

On Sun, Oct 13, 2019 at 03:02:17PM -0500, Justin Pryzby wrote:
> On Thu, Sep 19, 2019 at 05:15:37PM +0900, Amit Langote wrote:
> > Please find attached updated patches.
> 
> Tom pointed me to this thread, since we hit it in 12.0
> https://www.postgresql.org/message-id/flat/16802.1570989962%40sss.pgh.pa.us#070f6675a11dff17760b1cfccf1c038d
> 
> I can't say much about the patch; there's a little typo:
> "The nullability of inner relation keys prevents them to"
> ..should say "prevent them from".
> 
> In order to compile it against REL12, I tried to cherry-pick this one:
> 3373c715: Speed up finding EquivalenceClasses for a given set of rels
> 
> But then it crashes in check-world (possibly due to misapplied hunks).

I did it again paying more attention and got it to pass.

The PWJ + FULL JOIN query seems ok now.

But I'll leave PWJ disabled until I can look more closely.

$ PGOPTIONS='-c max_parallel_workers_per_gather=0 -c enable_mergejoin=off -c enable_hashagg=off -c
enable_partitionwise_join=on'psql postgres -f tmp/sql-2019-10-11.1      
 
SET
 Nested Loop  (cost=80106964.13..131163200.28 rows=2226681567 width=6)
   Join Filter: ((s.site_location = ''::text) OR ((s.site_office)::integer = ((COALESCE(t1.site_id,
t2.site_id))::integer)))
   ->  Group  (cost=80106964.13..80837945.46 rows=22491733 width=12)
         Group Key: (COALESCE(t1.start_time, t2.start_time)), ((COALESCE(t1.site_id, t2.site_id))::integer)
         ->  Merge Append  (cost=80106964.13..80613028.13 rows=22491733 width=12)
               Sort Key: (COALESCE(t1.start_time, t2.start_time)), ((COALESCE(t1.site_id, t2.site_id))::integer)
               ->  Group  (cost=25494496.54..25633699.28 rows=11136219 width=12)
                     Group Key: (COALESCE(t1.start_time, t2.start_time)), ((COALESCE(t1.site_id,
t2.site_id))::integer)
                     ->  Sort  (cost=25494496.54..25522337.09 rows=11136219 width=12)
                           Sort Key: (COALESCE(t1.start_time, t2.start_time)), ((COALESCE(t1.site_id,
t2.site_id))::integer)
                           ->  Hash Full Join  (cost=28608.75..24191071.36 rows=11136219 width=12)
                                 Hash Cond: ((t1.start_time = t2.start_time) AND (t1.site_id = t2.site_id))
                                 Filter: ((COALESCE(t1.start_time, t2.start_time) >= '2019-10-01 00:00:00'::timestamp
withouttime zone) AND (COALESCE(t1.start_time, t2.start_time) < '2019-10-01 01:00:00'::timestamp without time zone))
 
                                 ->  Seq Scan on t1  (cost=0.00..14495.10 rows=940910 width=10)
                                 ->  Hash  (cost=14495.10..14495.10 rows=940910 width=10)
                                       ->  Seq Scan on t1 t2  (cost=0.00..14495.10 rows=940910 width=10)
               ->  Group  (cost=54612467.58..54754411.51 rows=11355514 width=12)
                     Group Key: (COALESCE(t1_1.start_time, t2_1.start_time)), ((COALESCE(t1_1.site_id,
t2_1.site_id))::integer)
                     ->  Sort  (cost=54612467.58..54640856.37 rows=11355514 width=12)
                           Sort Key: (COALESCE(t1_1.start_time, t2_1.start_time)), ((COALESCE(t1_1.site_id,
t2_1.site_id))::integer)
                           ->  Hash Full Join  (cost=28608.75..53281777.94 rows=11355514 width=12)
                                 Hash Cond: ((t1_1.start_time = t2_1.start_time) AND (t1_1.site_id = t2_1.site_id))
                                 Filter: ((COALESCE(t1_1.start_time, t2_1.start_time) >= '2019-10-01
00:00:00'::timestampwithout time zone) AND (COALESCE(t1_1.start_time, t2_1.start_time) < '2019-10-01
01:00:00'::timestampwithout time zone))
 
                                 ->  Seq Scan on t2 t1_1  (cost=0.00..14495.10 rows=940910 width=10)
                                 ->  Hash  (cost=14495.10..14495.10 rows=940910 width=10)
                                       ->  Seq Scan on t2 t2_1  (cost=0.00..14495.10 rows=940910 width=10)
   ->  Materialize  (cost=0.00..2.48 rows=99 width=6)
         ->  Seq Scan on s  (cost=0.00..1.99 rows=99 width=6)

-- 
Justin Pryzby
System Administrator
Telsasoft
+1-952-707-8581