Re: Speed difference between select ... union select ... and select from partitioned_table

From: Pablo Alcaraz <pabloa(at)laotraesquina(dot)com(dot)ar>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Speed difference between select ... union select ... and select from partitioned_table
Date: 2007-10-27 22:31:18
Message-ID: 4723BC36.8000300@laotraesquina.com.ar
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-performance

Pablo Alcaraz wrote:
> These are the EXPLAIN ANALIZE:
>

If you raise work_mem enough to let the second query use a hash
aggregate (probably a few MB would do it), I think it'll be about
the same speed as the first one.

The reason it's not picking that on its own is the overestimate
of the number of resulting groups. This is because
get_variable_numdistinct is not smart about append relations.
We should try to fix that sometime...

I re run the partitioned-query. it completed in 15996 seconds. It
builded a BIG temp file:

[root(at)igor xxx]# ls -lh pgsql-data/data/16386/pgsql_tmp/
total 2.2G
-rw------- 1 postgres postgres 1.0G Oct 27 15:35 pgsql_tmp7004.0
-rw------- 1 postgres postgres 1.0G Oct 27 15:35 pgsql_tmp7004.1
-rw------- 1 postgres postgres 175M Oct 27 15:35 pgsql_tmp7004.2

work_mem=1Mb. How much do I need to raise work_mem variable? 2.2G?

Regards

Pablo

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Gregory Stark 2007-10-28 00:20:26 Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1
Previous Message Luke Lonergan 2007-10-27 21:48:16 Re: partitioned table and ORDER BY indexed_field DESCLIMIT 1