Re: [HACKERS] strange explain in upstream - subplan 1 twice - is it bug?
Robert Haas writes: > On Wed, Jun 1, 2016 at 7:29 AM, Pavel Stehule wrote: >> When I tested some queries, I found strange plan >> postgres=# explain analyze select s.nazev, o.nazev, o.pocet_obyvatel from >> (select nazev, array(select id from obce_pocet_obyvatel where okresy.id = >> okres_id order by pocet_obyvatel desc limit 3) as obceids from okresy) s >> join obce_pocet_obyvatel o on o.id = ANY(obceids) order by 1, 3 desc; > The EXPLAIN plan you posted certainly looks weird, since I wouldn't > expect SubPlan 1 to be displayed twice, but I'm wondering if it's a > display artifact rather than an actual defect in the plan. It is an artifact. The reason is that the same SubPlan appears in both indexqual and indexqualorig of the IndexScan node. (I'm not sure it's physically the same SubPlan node both places, and indeed that might vary depending on whether the plan tree had gotten copied; but they've got the same plan_id and thus refer to the same sub-plan within the PlannedStmt's subplans list.) We run ExecInitExpr on both, so we end up with two SubPlanState nodes that are both linked into the IndexScanState's subPlan list, and that's what explain.c prints from. They're pointing at the same subplan state tree, which is why you always see identical stats. The reason you don't see two copies without ANALYZE is that in EXPLAIN_ONLY mode, ExecInitIndexScan quits before doing ExecIndexBuildScanKeys, so the indexqual copy isn't ExecInitExpr'd. A crude way to improve this would be to have ExplainSubPlans check for duplicate plan_id values and not print the same plan_id more than once. I think we might have to do that globally across the whole plan tree, not just per invocation of ExplainSubPlans, because in bitmap scan cases the "indexqualorig" equivalent is in the parent BitmapHeapScan node while the "indexqual" equivalent is in the child BitmapIndexScan. So the duplicate subplans might not be in the same plan node's subPlan list. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] strange explain in upstream - subplan 1 twice - is it bug?
On Thu, Jul 7, 2016 at 4:13 PM, Pavel Stehule wrote: > 2016-07-07 21:57 GMT+02:00 Robert Haas : >> On Wed, Jun 1, 2016 at 7:29 AM, Pavel Stehule >> wrote: >> > Hi >> > >> > When I tested some queries, I found strange plan >> > >> > postgres=# explain analyze select s.nazev, o.nazev, o.pocet_obyvatel >> > from >> > (select nazev, array(select id from obce_pocet_obyvatel where okresy.id >> > = >> > okres_id order by pocet_obyvatel desc limit 3) as obceids from okresy) s >> > join obce_pocet_obyvatel o on o.id = ANY(obceids) order by 1, 3 desc; >> >> The EXPLAIN plan you posted certainly looks weird, since I wouldn't >> expect SubPlan 1 to be displayed twice, but I'm wondering if it's a >> display artifact rather than an actual defect in the plan. >> >> Just out of curiosity, what does the output look like with FORMAT JSON >> or similar? > > The test case was wrong, the view "" is necessary > > create view obce_pocet_obyvatel as select id, okres_id, nazev, pocet_muzu + > pocet_zen as pocet_obyvatel from obce; > > But the result is same (explain is ok, explain analyze is broken): Hmm, so it looks like the subplan is somehow getting into it's parents subPlan list twice. I guess ExecInitExpr must reach that subplan twice via two different paths, but I'm not quite sure how that's happening. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] strange explain in upstream - subplan 1 twice - is it bug?
2016-07-07 21:57 GMT+02:00 Robert Haas :
> On Wed, Jun 1, 2016 at 7:29 AM, Pavel Stehule
> wrote:
> > Hi
> >
> > When I tested some queries, I found strange plan
> >
> > postgres=# explain analyze select s.nazev, o.nazev, o.pocet_obyvatel from
> > (select nazev, array(select id from obce_pocet_obyvatel where okresy.id
> =
> > okres_id order by pocet_obyvatel desc limit 3) as obceids from okresy) s
> > join obce_pocet_obyvatel o on o.id = ANY(obceids) order by 1, 3 desc;
>
> The EXPLAIN plan you posted certainly looks weird, since I wouldn't
> expect SubPlan 1 to be displayed twice, but I'm wondering if it's a
> display artifact rather than an actual defect in the plan.
>
> Just out of curiosity, what does the output look like with FORMAT JSON
> or similar?
>
The test case was wrong, the view "" is necessary
create view obce_pocet_obyvatel as select id, okres_id, nazev, pocet_muzu +
pocet_zen as pocet_obyvatel from obce;
But the result is same (explain is ok, explain analyze is broken):
┌──┐
│ QUERY
PLAN │
╞══╡
│
[
↵│
│
{
↵│
│ "Plan":
{ ↵│
│ "Node Type":
"Sort", ↵│
│ "Startup Cost":
1599.86, ↵│
│ "Total Cost":
1601.79,↵│
│ "Plan Rows":
769, ↵│
│ "Plan Width":
24, ↵│
│ "Actual Startup Time":
9.525, ↵│
│ "Actual Total Time":
9.547, ↵│
│ "Actual Rows":
227, ↵│
│ "Actual Loops":
1,↵│
│ "Sort Key": ["okresy.nazev", "((obce.pocet_muzu + obce.pocet_zen))
DESC"],↵│
│ "Sort Method":
"quicksort", ↵│
│ "Sort Space Used":
44,↵│
│ "Sort Space Type":
"Memory", ↵│
│ "Plans":
[↵│
│
{
↵│
│ "Node Type": "Nested
Loop", ↵│
│ "Parent Relationship":
"Outer", ↵│
│ "Join Type":
"Inner", ↵│
│ "Startup Cost":
13.95,↵│
│ "Total Cost":
1563.00,↵│
│ "Plan Rows":
769, ↵│
│ "Plan Width":
24, ↵│
│ "Actual Startup Time":
0.212, ↵│
│ "Actual Total Time":
8.991, ↵│
│ "Actual Rows":
227, ↵│
│ "Actual Loops":
1,↵│
│ "Plans":
[↵│
│
{ ↵│
│ "Node Type": "Seq
Scan", ↵│
│ "Parent Relationship":
"Outer", ↵│
│ "Relation Name":
"okresy",↵│
│ "Alias":
"okresy",↵│
│ "Startup Cost":
0.00, ↵│
│ "Total Cost":
1.77, ↵│
│ "Plan Rows":
77, ↵│
│ "Plan Width":
17, ↵│
│ "Actual Startup Time":
0.016, ↵│
│ "Actual Total Time":
0.042, ↵│
│ "Actual Rows":
77,↵│
│ "Actual Loops":
1 ↵│
│
}, ↵│
│
{ ↵│
│
Re: [HACKERS] strange explain in upstream - subplan 1 twice - is it bug?
On Wed, Jun 1, 2016 at 7:29 AM, Pavel Stehule wrote: > Hi > > When I tested some queries, I found strange plan > > postgres=# explain analyze select s.nazev, o.nazev, o.pocet_obyvatel from > (select nazev, array(select id from obce_pocet_obyvatel where okresy.id = > okres_id order by pocet_obyvatel desc limit 3) as obceids from okresy) s > join obce_pocet_obyvatel o on o.id = ANY(obceids) order by 1, 3 desc; The EXPLAIN plan you posted certainly looks weird, since I wouldn't expect SubPlan 1 to be displayed twice, but I'm wondering if it's a display artifact rather than an actual defect in the plan. Just out of curiosity, what does the output look like with FORMAT JSON or similar? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
