Re: SELECT Aggregate - Mailing list pgsql-sql

<div class="Section1"><p class="MsoPlainText"><font face="Times New Roman" size="3"><span
style="font-size:12.0pt;font-family:"TimesNew Roman"">Well whatdyaknow?? Being a Postgres newbie I hadn't even played
withindexes yet. They're awesome!!</span></font><p class="MsoPlainText"><font face="Times New Roman" size="3"><span
style="font-size:12.0pt;font-family:"TimesNew Roman""> </span></font><p class="MsoPlainText"><font face="Times New
Roman"size="3"><span style="font-size:12.0pt;font-family:"Times New Roman"">Using Richard's suggestion of the
Sub-Selectin the COLUMN list, combined with adding some indexes, I can now return this in under 5
seconds!</span></font><pclass="MsoPlainText"><font face="Times New Roman" size="3"><span
style="font-size:12.0pt;font-family:"TimesNew Roman""> </span></font><p class="MsoPlainText"><font face="Times New
Roman"size="3"><span style="font-size:12.0pt;font-family:"Times New Roman"">I’ve included the new SELECT query, as well
asthe definitions of the indexes below for anyone who’s interested.</span></font><p class="MsoPlainText"><font
face="TimesNew Roman" size="3"><span style="font-size:12.0pt;font-family:"Times New Roman""> </span></font><p
class="MsoPlainText"><fontface="Times New Roman" size="3"><span style="font-size:12.0pt;font-family:"Times New
Roman"">Thanksguys!</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><b><font face="Times New Roman" size="3"><span
style="font-size:12.0pt;font-family:"TimesNew Roman";font-weight:bold">QUERY:</span></font></b><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt">SELECT      trans_no,</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">            customer,</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">            date_placed,</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">            date_complete,</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">            date_printed,</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">            <font color="red"><span style="color:red">(SELECT  SUM(sell_price)</span></font></span></font><p
class="MsoPlainText"><fontcolor="red" face="Courier New" size="2"><span
style="font-size:10.0pt;color:red">                FROM   soh_product</span></font><p class="MsoPlainText"><font
color="red"face="Courier New" size="2"><span style="font-size:10.0pt;color:red">                 WHERE 
sales_orders.trans_no= soh_product.soh_num</span></font><p class="MsoPlainText"><font color="red" face="Courier New"
size="2"><spanstyle="font-size:10.0pt;color:red">                 ) AS wholesale,</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt">            ord_type,</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">            ord_status,</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">            customer_reference,</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">            salesman,</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">            parent_order,</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">            child_order,</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">            order_number</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">FROM        sales_orders</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">WHERE       (trans_no Like '8%' AND order_number Like '8%')</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt"> OR         (trans_no Like '9%' AND order_number Like '8%')</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt"> OR         (trans_no Like '8%' AND order_number Like '9%')</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt"> OR         (trans_no Like '9%' AND order_number Like '9%')</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt"> AND        warehouse='M'</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt"> AND        date_placed > (current_date + ('12 months ago'::interval))</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt">ORDER BY    trans_no DESC</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><b><font face="Times New Roman" size="3"><span
style="font-size:12.0pt;font-family:"TimesNew Roman";font-weight:bold">INDEXES:</span></font></b><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt">CREATE INDEX sales_orders_customer</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">  ON <font color="red"><span style="color:red">sales_orders</span></font></span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt">  USING btree</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">  (customer);</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">CREATE INDEX sales_orders_orderno</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">  ON <font color="red"><span style="color:red">sales_orders</span></font></span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt">  USING btree</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">  (order_number);</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">CREATE INDEX sales_orders_customer</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">  ON <font color="red"><span style="color:red">sales_orders</span></font></span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt">  USING btree</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">  (customer);</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">CREATE INDEX soh_product_prodcode</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">  ON <font color="red"><span style="color:red">soh_product</span></font></span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt">  USING btree</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">  (prod_code);</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">CREATE INDEX soh_product_transno</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">  ON <font color="red"><span style="color:red">soh_product</span></font></span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt">  USING btree</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">  (soh_num);</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt">-----OriginalMessage-----<br /> From: Richard Broersma Jr [mailto:rabroersma@yahoo.com] <br />
Sent:Friday, 30 June 2006 10:51<br /> To: Phillip Smith; pgsql-sql@postgresql.org<br /> Subject: Re: [SQL] SELECT
Aggregate</span></font><pclass="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">> I've tried Aaron's suggestion of the GROUP BY and I don't know much about</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt">> it, but it ran for around 17 hours and still going (it had a dedicated Dual</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt">> Xeon 3.0GHz box under RHEL4 running it!)</span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanstyle="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">Maybe, this query that you are trying to run is a good candidate for a "Materialize View".</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt">http://archives.postgresql.org/pgsql-performance/2004-02/msg00279.php</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">Also before you run your query you might want to see the explain plan is.  Perhap it is using a</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt">sequencial scan in a place where an index can improve query preformance.</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt"> </span></font></div><br /><p><b>*******************Confidentiality and Privilege
Notice*******************</b><p>The material contained in this message is privileged and confidential to the addressee.
Ifyou are not the addressee indicated in this message or responsible for delivery of the message to such person, you
maynot copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email.
<p>Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither
givennor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for
direct,indirect or consequential loss arising from transmission of this message or any attachments <br />