From: Gregory Stark <stark@enterprisedb.com>
To: Pablo Alcaraz <pabloa@laotraesquina.com.ar>
Cc: pgsql-performance@postgresql.org
Subject: Re: Speed difference between select ... union select ... and select from partitioned_table
Date: Fri, 26 Oct 2007 22:12:37 +0100
Message-ID: <87tzod1sxm.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <47225014.5040101@laotraesquina.com.ar>
References: <47225014.5040101@laotraesquina.com.ar>
"Pablo Alcaraz" <pabloa@laotraesquina.com.ar> writes:
> Hi List!
>
> I executed 2 equivalents queries. The first one uses a union structure. The
> second uses a partitioned table. The tables are the same with 30 millions of
> rows each one and the returned rows are the same.
>
> But the union query perform faster than the partitioned query.
>
> My question is: why? :)
>
> [pabloa@igor testeo]$ cat query-union.sql
> select e, p, sum( c) as c
> from (
> select e, p, count( *) as c
> from tt_00003
> group by e, p
> union
> select e, p, count( *) as c
> from tt_00006
> group by e, p
> union
...
You should send along the "explain analyze" results for both queries,
otherwise we're just guessing.
Also, you should consider using UNION ALL instead of plain UNION.
Finally you should consider removing all the intermediate GROUP BYs and just
group the entire result. In theory it should be faster but in practice I'm not
sure it works out that way.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stark@enterprisedb.com, pabloa@laotraesquina.com.ar
Subject: Re: Speed difference between select ... union select ... and select from partitioned_table
In-Reply-To: <87tzod1sxm.fsf@oxford.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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