Re: Partitioning: Planner makes no use of indexes on inherited

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(at)svana(dot)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.