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