Re: Partitioned table question

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?

>
> Thanks,
> Torsten
>