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
In-Reply-To: <47225014.5040101@laotraesquina.com.ar> (Pablo Alcaraz's message
	of "Fri\, 26 Oct 2007 16\:37\:40 -0400")
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux)
References: <47225014.5040101@laotraesquina.com.ar>
From: Gregory Stark <stark@enterprisedb.com>
Organization: EnterpriseDB
Date: Fri, 26 Oct 2007 22:12:37 +0100
Message-ID: <87tzod1sxm.fsf@oxford.xeocode.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

"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