Thread: Query plan optimization: sorting vs. partitioning

Hello list,

I have a large time-indexed table (states) partitioned into several
tables based on the date. The smaller tables are clustered by their
time indices.The main table is empty.

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?

Please CC me as I'm not on the list. Thanks in advance,

--
DoubleF

Sergey Zaharchenko <doublef.mobile@gmail.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

2011/2/2, Tom Lane <tgl@sss.pgh.pa.us>:

>> 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

OK, I hope I'll be able to come up with a stored procedure to query
the tables directly, then. Thanks!

--
DoubleF