Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 475CA643DEA for ; Thu, 10 Jun 2010 18:06:43 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 55980-09 for ; Thu, 10 Jun 2010 21:06:25 +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 58F64643DAB for ; Thu, 10 Jun 2010 18:06:26 -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 9807122; Thu, 10 Jun 2010 14:06:24 -0700 Message-ID: <4C1153D0.5060806@joeconway.com> Date: Thu, 10 Jun 2010 14:06:24 -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> <4C11469A.8000706@joeconway.com> <4C11477A.5020909@joeconway.com> <4C114958.4080308@collab.net> In-Reply-To: <4C114958.4080308@collab.net> X-Enigmail-Version: 1.0.1 OpenPGP: id=B699434A Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="------------enigCF07F75C357679E3D95AE9CA" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.782 tagged_above=-10 required=5 tests=BAYES_50=0.8, RDNS_DYNAMIC=0.982 X-Spam-Level: * X-Archive-Number: 201006/191 X-Sequence-Number: 39047 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enigCF07F75C357679E3D95AE9CA Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable On 06/10/2010 01:21 PM, Anne Rosset wrote: >> =20 > I tried that and it didn't make any difference. Same query plan. A little experimentation suggests this might work: create index item_rank_project on item_rank(project_id, rank) where pf_id IS NULL; Joe --------------enigCF07F75C357679E3D95AE9CA 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/ iQIcBAEBCAAGBQJMEVPQAAoJEDfy90M199hl6XEP/387Nt7LdTmFRahGZ3iedg28 2BfJNKtdg+TV3a8oE+XhWFZ7S/6wpVLccayRfGGl6053ajcKzGodm/0zfvvDm/k9 Mb3PUOT8hxGo7aWrNWppDzzljiCy64YYbh9+DVbMblXzaYVhKGDG6oElBV9XXKDN 88s0bKbUms0UF4pq2Tab4YJwH3g2mOt02Yt+oU9ys7u943Mv4XcRY1QJJjHYuNBH ee2fm3PaJJeA/PDTYz9GAqil5QLwiJtn7glGWhZZYriCHvWtU6cOtf/2o0wedMw9 QL8ObetLx0EVpLO2FGjZTfpyIWzetOCoPi3Jnrza0WbpFp6HgoBvaWKsFJxCXgM/ so7saU3JZ9aLl6LiJB5v9WjE3DIN1/JlBo6i4Ms8e5MlYNLJcdvkmNQHoLwts7/Y wf+S3YE+6ZyAxUAWzyD7YbGrz1oluysx3wTsJJ4pxXO04VUH6O+n5i5MZKiKE5vL lxFjXDVN3u1rrPajn0YU7sGEkPZwJN14JwuXUOg+9qH8tzZizhvqGFxs02ZWyeop CMdUJ9RYxmQdiOHSMDd5z5fsfsi9rV/CjChFG5huX6fMuRYH0uLkb4Tl6NI043Hd IsgRVaBDFk8ZiRjkauBg7kn0khrMSKj4QQ+cjUv1IH2z5oIHK39CPfIXUj7hsW6O rGGXILTFmIOsA8T7YsDQ =R/pu -----END PGP SIGNATURE----- --------------enigCF07F75C357679E3D95AE9CA--