Speed difference between select ... union select ... and select from partitioned_table - Mailing list pgsql-performance

From Pablo Alcaraz
Subject Speed difference between select ... union select ... and select from partitioned_table
Date
Msg-id 47225014.5040101@laotraesquina.com.ar
Whole thread Raw
Responses Re: Speed difference between select ... union select ... and select from partitioned_table  (Jeff Davis <pgsql@j-davis.com>)
Re: Speed difference between select ... union select ... and select from partitioned_table  (Pablo Alcaraz <pabloa@laotraesquina.com.ar>)
Re: Speed difference between select ... union select ... and select from partitioned_table  (Gregory Stark <stark@enterprisedb.com>)
Re: Speed difference between select ... union select ... and select from partitioned_table  (Simon Riggs <simon@2ndquadrant.com>)
List pgsql-performance
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);






pgsql-performance by date:

Previous
From: "Campbell, Lance"
Date:
Subject: Re: Suggestions on an update query
Next
From: Jeff Davis
Date:
Subject: Re: Speed difference between select ... union select ... and select from partitioned_table