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@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
>