Re: [GENERAL] Another question about partitioning
Yes, I enter query manually while testing. Here are explain plans : for select count(*) from poll_3 where eid = 72333 Aggregate (cost=34697.64..34697.65 rows=1 width=0) - Seq Scan on poll_3 (cost=0.00..34650.40 rows=18893 width=0) Filter: (eid = 72333) for for select count(*) from poll where eid = 72333 Aggregate (cost=320001.59..320001.60 rows=1 width=0) - Append (cost=0.00..319570.78 rows=172323 width=0) - Seq Scan on poll (cost=0.00..27.50 rows=17 width=0) Filter: (eid = 72333) - Seq Scan on poll_0 poll (cost=0.00..14348.85 rows=9014 width=0) Filter: (eid = 72333) - Seq Scan on poll_1 poll (cost=0.00..34796.82 rows=18735 width=0) Filter: (eid = 72333) - Seq Scan on poll_2 poll (cost=0.00..34993.84 rows=18527 width=0) Filter: (eid = 72333) - Seq Scan on poll_3 poll (cost=0.00..34650.40 rows=18893 width=0) Filter: (eid = 72333) - Seq Scan on poll_4 poll (cost=0.00..34230.55 rows=18099 width=0) Filter: (eid = 72333) - Seq Scan on poll_5 poll (cost=0.00..34267.64 rows=17543 width=0) Filter: (eid = 72333) - Seq Scan on poll_6 poll (cost=0.00..34469.73 rows=18719 width=0) Filter: (eid = 72333) - Seq Scan on poll_7 poll (cost=0.00..33642.98 rows=17968 width=0) Filter: (eid = 72333) - Seq Scan on poll_8 poll (cost=0.00..32199.15 rows=16480 width=0) Filter: (eid = 72333) - Seq Scan on poll_9 poll (cost=0.00..31943.33 rows=18328 width=0) Filter: (eid = 72333) On Tue, 2007-11-27 at 17:40 -0800, paul rivers wrote: Alex Vinogradovs wrote: Hello all, I have a table which is partitioned by range into 10 pieces with constraint exceptions. Constraint exceptions is enabled in server configuration too. For some reason, queries to the master table are still slower than direct queries against partitions. Is there any real reason for that, or I should look into misconfiguration ? Thanks! Best regards, Alex Vinogradovs Is that true even if you type the query yourself in psql and ensure that the values for the partitioned columns are constants in the where clause? Can you post an explain of the sql? Paul ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Another question about partitioning
Alex Vinogradovs wrote: Yes, I enter query manually while testing. Here are explain plans : for select count(*) from poll_3 where eid = 72333 Aggregate (cost=34697.64..34697.65 rows=1 width=0) - Seq Scan on poll_3 (cost=0.00..34650.40 rows=18893 width=0) Filter: (eid = 72333) for for select count(*) from poll where eid = 72333 Aggregate (cost=320001.59..320001.60 rows=1 width=0) - Append (cost=0.00..319570.78 rows=172323 width=0) - Seq Scan on poll (cost=0.00..27.50 rows=17 width=0) Filter: (eid = 72333) - Seq Scan on poll_0 poll (cost=0.00..14348.85 rows=9014 width=0) Filter: (eid = 72333) - Seq Scan on poll_1 poll (cost=0.00..34796.82 rows=18735 width=0) Filter: (eid = 72333) - Seq Scan on poll_2 poll (cost=0.00..34993.84 rows=18527 width=0) Do you have appropriate check constraints defined on table poll? Can you include a \d poll? Also, what version is this? Paul ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Another question about partitioning
paul rivers wrote: Alex Vinogradovs wrote: Yes, I enter query manually while testing. Here are explain plans : for select count(*) from poll_3 where eid = 72333 Aggregate (cost=34697.64..34697.65 rows=1 width=0) - Seq Scan on poll_3 (cost=0.00..34650.40 rows=18893 width=0) Filter: (eid = 72333) for for select count(*) from poll where eid = 72333 Aggregate (cost=320001.59..320001.60 rows=1 width=0) - Append (cost=0.00..319570.78 rows=172323 width=0) - Seq Scan on poll (cost=0.00..27.50 rows=17 width=0) Filter: (eid = 72333) - Seq Scan on poll_0 poll (cost=0.00..14348.85 rows=9014 width=0) Filter: (eid = 72333) - Seq Scan on poll_1 poll (cost=0.00..34796.82 rows=18735 width=0) Filter: (eid = 72333) - Seq Scan on poll_2 poll (cost=0.00..34993.84 rows=18527 width=0) Do you have appropriate check constraints defined on table poll? Can you include a \d poll? Also, what version is this? Paul Sorry, I should have asked: do you have check constraints defined on all the child poll tables? So, what's \d poll_3 look like, etc? You've already said you're sure constraint exclusion is on, but you're also sure postmaster was restarted too? Paul ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Another question about partitioning
Hello all, I have a table which is partitioned by range into 10 pieces with constraint exceptions. Constraint exceptions is enabled in server configuration too. For some reason, queries to the master table are still slower than direct queries against partitions. Is there any real reason for that, or I should look into misconfiguration ? Thanks! Best regards, Alex Vinogradovs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Another question about partitioning
Alex Vinogradovs [EMAIL PROTECTED] writes: Hello all, I have a table which is partitioned by range into 10 pieces with constraint exceptions. Constraint exceptions is enabled in server configuration too. For some reason, queries to the master table are still slower than direct queries against partitions. Is there any real reason for that, or I should look into misconfiguration ? Well you have to look at the actual plans. Having to combine multiple partitions does have some cost to it and does interfere somewhat in the planner's ability to optimize plans so it might not be a win on individual queries if they were not doing big scans of unnecessary data previously. You might also consider using partial indexes instead of partitioning if your goal is just optimizing queries. The big advantage of partitioning is being able to add and drop entire partitions effectively instantaneously. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Another question about partitioning
Alex Vinogradovs wrote: Hello all, I have a table which is partitioned by range into 10 pieces with constraint exceptions. Constraint exceptions is enabled in server configuration too. For some reason, queries to the master table are still slower than direct queries against partitions. Is there any real reason for that, or I should look into misconfiguration ? Thanks! Best regards, Alex Vinogradovs Is that true even if you type the query yourself in psql and ensure that the values for the partitioned columns are constants in the where clause? Can you post an explain of the sql? Paul ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
