Thread: Partitioning: Planner makes no use of indexes on inherited table?!
Oh, seems the table partitioning I set up with so much effort,
isn't so powerful as I hoped to be:
I do a select on the main table, which postgres correctly restricts
to one of the inherited table because of its check constraint.
Still the order by clause does not make use of the index that I
configured for the inherited table.
Is there anything I can do to make this work?
I looked into creating another rule that replaces the table name on selects
but it seems that the parent table must be a view in that case, which
completely disables constraint exclusion, right?
Clueless. Please help if you can...
------------------------------------------------------------------------
Here is the plan if I query the inherited table directly
(which is not really possible to do with that stupid, inflexible
hibernate framework):
explain select this_.id as id1_0_, <more columns>
from call_sources_10554 this_
where this_.cdr_id = 10554
order by this_.id asc
limit 100;
Limit (cost=0.00..4.09 rows=100 width=8907)
-> Index Scan using call_source_10554_id_index on call_sources_10554 this_ (cost=0.00..23510.68 rows=575532
width=8907)
Filter: (cdr_id = 10554)
------------------------------------------------------------------------
However the same query executed in the parent table ("... from call_sources ...") gives:
Limit (cost=23362879.77..23362880.02 rows=100 width=13436)
-> Sort (cost=23362879.77..23364318.60 rows=575533 width=13436)
Sort Key: this_.id
-> Result (cost=0.00..20741.97 rows=575533 width=13436)
-> Append (cost=0.00..20741.97 rows=575533 width=13436)
-> Index Scan using call_source_cdr_index on call_sources this_ (cost=0.00..4.82 rows=1
width=13436)
Index Cond: (cdr_id = 10554)
-> Seq Scan on call_sources_10554 this_ (cost=0.00..20737.15 rows=575532 width=8907)
Filter: (cdr_id = 10554)
Stephen Friedrich wrote: > Here is the plan if I query the inherited table directly > (which is not really possible to do with that stupid, inflexible > hibernate framework): > > explain select this_.id as id1_0_, <more columns> > from call_sources_10554 this_ > where this_.cdr_id = 10554 > order by this_.id asc > limit 100; I don't suppose changing it to: ORDER BY this_.cdr_id, this_.id does anything for you? -- Richard Huxton Archonet Ltd
Richard Huxton wrote: > I don't suppose changing it to: > ORDER BY this_.cdr_id, this_.id > does anything for you? Nope. Same cost :-( I am currently trying to find a way to intercept and modify the sql generated by hibernate. There does not seem to be any hook for general queries. My last resort will be to create a jdbc driver as a wrapper. Parsing the statement will not be trivial, though. Still, even though it would be easy to fix with different SQL, I think this is a shortcoming that postgres should fix.
Stephen Friedrich wrote: > Richard Huxton wrote: >> I don't suppose changing it to: >> ORDER BY this_.cdr_id, this_.id >> does anything for you? > > Nope. Same cost :-( Maybe I'm barking up the wrong tree then. Maybe the costs for the index just look wrong. Hmm - looking at it, this seems the case. Directly querying the partition it uses the index but estimates a cost of: cost=0.00..23510.68 rows=575532 width=8907) The seq-scan says it has a cost of: cost=0.00..20737.15 rows=575532 width=8907 Could you issue "set enable_seqscan=off" and then show explain analyse on the second query again? -- Richard Huxton Archonet Ltd
> Hmm - looking at it, this seems the case. Directly querying the
> partition it uses the index but estimates a cost of:
> cost=0.00..23510.68 rows=575532 width=8907)
> The seq-scan says it has a cost of:
> cost=0.00..20737.15 rows=575532 width=8907
>
> Could you issue "set enable_seqscan=off" and then show explain analyse
> on the second query again?
That doesn't make a big difference:
Limit (cost=23365451.30..23365453.80 rows=1000 width=13436)
-> Sort (cost=23365451.30..23366890.13 rows=575533 width=13436)
Sort Key: this_.id
-> Result (cost=0.00..23313.51 rows=575533 width=13436)
-> Append (cost=0.00..23313.51 rows=575533 width=13436)
-> Index Scan using call_source_cdr_index on call_sources this_ (cost=0.00..4.82 rows=1
width=13436)
Index Cond: (cdr_id = 10554)
-> Index Scan using call_source_10554_cdr_index on call_sources_10554 this_ (cost=0.00..23308.68
rows=575532width=8907)
Index Cond: (cdr_id = 10554)
IMHO the problem is that this should not be done:
-> Sort (cost=23365451.30..23366890.13 rows=575533 width=13436)
Sort Key: this_.id
because there is an index on the id column of the inherited table, so it
should be used as it is when querying the inherited table directly:
-> Index Scan using call_source_10554_id_index on call_sources_10554 this_ (cost=0.00..23510.68 rows=575532
width=8907)
Filter: (cdr_id = 10554)
On Wed, Jan 18, 2006 at 11:05:24AM +0100, Stephen Friedrich wrote: > That doesn't make a big difference: <snip> > IMHO the problem is that this should not be done: > -> Sort (cost=23365451.30..23366890.13 rows=575533 width=13436) > Sort Key: this_.id > because there is an index on the id column of the inherited table, so it > should be used as it is when querying the inherited table directly: > -> Index Scan using call_source_10554_id_index on call_sources_10554 > this_ (cost=0.00..23510.68 rows=575532 width=8907) > Filter: (cdr_id = 10554) The problem AFAICS is that the planner is taking the results of two tables which are ordered by id and merging them. PostgreSQL doesn't have a Merge node type so it does this by concatentating the lists and sorting again. As an example, say the results of the query on the parent table were (1,3,5) and the results on the child table (2,4,6), what the planner is doing is concatenation and then sorting. Ideally you'd want a node to simply take the sort sorted lists and merge them. Like a MergeJoin only it's not a join. Doesn't seem too hard, but someone needs to do the legwork. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
Martijn van Oosterhout wrote: > The problem AFAICS is that the planner is taking the results of two > tables which are ordered by id and merging them. PostgreSQL doesn't > have a Merge node type so it does this by concatentating the lists and > sorting again. That's definitely part of the problem. The deeper problem though is that postgres should never consider the results of the parent table at all. There is a check constraint on the inherited table (cdr_id = 10554), so all rows should come from that table, right? Or wait, maybe postgres considers rows from the parent table, because the parent table has no check constraint. Unfortunately according to the doc I cannot define a check constraint on the parent table, because it would be inherited by the other tables. There are problems with even simpler queries, e.g. "select min(id) from call_sources where cdr_id = 10554;" is doing a sequential scan. Even if it considers results from the parent table it should combine the results of two index scans.
On Wed, Jan 18, 2006 at 11:35:27AM +0100, Stephen Friedrich wrote: > Martijn van Oosterhout wrote: > >The problem AFAICS is that the planner is taking the results of two > >tables which are ordered by id and merging them. PostgreSQL doesn't > >have a Merge node type so it does this by concatentating the lists and > >sorting again. <snip> > Or wait, maybe postgres considers rows from the parent table, because > the parent table has no check constraint. Unfortunately according to the > doc I cannot define a check constraint on the parent table, because it > would be inherited by the other tables. Are you sure? I havn't tested it but the ALTER syntax allows you to say something like: ALTER TABLE ONLY foo ADD CONSTRAINT ... > There are problems with even simpler queries, e.g. > "select min(id) from call_sources where cdr_id = 10554;" is doing a > sequential scan. Even if it considers results from the parent table it > should > combine the results of two index scans. Same problem. Min/max want something sorted and the partitioning code doesn't do that yet. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.