On 11/13/2013 08:26 AM, Torsten Förtsch wrote:
On 13/11/13 13:49, Gabriel Sánchez Martínez wrote:
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.
It probably does. According to the "analyze" part of the query plan it
does not find any match in 2013_5. But from 2013_4 it fetches 100 rows.
-> Index Scan Backward using tick_2013_4_pkey on tick_2013_4 tick
(cost=0.00..5025184.53 rows=1336481 width=40)
(actual time=0.047..0.124 rows=100 loops=1) <== rows=100
Of course, it's a good idea to add a lower bound to the query.
I also know that the planner does not know how many rows it can fetch
from each table (it can have a quite accurate guess though). So, the
plan must include all tables before and including 2013_5.
The question, however, was why does the executor try to fetch rows from
the other tables at all.
I suspect it is because the checks are used just for checking and table
exclusion, not for ordering. The planner does not understand the logic
of how your check constraints are set up, so it does not have a
guarantee that after scanning through 2013_4 there will be no more rows
that should enter the result set in other tables. So all tables pass
the check constraints and none are excluded, and then index scans are
used to figure out everything else from there on.
I don't work with the PostgreSQL source code (I'm just answering based
on what I've observed in my experience as a user, experimenting with
partitioning and constraint exclusion), so perhaps others in the list
who are closer to the source can chime in.
-Gabriel
Torsten
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
