From: | George Nychis <gnychis(at)cmu(dot)edu> |
---|---|
To: | cedric(at)over-blog(dot)com |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: performance of partitioning? |
Date: | 2007-02-27 14:13:04 |
Message-ID: | 45E43C70.8090208@cmu.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2007-02-27 14:30:10 | Re: Unable to restore dump due to client encoding issues -- or, when is SQL_ASCII really UTF8 |
Previous Message | cedric | 2007-02-27 14:11:31 | Re: performance of partitioning? |