Search Postgresql Archives

Re: workaround for column cross-correlation

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxx> 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.064 rows=2169821 loops=1)
	 ->  HashAggregate  (cost=3409585.76..3416223.75 rows=663799 width=16) (actual time=510475.984..511650.337 rows=2169821 loops=1)
	       Group Key: eric_enodeb_cell_metrics_1.site_id, eric_enodeb_cell_metrics_1.start_time

Thanks,
Justin


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux