From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Raphael Bauduin <rblists(at)gmail(dot)com> |
Cc: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: problem with partitioned table and indexed json field |
Date: | 2013-10-31 12:48:21 |
Message-ID: | CAHyXU0xfEEnh4zhzK8spCW+s+NRRGTi8Y8cL8WzfUUDnBhz3Qw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Oct 31, 2013 at 5:46 AM, Raphael Bauduin <rblists(at)gmail(dot)com> wrote:
>
> Hi,
>
> I have a partitioned table events, with one partition for each month, eg
> events_2013_03. The partition is done on the field timestamp, and
> constraints are set, but insertion of data is done in the partition directly
> (so not with a trigger on the events table)
> The field event is of type json, and has a field '_id', which I can access:
>
> => select event->>'_id' from events limit 1;
> ?column?
> --------------------------
> 4f9a786f44650105b50aafc9
>
> I created an index on each partition of the table, but not on the events
> table itself:
> create index events_${y}_${m}_event_id_index on events_${y}_${m}
> ((event->>'_id'));
>
> Querying the max event_id from a partition works fine:
> => select max(event->>'_id') from events_2013_03;
> max
> --------------------------
> 5158cdfe4465012cff522b74
>
>
> However, requesting on the parent table does return the whole json field,
> and not only the '_id':
> => select max(event->>'_id') from events;
> {"_id":"526eb3ad4465013e3e131a43","origin":..... }
>
> An explain returns an error:
> => explain select max(event->>'_id') from events;
> ERROR: no tlist entry for key 2
>
> This problem appeared when I created the indexes, and removing the index
> make the explain work fine, but the plan implies a sequential scan on the
> tables which is exactly what I wanted to avoid with the indexes.
>
> Does someone have an explanation, and possibly a way to solve this problem?
wow, that looks like a bug. Can you post the specific postgres version?
merlin
From | Date | Subject | |
---|---|---|---|
Next Message | Raphael Bauduin | 2013-10-31 13:21:03 | Re: problem with partitioned table and indexed json field |
Previous Message | si24 | 2013-10-31 12:34:55 | Re: Connection pooling |