Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 0A9DC642784 for ; Thu, 10 Jun 2010 17:10:18 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 58902-07 for ; Thu, 10 Jun 2010 20:10:10 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from joeconway.com (wsip-72-214-29-243.sd.sd.cox.net [72.214.29.243]) by mail.postgresql.org (Postfix) with ESMTP id A2921642778 for ; Thu, 10 Jun 2010 17:10:10 -0300 (ADT) Received: from [192.168.4.40] (account jconway HELO dev.joeconway.com) by joeconway.com (CommuniGate Pro SMTP 4.1.8) with ESMTP-TLS id 9807012; Thu, 10 Jun 2010 13:10:08 -0700 Message-ID: <4C11469A.8000706@joeconway.com> Date: Thu, 10 Jun 2010 13:10:02 -0700 From: Joe Conway User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.8.0.12) Gecko/20071019 Fedora/1.5.0.12-3.fc6 pango-text Thunderbird/1.5.0.12 Mnenhy/0.7.5.666 MIME-Version: 1.0 To: Anne Rosset CC: Craig James , pgsql-performance@postgresql.org Subject: Re: Need to increase performance of a query References: <4C1125F0.8090101@collab.net> <4C112B19.6080908@krogh.cc> <4C113098.5040105@collab.net> <98414706.20100610212211@erwied.eu> <4C113E2F.6000305@collab.net> <4C11413B.8080107@emolecules.com> <4C114381.3030900@collab.net> In-Reply-To: <4C114381.3030900@collab.net> X-Enigmail-Version: 1.0.1 OpenPGP: id=B699434A Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="------------enig096021DEA783E2DB7732C660" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.981 tagged_above=-10 required=5 tests=BAYES_40=-0.001, RDNS_DYNAMIC=0.982 X-Spam-Level: X-Archive-Number: 201006/186 X-Sequence-Number: 39042 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig096021DEA783E2DB7732C660 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable On 06/10/2010 12:56 PM, Anne Rosset wrote: > Craig James wrote: >> create index item_rank_null_idx on item_rank(pf_id) >> where item_rank.pf_id is null; >> >> Craig >> > Hi Craig, > I tried again after adding your suggested index but I didn't see any > improvements: (seems that the index is not used) > Filter: ((rank IS NOT NULL) AND (pf_id IS NULL) AND > ((project_id)::text =3D 'proj2783'::text)) > Total runtime: 11.988 ms > (6 rows) >=20 > Time: 13.654 ms try: create index item_rank_null_idx on item_rank(pf_id) where rank IS NOT NULL AND pf_id IS NULL; Joe --------------enig096021DEA783E2DB7732C660 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.14 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/ iQIcBAEBCAAGBQJMEUafAAoJEDfy90M199hlq+gP/34bf3xN9za7QOcnSyLEHIs5 vWKQHEX7pyERERxX+zNx93qCZrxak9cjLNZq1qjFvFslgI/8rddkPX8qZovfPZtr 1txpa2glPSDk+hzfClDWILcxpa6fKn88NKT/lYaS390qvwBeLEz5UQXaXVw+Zmi4 tsUZjmrDYPUwTHEcKTOvo2l+mES49iOvjpzrM1CXjJ6V0lH0sWlv7eSmCnCKtuoy tS5DShAJ4+iaVQ617/CHlSmtYMQyaXivDqp03G2duIaWFcfKSmFSh1BNZqGpoa+t F8TB2pdEDCtiHYoAdIFw2WK14RQOBFCb6yk+W2Qjvplwl1+0Sr2D8bxgEC65z1u8 EeJxVSmoAvt6JMsVXlNTBGD2RYvqKDrQgcCug2Lc0j1Vl/6fkDWwWVBxAQaEm3+b DgkToeFkFctZrFLB2sPfm3jJ5pwI8N5ILDUBwc0Af6g70T7pR9uyl8jwNNjbFzfJ L36qa+K/wz6G6/FkfivrP0xTH1q3nqVrDf4Os5/pwyf8/WNAmIFz4GmjarcqntrH d7Z63Sb39VSSFNR1LKVrTevJUPO0Z+broQLdBs4huq1QfWIxd/hVG6f3AJVhnIoW 77IXqXYnizJ6HUo4eygAoUd5fNgF3ofzWnLgS6AylssQuKl/MpXispwpb1DIMbA+ ddHH0ofSw+d8/LzkIgwp =djd5 -----END PGP SIGNATURE----- --------------enig096021DEA783E2DB7732C660--