Partitioned table question

From: Torsten Förtsch <torsten(dot)foertsch(at)gmx(dot)net>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Partitioned table question
Date: 2013-11-13 11:22:27
Message-ID: 528360F3.6010906@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

we have a table partitioned by time. Each month goes into a separate
child table. Primary key in each table is (underlying, ts). The
resulting index is perfect for ordering like in the query below. Each
child table has a constraint like:

CHECK(ts>= '2011-1-1' and ts<'2011-1-1'::DATE + interval '1 month')

Now, we have queries of this type:

SELECT * FROM tick
WHERE underlying = 'R_50' AND ts <= '2013-05-02'
ORDER BY ts DESC LIMIT 100

The query plan for this is at http://explain.depesz.com/s/fB6

According to this plan it fetches all the result tuples from tick_2013_4
which is fine because tick_2013_5 obviously does not contain matches.

My question is, why does it then try to fetch one row from every other
index? Can that be avoided without a lower bound on ts?

Thanks,
Torsten

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alexander Farber 2013-11-13 11:28:49 Can't find /var/lib/pgsql/9.3/data/global/pg_auth
Previous Message Chris Travers 2013-11-13 08:05:17 select ... inherits?