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 787489FA4A5 for ; Thu, 29 Jun 2006 12:25:15 -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 10079-02 for ; Thu, 29 Jun 2006 12:25:07 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.171]) by postgresql.org (Postfix) with ESMTP id 222C09FA081 for ; Thu, 29 Jun 2006 12:25:06 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id a2so391389ugf for ; Thu, 29 Jun 2006 08:25:06 -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=XCEKNOM1nSGJDs08Q0+qE9JbPLbJr2TbraLHyahzEYpWI9o1Zydjy4xWbMYYTHDyXyMuQRe228dmiMN7CQqHiGnjo6HqUnBE7RO7H2TI9f0GCUFK6YvkK2OXzvHMnJp0YrvJ4haxRECw3XHJM3WkhO/5bO69bOOGOUO1ncfG69s= Received: by 10.78.156.6 with SMTP id d6mr1015032hue; Thu, 29 Jun 2006 08:25:05 -0700 (PDT) Received: by 10.78.29.2 with HTTP; Thu, 29 Jun 2006 08:25:05 -0700 (PDT) Message-ID: Date: Thu, 29 Jun 2006 10:25:05 -0500 From: "Aaron Bono" To: "Richard Broersma Jr" Subject: Re: SELECT Aggregate Cc: "Phillip Smith" , pgsql-sql@postgresql.org In-Reply-To: <20060629052335.51714.qmail@web31812.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_21471_30726053.1151594705920" References: <00d001c69b33$bfa13b90$9b0014ac@ITPhil> <20060629052335.51714.qmail@web31812.mail.mud.yahoo.com> X-Google-Sender-Auth: 634a7151349745e0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/315 X-Sequence-Number: 25551 ------=_Part_21471_30726053.1151594705920 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline I am not familiar enough with how postgres optimizes the queries but won't this end up with total number of queries run on DB = 1 query + 1 query/row in first query What would be more efficient on a large database - a query like Richard submitted (subquery in the select) or one like I submitted (join the two tables and then do a group by)? My guess is it depends on the % of records returned out of the sales_orders table, the smaller the % the better Richard's query would perform, the higher the % the better the join would run. The database I am working with aren't big enough yet to warrant spending a lot of time researching this but if someone with more experience knows what is best I would love to hear about it. Thanks, Aaron Bono On 6/29/06, Richard Broersma Jr wrote: > > > SELECT trans_no, > > customer, > > date_placed, > > date_complete, > > date_printed, > > ord_type, > > ord_status, > select ( > SUM(sell_price) > from soh_product > where sales_orders.trans_no = soh_product.soh_num > ) as transact_sum, > > customer_reference, > > salesman, > > parent_order, > > child_order, > > order_number > > FROM sales_orders > > WHERE (trans_no Like '8%' AND order_number Like '8%') > > OR (trans_no Like '9%' AND order_number Like '8%') > > OR (trans_no Like '8%' AND order_number Like '9%') > > OR (trans_no Like '9%' AND order_number Like '9%') > > AND (warehouse='M') > > AND (date_placed > (current_date + ('12 months ago'::interval))) > > ORDER BY trans_no DESC > > ------=_Part_21471_30726053.1151594705920 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline I am not familiar enough with how postgres optimizes the queries but won't this end up with

total number of queries run on DB = 1 query + 1 query/row in first query

What would be more efficient on a large database - a query like Richard submitted (subquery in the select) or one like I submitted (join the two tables and then do a group by)?  My guess is it depends on the % of records returned out of the sales_orders table, the smaller the % the better Richard's query would perform, the higher the % the better the join would run.

The database I am working with aren't big enough yet to warrant spending a lot of time researching this but if someone with more experience knows what is best I would love to hear about it.

Thanks,
Aaron Bono

On 6/29/06, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> SELECT      trans_no,
>             customer,
>             date_placed,
>             date_complete,
>             date_printed,
>             ord_type,
>             ord_status,
              select (
                         SUM(sell_price)
                         from soh_product
                         where sales_orders.trans_no = soh_product.soh_num
                      ) as transact_sum,
>             customer_reference,
>             salesman,
>             parent_order,
>             child_order,
>             order_number
> FROM        sales_orders
> WHERE       (trans_no Like '8%' AND order_number Like '8%')
>  OR         (trans_no Like '9%' AND order_number Like '8%')
>  OR         (trans_no Like '8%' AND order_number Like '9%')
>  OR         (trans_no Like '9%' AND order_number Like '9%')
>  AND        (warehouse='M')
>  AND        (date_placed > (current_date + ('12 months ago'::interval)))
> ORDER BY trans_no DESC

------=_Part_21471_30726053.1151594705920--