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: Sun, 27 Aug 2006 23:10:06 -0500
Message-ID: <bf05e51c0608272110t6e994e09n7922e494fe62cd2f@mail.gmail.com> (raw)
In-Reply-To: <cf9b4f3e0608241201l51a2e61cxd15d2ddbbdd710b9@mail.gmail.com>
References: <cf9b4f3e0608241201l51a2e61cxd15d2ddbbdd710b9@mail.gmail.com>
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: <bf05e51c0608272110t6e994e09n7922e494fe62cd2f@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