Re: Query optimization

Jorge Arevalo <jorgearevalo@libregis.org> writes:
>> On Wed, Oct 29, 2014 at 11:53 AM, Tom Lane <tgl@sss.pgh.pa.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