performance of partitioning?

From: George Nychis <gnychis(at)cmu(dot)edu>
To: pgsql-general(at)postgresql(dot)org
Subject: performance of partitioning?
Date: 2007-02-27 14:00:09
Message-ID: 45E43969.9080609@cmu.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hey all,

So I have a master table called "flows" and 400 partitions in the format
"flow_*" where * is equal to some epoch.

Each partition contains ~700,000 rows and has a check such that 1 field is equal
to a value:
"flows_1107246900_interval_check" CHECK ("interval" = '2005-02-01
03:35:00'::timestamp without time zone)

Each partition has a different and unique non-overlapping check.

This query takes about 5 seconds to execute:
dp=> select count(*) from flows_1107246900;
count
--------
696836
(1 row)

This query has been running for 10 minutes now and hasn't stopped:
dp=> select count(*) from flows where interval='2005-02-01 03:35:00';

Isn't partitioning supposed to make the second query almost as fast? My WHERE
is exactly the partitioning constraint, therefore it only needs to go to 1
partition and execute the query.

Why would it take magnitudes longer to run? Am i misunderstanding something?

Thanks!
George

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jorge Godoy 2007-02-27 14:09:33 Re: performance of partitioning?
Previous Message Andrew Edson 2007-02-27 13:56:38 How does filter order relate to query optimization?