Re: Double ocurring Subplan

On Fri, May 13, 2011 at 6:02 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> writes:
> > I am listing the query, it's explain output and explain analyze output at
> > the end.
>
> > The EXPLAIN output shows the 'Subplan 2' node only once, whereas EXPLAIN
> > ANALYZE output shows that the 'Subplan 2' is being executed twice . Is
> that
> > true? Or is it just the plan printer getting confused? Is the confusion
> > because of the 2 conditions in the WHERE clause of the correlated
> subquery?
>
> The reason it looks like that is that the SubPlan is referenced in the
> index condition, and there are actually two copies of that (indxqual and
> indxqualorig). They both point at the same physical subplan, but there
> are two entries in the parent node's subPlan list. In EXPLAIN you only
> see one because ExecInitIndexScan skips initializing the indxquals in
> EXPLAIN_ONLY mode.
>
> In short: it's cosmetic.
>

That's a relief

>
> We could probably suppress the duplicate printout when both references
> are in the same plan node, but in bitmap scans the indxqual and
> indxqualorig expressions are actually in different plan nodes (the
> indexscan and heapscan respectively). I'm not sure how we could
> suppress the duplicate printout in that case, or whether it would even
> make sense to do so --- after all, the subplan can in fact get called by
> both nodes.
>

As long as it's not being re-evaluated, it's not a big deal.

It does confuse the casual onlooker, though; and if there's any automatic
tool to parse and report explain analyze output, it might get its numbers
quite wrong.

Regards,
--
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company