Re: Query optimization

Jorge Arevalo <jorgearevalo(at)libregis(dot)org> writes:
>> On Wed, Oct 29, 2014 at 11:53 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> I'm also a bit confused as to why the planner is saying that the (SELECT
>>> ARRAY(...)) bit is an InitPlan and not a SubPlan. That implies that
>>> "field7" in the innermost WHERE clause is not a reference to table1 but a
>>> reference to table2. Is that really what you meant? IOW, are you sure
>>> this query is performing the right calculation in the first place?

> Yes, for each row of table1, table2 is being scanned, to find all the
> entries that satisfy table1.field7 = table2.f3.

You might think that's what it's doing, but the plan shape says otherwise.
An array of 2537787 entries is being formed *once* and then referenced at
each row of table1. I'd take another look and see if "field7" isn't a
column name that exists in both tables.

regards, tom lane