From: Pablo Alcaraz <pabloa@laotraesquina.com.ar>
To: pgsql-performance@postgresql.org
Subject: Re: Speed difference between select ... union select ... and select from partitioned_table
Date: Fri, 26 Oct 2007 17:10:15 -0400
Message-ID: <472257B7.5090702@laotraesquina.com.ar> (raw)
In-Reply-To: <47225014.5040101@laotraesquina.com.ar>
References: <47225014.5040101@laotraesquina.com.ar>
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
>
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: pabloa@laotraesquina.com.ar
Subject: Re: Speed difference between select ... union select ... and select from partitioned_table
In-Reply-To: <472257B7.5090702@laotraesquina.com.ar>
* 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