Re: Partitioned table question - Mailing list pgsql-general

From Torsten Förtsch
Subject Re: Partitioned table question
Date
Msg-id 52837E0F.6060301@gmx.net
Whole thread Raw
In response to Re: Partitioned table question  (Gabriel Sánchez Martínez<gabrielesanchez@gmail.com>)
Responses Re: Partitioned table question
Re: Partitioned table question
List pgsql-general
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.

Torsten


pgsql-general by date:

Previous
From: Pavel Stehule
Date:
Subject: Re: Return only non-null columns
Next
From: Tom Lane
Date:
Subject: Re: select ... inherits?