Message-ID: <472257B7.5090702@laotraesquina.com.ar>
Date: Fri, 26 Oct 2007 17:10:15 -0400
From: Pablo Alcaraz <pabloa@laotraesquina.com.ar>
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Speed difference between select ... union select ...
	and select from partitioned_table
References: <47225014.5040101@laotraesquina.com.ar>
In-Reply-To: <47225014.5040101@laotraesquina.com.ar>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

I forgot to post the times:

query-union: 21:59
query-heritage: 1:31:24

Regards

Pablo

Pablo Alcaraz wrote:
> 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
>        select e, p, count( *) as c
>        from tt_00009
>        group by e, p
>        union
>        select e, p, count( *) as c
>        from tt_00012
>        group by e, p
>        union
>        select e, p, count( *) as c
>        from tt_00015
>        group by e, p
> ) as t
> group by e, p
> order by e, p desc;
>
>
>
> [pabloa@igor testeo]$ cat query-heritage.sql
> select e, p,  count( *) as c
> from tt
> group by e, p
> order by e, p desc;
>
>
> The server is a Athlon 64x2 6000+ 2 Gb RAM PostreSQL 8.2.5
>
> The structure tables are:
>
> CREATE TABLE tt_00003
> (
> -- Inherited:   idtt bigint NOT NULL,
> -- Inherited:   idttp bigint NOT NULL,
> -- Inherited:   e integer NOT NULL,
> -- Inherited:   dmodi timestamp without time zone NOT NULL DEFAULT now(),
> -- Inherited:   p integer NOT NULL DEFAULT 0,
> -- Inherited:   m text NOT NULL,
>  CONSTRAINT tt_00003_pkey PRIMARY KEY (idtt),
>  CONSTRAINT tt_00003_idtt_check CHECK (idtt >= 1::bigint AND idtt <= 
> 30000000::bigint)
> ) INHERITS (tt)
> WITHOUT OIDS;
> ALTER TABLE tt_00003 ;
>
> CREATE INDEX tt_00003_e
>  ON tt_00003
>  USING btree
>  (e);
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                http://www.postgresql.org/about/donate
>