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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
