From: | Justin Pryzby <pryzby(at)telsasoft(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | workaround for column cross-correlation |
Date: | 2017-06-13 03:17:52 |
Message-ID: | 20170613031752.GB4653@telsasoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I know PG 10 will have support "CREATE STATISTICS.." for this..
..but I wondered if there's a recommended workaround in earlier versions ?
We had two issues:
1) improper estimate caused poor plans (nested loops due to ::date, GROUP BY,
cross-column stats, and maybe more).
2) memory explosion in hash join (due to poor estimate?) caused OOM.
I tried ROW/ARRAY comparison for the correlated columns which seems to do what
I want, acting as a single comparison, rather than two, independent
comparisons.
-> Merge Join (cost=57811.81..62290.04 rows=114606 width=58) (actual time=5174.556..5698.323 rows=204672 loops=1)
Merge Cond: ((eric_enodeb_cellrelation_metrics.start_time = eric_enodeb_cellrelation_metrics_1.start_time) AND ((ROW(eric_enodeb_cellrelation_metrics.sect_id, eric_enodeb_cellrelation_metrics.neigh_sect_id)) = (ROW(eric_enodeb_cellrelation_metrics_1.neigh_sect_id, eric_enodeb_cellrelation_metrics_1.sect_id))))
Thanks,
Justin
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Janes | 2017-06-13 03:46:57 | Re: workaround for column cross-correlation |
Previous Message | armand pirvu | 2017-06-12 21:38:45 | Re: Strange port in pg_upgrade output |