Search Postgresql Archives

Re: Partitioned table question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




On 11/13/2013 06:22 AM, Torsten Förtsch wrote:
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
In the query plan the condition shown is ... AND ts <= '2013-05-01' Did you mean 01 in the above query?

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.
Since the constraint is not strict (i.e. you allow dates equal to 2013-05-01 to pass), the 2013-05 table has to be scanned.

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?
If you don't set a lower bound, since every other table has dates below 2013-05-01, they have to be scanned too. I'm not sure what happens on actual execution if it searches in '2013_4' first and finds 100 or more rows. I don't know if the query planner uses constraint exclusion rules to figure out the order in which tables will be scanned. I suspect not, because I've read and seen that the constraint exclusion rules behavior is rather simple. If you set a lower bound the constraint exclusion rule should kick in and limit the tables searched. Have you tried that?


Thanks,
Torsten




--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux