Re: [SQL] SELECT Aggregate

[Phillip Smith]

Well whatdyaknow?? Being a Postgres newbie I hadn't even played with indexes yet. They're awesome!!   Using Richard's suggestion of the Sub-Select in the COLUMN list, combined with adding some indexes, I can now return this in under 5 seconds!   I’ve included the new SELECT query, as well as the definitions of the indexes below for anyone who’s interested.   Thanks guys!   QUERY: SELECT      trans_no,             customer,             date_placed,             date_complete,             date_printed,             (SELECT  SUM(sell_price)                  FROM   soh_product                  WHERE  sales_orders.trans_no = soh_product.soh_num                  ) AS wholesale,             ord_type,             ord_status,             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   INDEXES: CREATE INDEX sales_orders_customer   ON sales_orders   USING btree   (customer);   CREATE INDEX sales_orders_orderno   ON sales_orders   USING btree   (order_number);   CREATE INDEX sales_orders_customer   ON sales_orders   USING btree   (customer);   CREATE INDEX soh_product_prodcode   ON soh_product   USING btree   (prod_code);   CREATE INDEX soh_product_transno   ON soh_product   USING btree   (soh_num);     -----Original Message----- From: Richard Broersma Jr [mailto:[EMAIL PROTECTED] Sent: Friday, 30 June 2006 10:51 To: Phillip Smith; pgsql-sql@postgresql.org Subject: Re: [SQL] SELECT Aggregate   > I've tried Aaron's suggestion of the GROUP BY and I don't know much about > it, but it ran for around 17 hours and still going (it had a dedicated Dual > Xeon 3.0GHz box under RHEL4 running it!)   Maybe, this query that you are trying to run is a good candidate for a "Materialize View". http://archives.postgresql.org/pgsql-performance/2004-02/msg00279.php   Also before you run your query you might want to see the explain plan is.  Perhap it is using a sequencial scan in a place where an index can improve query preformance.     *******************Confidentiality and Privilege Notice******************* The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor 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