Thread: [GENERAL] workaround for column cross-correlation
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
On Mon, Jun 12, 2017 at 8:17 PM, Justin Pryzby <pryzby@telsasoft.com> wrote:
I know PG 10 will have support "CREATE STATISTICS.." for this..
..but I wondered if there's a recommended workaround in earlier versions ?
Not without seeing the query....
2) memory explosion in hash join (due to poor estimate?) caused OOM.
As far as I know, the only way a hash join should do this is if the join includes a huge number of rows with exactly the same 32 bit hash codes. Otherwise, it should spill to disk without causing OOM. Hash aggregates, on the other hand, are a different matter.
Cheers,
Jeff
On Mon, Jun 12, 2017 at 08:46:57PM -0700, Jeff Janes wrote: > On Mon, Jun 12, 2017 at 8:17 PM, Justin Pryzby <pryzby@telsasoft.com> wrote: > > > I know PG 10 will have support "CREATE STATISTICS.." for this.. > > > > ..but I wondered if there's a recommended workaround in earlier versions ? > > Not without seeing the query.... With my workaround: ts=# explain ANALYZE SELECT t1.sect_id, t1.start_time as period, sum (1) FROM enodeb_ncell_view t1, enodeb_ncell_view inc WHERE ((t1.start_time >= '2017-04-30 00:00:00' AND t1.start_time < '2017-05-01 00:00:00')) AND ((inc.start_time >= '2017-04-30 00:00:00' AND inc.start_time < '2017-05-01 00:00:00')) AND t1.start_time = inc.start_time AND ROW((t1.sect_id,t1.neigh_sect_id))= ROW((inc.neigh_sect_id,inc.sect_id)) GROUP BY t1.sect_id, period; HashAggregate (cost=63149.59..63371.74 rows=22215 width=10) (actual time=80092.652..80097.521 rows=22464 loops=1) ... Without: ts=# explain ANALYZE SELECT t1.sect_id, t1.start_time as period, sum (1) FROM enodeb_ncell_view t1, enodeb_ncell_view inc WHERE ((t1.start_time >= '2017-04-30 00:00:00' AND t1.start_time < '2017-05-01 00:00:00')) AND ((inc.start_time >= '2017-04-30 00:00:00' AND inc.start_time < '2017-05-01 00:00:00')) AND t1.start_time = inc.start_time AND t1.sect_id=inc.neigh_sect_id AND t1.neigh_sect_id=inc.sect_id GROUP BY t1.sect_id, period; GroupAggregate (cost=57847.32..62265.54 rows=402 width=10) (actual time=694.186..952.744 rows=22464 loops=1) ... This is a small inner subquery of a larger report - sum(1) is a placeholder for other aggregates I've stripped out. > > 2) memory explosion in hash join (due to poor estimate?) caused OOM. > > As far as I know, the only way a hash join should do this is if the join > includes a huge number of rows with exactly the same 32 bit hash codes. > Otherwise, it should spill to disk without causing OOM. Hash aggregates, > on the other hand, are a different matter. That's almost certainy what I meant. -> Subquery Scan on data_rx_enb (cost=3409585.76..3422861.74 rows=663799 width=20) (actual time=510475.987..512069.064rows=2169821 loops=1) -> HashAggregate (cost=3409585.76..3416223.75 rows=663799 width=16) (actual time=510475.984..511650.337 rows=2169821loops=1) Group Key: eric_enodeb_cell_metrics_1.site_id, eric_enodeb_cell_metrics_1.start_time Thanks, Justin