Re: Query plan question, and a memory leak

Greg Stark <gsstark(at)mit(dot)edu> writes:
> 1) The following query has an odd plan that I can't figure out how to read. It
> seems to include the subplan twice, does that mean it's executing it twice?

If you had done EXPLAIN ANALYZE, you would know ;-)

My guess is that because the subplan is part of an indexscan qual, it
appears in both the indexqual and 'indexqualorig' lists of the indexscan
node. The copy in 'indexqualorig' will never be executed in this
example, though it could be if there were a multiple-index-scan plan
involved.

> Even twice doesn't explain the cost which is much higher than similar plans
> that don't trigger the duplicate subplan. What am I doing wrong to trigger
> this behaviour?

The inner nested loop's cost looks in line to me for one execution of
the subplan and one execution of the inner indexscan for each tuple of
the outer table (ad). Given that the subplan depends on ad.ad_id
there's really not any way to avoid re-executing it for each row of ad.

The reason the outer nested loop looks so bad is you've got an
unconstrained join to gg...

> 2) The version of the query at the bottom appears to trigger a big memory
> leak. The only difference is the addition of a "WHERE geom2 @ make_box()"
> clause. (make_box returns a box, the definition is below).

What datatype is geom2?

regards, tom lane