X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E068E52A6A for ; Thu, 3 Mar 2005 23:42:47 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 34197-10 for ; Thu, 3 Mar 2005 23:42:38 +0000 (GMT) Received: from fep7.cogeco.net (smtp.cogeco.net [216.221.81.25]) by svr1.postgresql.org (Postfix) with ESMTP id D474D52A63 for ; Thu, 3 Mar 2005 23:42:36 +0000 (GMT) Received: from windsortransportationclub.com (d57-59-156.home.cgocable.net [24.57.59.156]) by fep7.cogeco.net (Postfix) with SMTP id 171B4AF4 for ; Thu, 3 Mar 2005 18:42:39 -0500 (EST) Received: (qmail 25103 invoked from network); 4 Mar 2005 00:13:43 -0000 Received: from d36-80-110.home1.cgocable.net (HELO KEN) (24.36.80.110) by 0 with SMTP; 4 Mar 2005 00:13:43 -0000 Message-ID: <001d01c5204a$b440edf0$6a01a8c0@KEN> From: "Ken Egervari" To: "Josh Berkus" , References: <001001c51ef4$3849eeb0$6a01a8c0@KEN> <200503022152.13211.josh@agliodbs.com> <004601c51fbe$828bbb50$6a01a8c0@KEN> <200503030935.14537.josh@agliodbs.com> Subject: Re: Help with tuning this query (with explain analyze finally) Date: Thu, 3 Mar 2005 18:42:46 -0500 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.782 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, RCVD_IN_NJABL_DUL, RCVD_IN_SORBS_DUL X-Spam-Level: * X-Archive-Number: 200503/67 X-Sequence-Number: 10772 Josh, I did everything you said and my query does perform a bit better. I've been getting speeds from 203 to 219 to 234 milliseconds now. I tried increasing the work mem and the effective cache size from the values you provided, but I didn't see any more improvement. I've tried to looking into setting the shared buffers for Windows XP, but I'm not sure how to do it. I'm looking in the manual at: http://www.postgresql.org/docs/8.0/interactive/kernel-resources.html#SYSVIPC-PARAMETERS It doesn't mention windows at all. Does anyone have any ideas on have to fix this? Here is the new explain analyze. Sort (cost=1996.21..1996.26 rows=17 width=165) (actual time=297.000..297.000 rows=39 loops=1) Sort Key: ss.date -> Merge Right Join (cost=1951.26..1995.87 rows=17 width=165) (actual time=297.000..297.000 rows=39 loops=1) Merge Cond: ("outer".id = "inner".driver_id) -> Index Scan using driver_pkey on driver d (cost=0.00..42.16 rows=922 width=43) (actual time=0.000..0.000 rows=922 loops=1) -> Sort (cost=1951.26..1951.30 rows=17 width=122) (actual time=297.000..297.000 rows=39 loops=1) Sort Key: s.driver_id -> Hash Join (cost=586.48..1950.91 rows=17 width=122) (actual time=219.000..297.000 rows=39 loops=1) Hash Cond: ("outer".carrier_code_id = "inner".id) -> Merge Join (cost=571.97..1931.95 rows=830 width=87) (actual time=219.000..297.000 rows=310 loops=1) Merge Cond: ("outer".current_status_id = "inner".id) -> Index Scan using shipment_current_status_id_idx on shipment s (cost=0.00..2701.26 rows=60307 width=66) (actual time=0.000..62.000 rows=27711 loops=1) Filter: (is_purged = false) -> Sort (cost=571.97..576.38 rows=1766 width=21) (actual time=125.000..156.000 rows=6902 loops=1) Sort Key: ss.id -> Hash Join (cost=1.11..476.72 rows=1766 width=21) (actual time=0.000..93.000 rows=6902 loops=1) Hash Cond: ("outer".release_code_id = "inner".id) -> Index Scan Backward using current_status_date_idx on shipment_status ss (cost=0.00..387.35 rows=14122 width=21) (actual time=0.000..16.000 rows=14925 loops=1) Index Cond: (date >= (('now'::text)::date - 31)) -> Hash (cost=1.10..1.10 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=1) -> Seq Scan on release_code rc (cost=0.00..1.10 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1) Filter: ((number)::text = '9'::text) -> Hash (cost=14.51..14.51 rows=2 width=35) (actual time=0.000..0.000 rows=0 loops=1) -> Nested Loop (cost=4.92..14.51 rows=2 width=35) (actual time=0.000..0.000 rows=2 loops=1) -> Index Scan using person_pkey on person p (cost=0.00..5.73 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1) Index Cond: (id = 355) -> Hash Join (cost=4.92..8.75 rows=2 width=39) (actual time=0.000..0.000 rows=2 loops=1) Hash Cond: ("outer".id = "inner".carrier_id) -> Seq Scan on carrier c (cost=0.00..3.54 rows=54 width=4) (actual time=0.000..0.000 rows=54 loops=1) -> Hash (cost=4.92..4.92 rows=2 width=43) (actual time=0.000..0.000 rows=0 loops=1) -> Hash Join (cost=3.04..4.92 rows=2 width=43) (actual time=0.000..0.000 rows=2 loops=1) Hash Cond: ("outer".carrier_id = "inner".carrier_id) -> Seq Scan on carrier_code cc (cost=0.00..1.57 rows=57 width=35) (actual time=0.000..0.000 rows=57 loops=1) -> Hash (cost=3.04..3.04 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1) -> Index Scan using carrier_to_person_person_id_idx on carrier_to_person ctp (cost=0.00..3.04 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1) Index Cond: (355 = person_id) Total runtime: 297.000 ms