Re: problem with partitioned table and indexed json field

Raphael Bauduin <rblists(at)gmail(dot)com> writes:
> The query is also problematic here, because it returns the full json, and
> not only the data I selected in the json.

Doh, right, you mentioned that in the original bug report, and now that
I'm paying a bit more attention I see it too. I was looking for
some sort of error from running the query, not just wrong data.

It looks like the problem is we're building a MergeAppend plan and not
getting the targetlist for the MergeAppend node right. I hacked EXPLAIN
very quickly to not fall over when it fails to find a sort key in the
node's targetlist, and here's what I see:

regression=# explain verbose select max(event->>'_id') from events where event is not null;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=58.75..58.76 rows=1 width=0)
Output: $0
InitPlan 1 (returns $0)
-> Limit (cost=58.70..58.75 rows=1 width=32)
Output: events.event
-> Merge Append (cost=58.70..200.88 rows=3268 width=32)
Sort Key: [no tlist entry for key 2]
-> Sort (cost=0.01..0.02 rows=1 width=32)
Output: events.event, ((events.event ->> '_id'::text))
Sort Key: ((events.event ->> '_id'::text))
-> Seq Scan on public.events (cost=0.00..0.00 rows=1 width=32)
Output: events.event, (events.event ->> '_id'::text)
Filter: ((events.event IS NOT NULL) AND ((events.event ->> '_id'::text) IS NOT NULL))
-> Sort (cost=29.20..31.92 rows=1089 width=32)
Output: events_2012_01.event, ((events_2012_01.event ->> '_id'::text))
Sort Key: ((events_2012_01.event ->> '_id'::text))
-> Seq Scan on public.events_2012_01 (cost=0.00..23.75 rows=1089 width=32)
Output: events_2012_01.event, (events_2012_01.event ->> '_id'::text)
Filter: ((events_2012_01.event IS NOT NULL) AND ((events_2012_01.event ->> '_id'::text) IS NOT NULL))
-> Sort (cost=29.20..31.92 rows=1089 width=32)
Output: events_2012_02.event, ((events_2012_02.event ->> '_id'::text))
Sort Key: ((events_2012_02.event ->> '_id'::text))
-> Seq Scan on public.events_2012_02 (cost=0.00..23.75 rows=1089 width=32)
Output: events_2012_02.event, (events_2012_02.event ->> '_id'::text)
Filter: ((events_2012_02.event IS NOT NULL) AND ((events_2012_02.event ->> '_id'::text) IS NOT NULL))
-> Index Scan Backward using events_2012_03_event_id_index on public.events_2012_03 (cost=0.15..63.30 rows=1089 width=32)
Output: events_2012_03.event, (events_2012_03.event ->> '_id'::text)
Index Cond: ((events_2012_03.event ->> '_id'::text) IS NOT NULL)
Filter: (events_2012_03.event IS NOT NULL)
(29 rows)

So everything looks right for the individual table-scan subplans, but
something's going badly wrong when making the MergeAppend ...
dunno what yet.

regards, tom lane