From: | George Nychis <gnychis(at)cmu(dot)edu> |
---|---|
To: | George Nychis <gnychis(at)cmu(dot)edu> |
Cc: | cedric(at)over-blog(dot)com, pgsql-general(at)postgresql(dot)org |
Subject: | Re: performance of partitioning? |
Date: | 2007-02-27 15:05:13 |
Message-ID: | 45E448A9.5080003@cmu.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Bill Moran | 2007-02-27 15:31:47 | Re: Unable to restore dump due to client encoding issues -- or, when is SQL_ASCII really UTF8 |
Previous Message | Robert Fitzpatrick | 2007-02-27 15:03:28 | Building a record in a function |