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

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(at)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(at)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
>