Re: performance of partitioning?

George Nychis wrote:
>
>
> cedric wrote:
>> Le mardi 27 février 2007 15:00, George Nychis a écrit :
>>> 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?
>> perhaps you should consider constraint_exclusion
>> http://www.postgresql.org/docs/current/static/ddl-partitioning.html
>> http://www.postgresql.org/docs/current/static/runtime-config-query.html#GUC-CONSTRAINT-EXCLUSION
>>
>>> Thanks!
>>> George
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 4: Have you searched our list archives?
>>>
>>>                http://archives.postgresql.org/
>>
>
> That sounds like what i'm looking for, thanks.  I'll give it a try and
> report back.
>
> - George
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>

Worked perfectly, the two commands have near exact execution time now.  Thank you!

- George