Query plan optimization: sorting vs. partitioning

From: Sergey Zaharchenko <doublef(dot)mobile(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Query plan optimization: sorting vs. partitioning
Date: 2011-02-02 11:49:04
Message-ID: AANLkTi=Yg_Vk_YE5tsWBBz+g7rT2zhjf+7ummVMTgV6M@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sachin Srivastava 2011-02-02 12:09:14 Re: Installation Issue of PostgresPlus-9.0
Previous Message Wouter D'Haeseleer 2011-02-02 11:03:52 Hot-Standby and sequences