Re: performance of partitioning?

George Nychis <gnychis(at)cmu(dot)edu> writes:

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

When checking from the partition it only contains the records from that
specific partition. When checking from the parent table it contains records
for all partitions.

Also note that an index on interval wouldn't be helpful here, I believe, due
to the fact that data is in a different table and not on the parent one.

--
Jorge Godoy <jgodoy(at)gmail(dot)com>