From: Aaron Bono <postgresql@aranya.com>
To: Travis Whitton <tinymountain@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Performance Problem with sub-select using array
Date: Tue, 29 Aug 2006 09:34:35 -0500
Message-ID: <bf05e51c0608290734w7795d260q160abc9b91e8a58e@mail.gmail.com> (raw)
In-Reply-To: <cf9b4f3e0608281857s4e82f40ib18632daa0d66221@mail.gmail.com>
References: <cf9b4f3e0608241201l51a2e61cxd15d2ddbbdd710b9@mail.gmail.com>
<bf05e51c0608272110t6e994e09n7922e494fe62cd2f@mail.gmail.com>
<cf9b4f3e0608281857s4e82f40ib18632daa0d66221@mail.gmail.com>
On 8/28/06, Travis Whitton <tinymountain@gmail.com> wrote:
>
> I'm pretty sure you're right, which leads me to my next question. Is it
> possible to pass a column from an outer query to a subquery? For example, is
> there a way to do something like.
>
> SELECT owners.id AS owner_id, array(SELECT dogs.name WHERE owners.id =
> owner_id) ...
>
> I would just do a normal inner-join, but then I get a row for each item
> that would otherwise come back nicely packaged in the array. The overhead of
> rearranging the data takes even more time than the subquery approach.
>
I don't think you can do that but I may be wrong. I usually try to stay
away from correlated sub-queries because of performance concerns and query
complexity. I find simple subqueries with well formed inner/outer joins
work much better.
Does anyone know where documentation about the array function can be found?
I did a search but cannot find it on the postgresql web site.
On 8/28/06, Aaron Bono <postgresql@aranya.com> wrote:
> >
> > On 8/24/06, Travis Whitton <tinymountain@gmail.com > wrote:
> >
> > > Hello all, I'm running the following query on about 6,000 records
> > > worth of data, and it takes about 8 seconds to complete. Can anyone provide
> > > any suggestions to improve performance? I have an index on two columns in
> > > the transacts table (program_id, customer_id). If I specify a number for
> > > customer.id in the sub-select, query time is reduced to about 2
> > > seconds, which still seems like a million years for only 6,000 records, but
> > > I'm guessing that the sub-select can't resolve the id since it's done before
> > > the outer query, so it scans the entre recordset for every row? Transacts is
> > > a many to many table for customers and programs. I know this query doesn't
> > > even reference any columns from programs; however, I dynamically insert
> > > where clauses to constrain the result set.
> > >
> > > SELECT distinct customers.id, first_name, last_name, address1,
> > > contact_city, contact_state, primary_phone, email, array(select
> > > programs.program_name from transacts, programs where customer_id =
> > > customers.id and programs.id = transacts.program_id and submit_status
> > > = 'success') AS partners from customers, transacts, programs where
> > > transacts.customer_id = customers.id and transacts.program_id =
> > > programs.id
> > >
> >
> >
> > My guess is that your problem is that you may be getting 6000 rows, but
> > the array(select ....) is having to run once for each of record returned (so
> > it is running 6000 times).
> >
> > Try an explain analyze: http://www.postgresql.org/docs/7.4/interactive/sql-explain.html
> > - that will reveal more of where the performance problem is.
> >
>
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.comhttp://codeelixir.com
==================================================================
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-sql@postgresql.org
Cc: postgresql@aranya.com, tinymountain@gmail.com
Subject: Re: Performance Problem with sub-select using array
In-Reply-To: <bf05e51c0608290734w7795d260q160abc9b91e8a58e@mail.gmail.com>
* 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