pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

parallel query evaluation
2+ messages / 2 participants
[nested] [flat]

* parallel query evaluation
@ 2012-11-08 11:55 Oliver Seidel <postgresql@os10000.net>
  2012-11-10 15:32 ` Re: parallel query evaluation Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Oliver Seidel @ 2012-11-08 11:55 UTC (permalink / raw)
  To: pgsql-performance

Hi,

I have

             create table x ( att bigint, val bigint, hash varchar(30) 
);

with 693million rows.  The query

             create table y as select att, val, count(*) as cnt from x 
group by att, val;

ran for more than 2000 minutes and used 14g memory on an 8g physical 
RAM machine -- eventually I stopped it.  Doing

             create table y ( att bigint, val bigint, cnt int );
             and something a bit like: for i in `seq 0 255` | xargs -n 1 
-P 6
                         psql -c "insert into y select att, val, 
count(*) from x where att%256=$1 group by att, val" test

runs 6 out of 256 in 10 minutes -- meaning the whole problem can be 
done in just under 3 hours.

Question 1: do you see any reason why the second method would yield a 
different result from the first method?
Question 2: is that method generalisabl so that it could be included in 
the base system without manual shell glue?

Thanks,

Oliver





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: parallel query evaluation
  2012-11-08 11:55 parallel query evaluation Oliver Seidel <postgresql@os10000.net>
@ 2012-11-10 15:32 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2012-11-10 15:32 UTC (permalink / raw)
  To: postgresql@os10000.net; +Cc: pgsql-performance

Oliver Seidel <postgresql@os10000.net> writes:
> I have
>              create table x ( att bigint, val bigint, hash varchar(30) 
> );
> with 693million rows.  The query

>              create table y as select att, val, count(*) as cnt from x 
> group by att, val;

> ran for more than 2000 minutes and used 14g memory on an 8g physical 
> RAM machine

What was the plan for that query?  What did you have work_mem set to?

I can believe such a thing overrunning memory if the planner chose to
use a hash-aggregation plan instead of sort-and-unique, but it would
only do that if it had made a drastic underestimate of the number of
groups implied by the GROUP BY clause.  Do you have up-to-date
statistics for the source table?

			regards, tom lane




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2012-11-10 15:32 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-11-08 11:55 parallel query evaluation Oliver Seidel <postgresql@os10000.net>
2012-11-10 15:32 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox