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
