From: | Gabriel Sánchez-Martínez <gsanmar(at)MIT(dot)EDU> |
---|---|
To: | Torsten Förtsch <torsten(dot)foertsch(at)gmx(dot)net>, Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Partitioned table question |
Date: | 2013-11-13 12:45:23 |
Message-ID: | 52837463.6020003@mit.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Gabriel Sánchez Martínez | 2013-11-13 12:49:18 | Re: Partitioned table question |
Previous Message | Devrim GÜNDÜZ | 2013-11-13 12:07:04 | Re: Can't find /var/lib/pgsql/9.3/data/global/pg_auth |