workaround for column cross-correlation

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

Responses

Browse pgsql-general by date

  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