X-Original-To: pgsql-performance@postgresql.org Received: from ms-smtp-02.texas.rr.com (ms-smtp-02.texas.rr.com [24.93.36.230]) by postgresql.org (Postfix) with ESMTP id A6D5C476321 for ; Thu, 6 Mar 2003 13:51:11 -0500 (EST) Received: from spaceship.com (cs24243214-140.austin.rr.com [24.243.214.140]) by ms-smtp-02.texas.rr.com (8.12.5/8.12.2) with ESMTP id h26ImKn1029954 for ; Thu, 6 Mar 2003 13:48:21 -0500 (EST) Message-ID: <3E67989F.7030001@spaceship.com> Date: Thu, 06 Mar 2003 12:51:11 -0600 From: Matt Mello User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.3b) Gecko/20030210 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance Subject: ISNULL performance tweaks Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Archive-Number: 200303/42 X-Sequence-Number: 1340 I have some existing code running in a production environment with embedded SELECTs whose WHERE's use ISNULL tests on indexed foreign key fields. This is obviously very slow. My ISNULL *queries *take anywhere from 6 to 40 seconds. These queries are used to generate reports to a device which times out at 20 seconds, so half the time these devices don't get their reports, which makes my customers VERY angry. I recall seeing an email (I believe on this list) about how to improve performance of ISNULL's with some sort of tweak or trick. However, I can't find that email anywhere, and couldn't find it searching the maillist archives. So, until I have the time to code the fixes I need to prevent the use of ISNULL, does anybody know how I can speed up this existing system? Man, I wish PG indexed nulls! Is there any plan on adding these in the future? Thanks for any help you can give! -- Matt Mello