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 0CF9455E98 for ; Thu, 3 Mar 2005 07:06:56 +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 57360-06 for ; Thu, 3 Mar 2005 07:06:51 +0000 (GMT) Received: from anchor-post-35.mail.demon.net (anchor-post-35.mail.demon.net [194.217.242.85]) by svr1.postgresql.org (Postfix) with ESMTP id 083F855CC5 for ; Thu, 3 Mar 2005 07:06:50 +0000 (GMT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-35.mail.demon.net with esmtp (Exim 4.42) id 1D6kO5-000J8B-JG; Thu, 03 Mar 2005 07:04:46 +0000 Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 7753216540; Thu, 3 Mar 2005 07:06:47 +0000 (GMT) Message-ID: <4226B787.3090605@archonet.com> Date: Thu, 03 Mar 2005 07:06:47 +0000 From: Richard Huxton User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Ken Egervari Cc: John A Meinel , pgsql-performance@postgresql.org Subject: Re: Help with tuning this query (more musings) References: <001001c51ef4$3849eeb0$6a01a8c0@KEN> <1109787227.31084.12.camel@localhost.localdomain> <000901c51f55$aa4b7400$6a01a8c0@KEN> <1109789394.31084.20.camel@localhost.localdomain> <000601c51f63$63c8bb10$6a01a8c0@KEN> <13490.1109795370@sss.pgh.pa.us> <000801c51f67$c3abdcc0$6a01a8c0@KEN> <14444.1109802588@sss.pgh.pa.us> <001a01c51f8f$32bba5b0$6a01a8c0@KEN> <000401c51f9b$219ebbd0$6a01a8c0@KEN> <001001c51f9b$f606cd40$6a01a8c0@KEN> <42269AFB.9010700@arbash-meinel.com> <001b01c51fbb$39377b90$6a01a8c0@KEN> In-Reply-To: <001b01c51fbb$39377b90$6a01a8c0@KEN> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.061 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/52 X-Sequence-Number: 10757 Ken Egervari wrote: > > Hash IN Join (cost=676.15..1943.11 rows=14 width=91) (actual > time=250.000..328.000 rows=39 loops=1) > Hash Cond: ("outer".carrier_code_id = "inner".id) > -> Merge Join (cost=661.65..1926.51 rows=392 width=91) (actual > time=250.000..328.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..2702.56 rows=27257 width=66) (actual time=0.000..110.000 > rows=27711 loops=1) > Filter: ((current_status_id IS NOT NULL) AND (is_purged = > false)) There's a feature in PG called partial indexes - see CREATE INDEX reference for details. Basically you can do something like: CREATE INDEX foo_idx ON shipment (carrier_code_id) WHERE current_status_id IS NOT NULL AND is_purged = FALSE; Something similar may be a win here, although the above index might not be quite right - sorry, bit tired at moment. -- Richard Huxton Archonet Ltd