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 <pgsql-sql-postgresql.org@postgresql.org>;
	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 <pgsql-sql-postgresql.org@postgresql.org>;
	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 <pgsql-sql@postgresql.org>; Thu, 24 Aug 2006 16:01:45 -0300 (ADT)
Received: by wx-out-0506.google.com with SMTP id t13so597440wxc
	for <pgsql-sql@postgresql.org>; 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: <cf9b4f3e0608241201l51a2e61cxd15d2ddbbdd710b9@mail.gmail.com>
Date: Thu, 24 Aug 2006 15:01:39 -0400
From: "Travis Whitton" <tinymountain@gmail.com>
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 <a href="http://customer.id/" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">customer.id</a>
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.
<br><br>SELECT distinct <a href="http://customers.id/" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">customers.id</a>,
first_name, last_name, address1, contact_city, contact_state,
primary_phone, email, array(select programs.program_name from
transacts, programs where customer_id = <a href="http://customers.id/" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">customers.id</a> and <a href="http://programs.id/" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
programs.id</a>
= transacts.program_id and submit_status = 'success') AS partners from
customers, transacts, programs where transacts.customer_id = <a href="http://customers.id/" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">customers.id</a> and transacts.program_id = <a href="http://programs.id/" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
programs.id</a><br><br>

------=_Part_97430_19005267.1156446099204--