Re: ORDER BY and LIMIT not propagated on inherited

Simon Riggs wrote:
>>The query plan generated when running the query on a table which has
>>inheritance forces the planner to choose a seq_scan for each table.
>>Wouldn't be a good thing to also promote ORDER BYs and LIMITs to each
>>subscan (like WHERE does)?
>
> The tuple_fraction implied by LIMIT is already passed through to each
> child table when using an inherited table structure. This would then be
> taken into account when plans are made for each child table. I don't
> think the situation you observe occurs as a result of query planning.
>
> Do your child tables have indexes on them? Indexes are not inherited
> onto child tables, so it is possible that there is no index for the
> planner to elect to use.

In this cases the tuple_fraction is useless if the planner doesn't know
that a ORDER BY on each child table is requested. In fact the sort is
applied after all the rows are appended. The correct strategy IMHO would
be applying the order by and limit for each child table (which results
in an index scan, if possible), appending, then finally sorting a bunch
of rows, and limiting again.

Every table has indexes, as you can see in the third attacheed EXPLAIN
ANALYZE output.


Best regards
--
Matteo Beccati
http://phpadsnew.com
http://phppgads.com