From tinymountain@gmail.com Thu Aug 24 19:01:54 2006 X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id DD7469FB37E for ; Thu, 24 Aug 2006 16:01:54 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 37358-06 for ; Thu, 24 Aug 2006 16:01:46 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.235]) by postgresql.org (Postfix) with ESMTP id 282829FB24D for ; Thu, 24 Aug 2006 16:01:45 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id t13so597440wxc for ; Thu, 24 Aug 2006 12:01:44 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=MFLL0ERzltBBVWfyohdo0MRxMhJ9umiXg4ru366kXiSY1wrbZBj2Bz6hlv1FwArMLd3zDWQYUzLtbhsNJhUewA8pWvShKyzmh3Az85D4EgIFtsrmElyzIobWYpYnrkaNvWalJZXIjVVaafPuK/dNndExZzJidAH16dMlN5x1HYk= Received: by 10.70.32.13 with SMTP id f13mr3047849wxf; Thu, 24 Aug 2006 12:01:39 -0700 (PDT) Received: by 10.70.72.14 with HTTP; Thu, 24 Aug 2006 12:01:39 -0700 (PDT) Message-ID: Date: Thu, 24 Aug 2006 15:01:39 -0400 From: "Travis Whitton" To: pgsql-sql@postgresql.org Subject: Performance Problem with sub-select using array MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_97430_19005267.1156446099204" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.574 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, HTML_30_40, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/186 X-Sequence-Number: 26144 ------=_Part_97430_19005267.1156446099204 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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.idand transacts.program_id = programs.id ------=_Part_97430_19005267.1156446099204 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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

------=_Part_97430_19005267.1156446099204-- From postgresql@aranya.com Mon Aug 28 04:10:12 2006 X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id B6A149FC901 for ; Mon, 28 Aug 2006 01:10:12 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 12983-02 for ; Mon, 28 Aug 2006 01:10:08 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.177]) by postgresql.org (Postfix) with ESMTP id 310DF9FC8FF for ; Mon, 28 Aug 2006 01:10:07 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id d80so2267890pyd for ; Sun, 27 Aug 2006 21:10:07 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=qDazoY1cIBYzKlsq0y2hwGqFp8MbVPRXDnHowwbHyGPm4dyBbSlAA0aOqvyVJj2KURVUWJqzTeQ6Ss/5tPGgxyIItylujthhViS8EeMPrDw0l7/09OesxiysxAjbsBHER+/L/CBpGX1kGzwNNHcsdS+BsAwdgLIus/eS8g+Y11g= Received: by 10.35.38.17 with SMTP id q17mr11479213pyj; Sun, 27 Aug 2006 21:10:06 -0700 (PDT) Received: by 10.35.80.9 with HTTP; Sun, 27 Aug 2006 21:10:06 -0700 (PDT) Message-ID: Date: Sun, 27 Aug 2006 23:10:06 -0500 From: "Aaron Bono" To: "Travis Whitton" Subject: Re: Performance Problem with sub-select using array Cc: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_212698_5678244.1156738206071" References: X-Google-Sender-Auth: 8430b5c22b167806 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.694 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_30_40, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/209 X-Sequence-Number: 26167 ------=_Part_212698_5678244.1156738206071 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 8/24/06, Travis Whitton 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.com http://codeelixir.com ================================================================== ------=_Part_212698_5678244.1156738206071 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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.com
   http://codeelixir.com
================================================================== ------=_Part_212698_5678244.1156738206071-- From postgresql@aranya.com Tue Aug 29 14:28:52 2006 X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 78E569FB24D for ; Tue, 29 Aug 2006 11:28:52 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 59145-07 for ; Tue, 29 Aug 2006 11:28:46 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.179]) by postgresql.org (Postfix) with ESMTP id 707F09FB227 for ; Tue, 29 Aug 2006 11:28:46 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id d80so2861356pyd for ; Tue, 29 Aug 2006 07:28:45 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=iW+llCvmmGkKKNTLHfEucqu8PkAPaxDVrj13R0RAD/fV5oLuOPIpkiEHGCfHx7gH4hI+T2ajDAbfrQcbny/rAR6wzlSPm4p3PzdoqyB8o5sUeJQ79Z9ObflJXTIkVPBcv3BuH2gHHrKt6foRV4AdwlFPOvuP67Vz1R9GG78/PUI= Received: by 10.35.61.2 with SMTP id o2mr15032621pyk; Tue, 29 Aug 2006 07:28:42 -0700 (PDT) Received: by 10.35.80.9 with HTTP; Tue, 29 Aug 2006 07:28:42 -0700 (PDT) Message-ID: Date: Tue, 29 Aug 2006 09:28:42 -0500 From: "Aaron Bono" To: "Travis Whitton" Subject: Re: Performance Problem with sub-select using array Cc: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_9814_23165280.1156861722299" References: X-Google-Sender-Auth: a55f814424d11dfa X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.737 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_40_50, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/229 X-Sequence-Number: 26187 ------=_Part_9814_23165280.1156861722299 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Make sure you do a reply to all so you include the list.... On 8/28/06, Travis Whitton wrote: > > Ok, I actually got down to business with EXPLAIN ANALYZE. My performance > was actually suffering from the DISTINCT in my SQL query and not the > subquery, which I guess isn't run repeatedly since it's not constrained and > can be cached by the optimizer? Bottom line is, by replacing DISTINCT with > DISTINCT ON all my index conditions show up in the EXPLAIN output. Best of > all: Total runtime: 353.588 ms. Thanks for the help. > > Travis > > > On 8/28/06, Travis Whitton 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. > > > > > > On 8/28/06, Aaron Bono < postgresql@aranya.com> wrote: > > > > > > On 8/24/06, Travis Whitton 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.com http://codeelixir.com ================================================================== ------=_Part_9814_23165280.1156861722299 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Make sure you do a reply to all so you include the list....

On 8/28/06, Travis Whitton <tinymountain@gmail.com > wrote:
Ok, I actually got down to business with EXPLAIN ANALYZE. My performance was actually suffering from the DISTINCT in my SQL query and not the subquery, which I guess isn't run repeatedly since it's not constrained and can be cached by the optimizer? Bottom line is, by replacing DISTINCT with DISTINCT ON all my index conditions show up in the EXPLAIN output. Best of all: Total runtime: 353.588 ms. Thanks for the help.

Travis


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.


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.com
   http://codeelixir.com
================================================================== ------=_Part_9814_23165280.1156861722299-- From postgresql@aranya.com Tue Aug 29 14:34:45 2006 X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id C75629FB24B for ; Tue, 29 Aug 2006 11:34:45 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 16503-09 for ; Tue, 29 Aug 2006 14:34:37 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.183]) by postgresql.org (Postfix) with ESMTP id 59C439FB227 for ; Tue, 29 Aug 2006 11:34:37 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id d80so2863197pyd for ; Tue, 29 Aug 2006 07:34:35 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=LOQdfvf9b0zKCUmwqvlFvTDw3D/+oCAYUpJUeo1vjMkkfPtVl+xB+qX18jB6OdLAhrry2tNdmd1XIAxee+VgW2gvOCQ/zLJUoUGuRSLR0EcmTZr5UmKKeyXPOpxqpbXMdPY+e4OUUhjij6xIj0zfCwH02G1ExG4NRzdwvNtevh4= Received: by 10.35.78.13 with SMTP id f13mr15044785pyl; Tue, 29 Aug 2006 07:34:35 -0700 (PDT) Received: by 10.35.80.9 with HTTP; Tue, 29 Aug 2006 07:34:35 -0700 (PDT) Message-ID: Date: Tue, 29 Aug 2006 09:34:35 -0500 From: "Aaron Bono" To: "Travis Whitton" Subject: Re: Performance Problem with sub-select using array Cc: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_9974_8455541.1156862075519" References: X-Google-Sender-Auth: 5068ca5ec472c3bb X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.696 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, HTML_40_50, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/230 X-Sequence-Number: 26188 ------=_Part_9974_8455541.1156862075519 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 8/28/06, Travis Whitton 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 wrote: > > > > On 8/24/06, Travis Whitton 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.com http://codeelixir.com ================================================================== ------=_Part_9974_8455541.1156862075519 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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.com
   http://codeelixir.com
================================================================== ------=_Part_9974_8455541.1156862075519--