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