| From: | David Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> |
|---|---|
| To: | Jorge Arevalo <jorgearevalo(at)libregis(dot)org> |
| Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: Query optimization |
| Date: | 2014-10-29 19:47:53 |
| Message-ID: | CAKFQuwb4_k86x7_J4Hx6KOCzkgCY8u49cbsdqdRV-B_saa6HSg@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Jorge Arevalo | 2014-10-29 20:26:01 | Re: Query optimization |
| Previous Message | Tom Lane | 2014-10-29 19:39:42 | Re: Query optimization |