Thread: 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
"Alex Vinogradovs" <AVinogradovs@Clearpathnet.com> 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!
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
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 >
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
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