From: | Raphael Bauduin <rblists(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | problem with partitioned table and indexed json field |
Date: | 2013-10-31 10:46:12 |
Message-ID: | CAONrwUGPc4J9i=Bdta=WB51=327RWb-GoQsN00Zhtmeef_07yA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
thanks
Raph
From | Date | Subject | |
---|---|---|---|
Next Message | Rémi Cura | 2013-10-31 10:50:48 | Re: Connection pooling |
Previous Message | Jayadevan | 2013-10-31 10:10:01 | Re: Connection pooling |