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 without time 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'::timestamp without time zone) AND (COALESCE(t1_1.start_time, t2_1.start_time) < '2019-10-01 01:00:00'::timestamp without 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