From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jorge Arevalo <jorgearevalo@libregis.org>
Cc: David G Johnston <david.g.johnston@gmail.com>
Cc: pgsql-general@postgresql.org
Subject: Re: Query optimization
Date: Wed, 29 Oct 2014 14:53:18 -0400
Message-ID: <29612.1414608798@sss.pgh.pa.us> (raw)
In-Reply-To: <CAMhtMNOoGMB3fSMdjjYf2ukq=DmhDp4eHsnM8H_cdfATCjTRyQ@mail.gmail.com>
References: <CAMhtMNNTDmcTXZ8yjyxtaXvaYYaW04_tnqXRqajH=Kz1Dswmtw@mail.gmail.com>
<1414547659900-5824746.post@n5.nabble.com>
<CAMhtMNOoGMB3fSMdjjYf2ukq=DmhDp4eHsnM8H_cdfATCjTRyQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>
Jorge Arevalo <jorgearevalo@libregis.org> writes:
> 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
The parentheses/brackets don't seem to match up real well here ...
> This is the result of EXPLAIN ANALYZE
> QUERY
> PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------------
> Index Scan using table1_pkey on table1 (cost=67846.38..395773.45
> rows=8419127 width=88) (actual time=7122.704..22670.680 rows=8419127
> loops=1)
> InitPlan 2 (returns $1)
> -> Result (cost=67846.29..67846.29 rows=1 width=0) (actual
> time=7009.063..7009.065 rows=1 loops=1)
> InitPlan 1 (returns $0)
> -> Seq Scan on table2 p (cost=0.00..67846.29 rows=12689
> width=20) (actual time=14.971..5069.840 rows=2537787 loops=1)
> Filter: (f3 = field7)
Hm. If I'm reading that right, you're building an array containing
2537787 entries, each of which is a composite datum containing two
columns of unmentioned datatypes. I suspect a big chunk of your
runtime is going into manipulating that array -- PG is not terribly
efficient with big arrays containing variable-width values.
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?
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: tgl@sss.pgh.pa.us, jorgearevalo@libregis.org, david.g.johnston@gmail.com
Subject: Re: Query optimization
In-Reply-To: <29612.1414608798@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox