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@xxxxxxxxxxxxxx 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
