Re: Query plan optimization: sorting vs. partitioning

Sergey Zaharchenko <doublef(dot)mobile(at)gmail(dot)com> writes:
> I need to select some data in the time order. When I query a separate
> smaller table, the index is used an no sorting is needed. However,
> when I query the main table, it occurs:
> ...
> -> Sort ...
> Sort Key: ...
> Sort Method: ...
> -> Result ...
> -> Append ...
> -> Seq Scan on states
> Filter: ...
> -> Seq Scan on states_20101206
> Filter: ...
> ...

> I see the database doesn't understand that there are no entries in the
> main table, so it has to assume the Append data is not ordered. Is
> there a way to avoid sorting?

No. In existing releases there is no plan type that can produce
presorted output from an append relation (ie, an inheritance tree).
9.1 will be able to do that, but it wasn't exactly a small fix:
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=11cad29c91524aac1d0b61e0ea0357398ab79bf8

regards, tom lane