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? It should not take long.
Thanks,
Torsten
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
