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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings