Re: Partitioned table question

On Wed, Nov 13, 2013 at 5:26 AM, Torsten Förtsch
<torsten(dot)foertsch(at)gmx(dot)net>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.
>

The planner uses the check constraints to reason about the relation between
each partition separately and the query, not between the different
partitions. So while it may be possible to know that all rows in 2013_4
must be greater than all in 2013_3, it doesn't make use of that, instead
taking the greatest value from each partition and putting it in a priority
queue. So the one row from each table acts as a sentinel to prove that more
rows from that table are not needed.

Cheers,

Jeff