Re: Query optimization

On Wed, Oct 29, 2014 at 8:47 PM, David Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Wed, Oct 29, 2014 at 12:14 PM, Jorge Arevalo <jorgearevalo(at)libregis(dot)org
> > wrote:
>
>>
>> SELECT value1,value2,value3,value4, value5, hstore(ARRAY['field9',
>> 'field10', 'field11', 'field12', 'field13', 'field14'], ARRAY[field9,
>> field10, field11, field12, field13, field14]) as metadata, value7, (select
>> array((select row(f1, f2) from table2 p where p.f3 = field7))) as
>> values_array FROM table1
>>
>
> ​More generally, you really should table-prefix all column in correlated
> subqueries.
>
> [...] from table2 p where p.f3 = table1.field7 [...]
>
> ​I guess the InitPlan 1 you showed simply scanned table2 and applied the
> filter which then was fed to InitPlan 2 where the array is built; that
> array then is inserted into the outer query ~8M​ times...
>
> David J.
>
>
Wow, you were right! There was a field with same name in both tables, and
that caused problems. I've just prefixed each field with the table
identifier, and now it works really fast.

Many thanks, guys!

--
Jorge Arevalo
Freelance developer

http://about.me/jorgeas80