From: Aaron Bono <postgresql@aranya.com>
To: Richard Broersma Jr <rabroersma@yahoo.com>
Cc: Phillip Smith <phillips@weatherbeeta.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: SELECT Aggregate
Date: Thu, 29 Jun 2006 10:25:05 -0500
Message-ID: <bf05e51c0606290825s218dac29veda8a8ec03ff1360@mail.gmail.com> (raw)
In-Reply-To: <20060629052335.51714.qmail@web31812.mail.mud.yahoo.com>
References: <00d001c69b33$bfa13b90$9b0014ac@ITPhil>
<20060629052335.51714.qmail@web31812.mail.mud.yahoo.com>
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
>
>
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, rabroersma@yahoo.com, phillips@weatherbeeta.com.au
Subject: Re: SELECT Aggregate
In-Reply-To: <bf05e51c0606290825s218dac29veda8a8ec03ff1360@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