Re: problem with partitioned table and indexed json field - Mailing list pgsql-general
From | Tom Lane |
---|---|
Subject | Re: problem with partitioned table and indexed json field |
Date | |
Msg-id | 20989.1383839506@sss.pgh.pa.us Whole thread Raw |
In response to | Re: problem with partitioned table and indexed json field (Raphael Bauduin <rblists@gmail.com>) |
Responses |
Re: problem with partitioned table and indexed json field
|
List | pgsql-general |
Raphael Bauduin <rblists@gmail.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 NOTNULL)) -> 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 NOTNULL)) -> Index Scan Backward using events_2012_03_event_id_index on public.events_2012_03 (cost=0.15..63.30rows=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
pgsql-general by date: