Обсуждение: strange explain in upstream - subplan 1 twice - is it bug?

On Wed, Jun 1, 2016 at 7:29 AM, Pavel Stehule <pavel.stehule@gmail.com> 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

On Thu, Jul 7, 2016 at 4:13 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> 2016-07-07 21:57 GMT+02:00 Robert Haas <robertmhaas@gmail.com>:
>> On Wed, Jun 1, 2016 at 7:29 AM, Pavel Stehule <pavel.stehule@gmail.com>
>> 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

Robert Haas <robertmhaas@gmail.com> writes:
> On Wed, Jun 1, 2016 at 7:29 AM, Pavel Stehule <pavel.stehule@gmail.com> 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