Another question about partitioning

"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)"

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