Re: performance of partitioning?

George Nychis wrote:
> 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?

We have a db with only 200,000,000 records, partitioned by year with
about 15 partitions. There is a clustered index on the timestamp field
and queries like a 25 wide self join for 3 months data are around 20
seconds. On a desktop box with a single SATA drive.

If you are querying by timestamp, I suggest a clustered index will help.

Brent Wood