Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id EB0191336971 for ; Tue, 8 Feb 2011 13:30:40 -0400 (AST) 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 19219-07 for ; Tue, 8 Feb 2011 17:30:33 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-wy0-f174.google.com (mail-wy0-f174.google.com [74.125.82.174]) by mail.postgresql.org (Postfix) with ESMTP id 3ED7E1337999 for ; Tue, 8 Feb 2011 13:30:32 -0400 (AST) Received: by wyb28 with SMTP id 28so5947888wyb.19 for ; Tue, 08 Feb 2011 09:30:31 -0800 (PST) Received: by 10.227.155.11 with SMTP id q11mr17918001wbw.130.1297186231573; Tue, 08 Feb 2011 09:30:31 -0800 (PST) Received: from [192.168.0.7] (85-168-147-132.rev.numericable.fr [85.168.147.132]) by mx.google.com with ESMTPS id j49sm2968084wer.38.2011.02.08.09.30.26 (version=SSLv3 cipher=RC4-MD5); Tue, 08 Feb 2011 09:30:29 -0800 (PST) Subject: Re: Indexes with condition using immutable functions applied to column not used From: Sylvain Rabot Reply-To: sylvain@abstraction.fr To: Jesper Krogh Cc: pgsql-performance@postgresql.org In-Reply-To: <4D50D16A.1060802@krogh.cc> References: <1297124098.2549.65.camel@kheops> <4D50D16A.1060802@krogh.cc> Content-Type: multipart/signed; micalg="pgp-sha1"; protocol="application/pgp-signature"; boundary="=-PQSDrBU3FRXbsPNyH9pD" Date: Tue, 08 Feb 2011 18:30:25 +0100 Message-ID: <1297186225.2497.4.camel@kheops> Mime-Version: 1.0 X-Mailer: Evolution 2.30.3 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.6 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_LOW=-0.7 X-Spam-Level: X-Archive-Number: 201102/308 X-Sequence-Number: 42446 --=-PQSDrBU3FRXbsPNyH9pD Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, 2011-02-08 at 06:15 +0100, Jesper Krogh wrote: > On 2011-02-08 01:14, Sylvain Rabot wrote: > > CREATE INDEX directory_id_user_mod_cons_hash_0_btree_idx ON mike.direct= ory USING btree (id_user) WHERE __mod_cons_hash(id_user, 4) =3D 0; > > CREATE INDEX directory_id_user_mod_cons_hash_1_btree_idx ON mike.direct= ory USING btree (id_user) WHERE __mod_cons_hash(id_user, 4) =3D 1; > > CREATE INDEX directory_id_user_mod_cons_hash_2_btree_idx ON mike.direct= ory USING btree (id_user) WHERE __mod_cons_hash(id_user, 4) =3D 2; > > CREATE INDEX directory_id_user_mod_cons_hash_3_btree_idx ON mike.direct= ory USING btree (id_user) WHERE __mod_cons_hash(id_user, 4) =3D 3; > > >=20 > > mike=3D# EXPLAIN ANALYZE SELECT * FROM directory WHERE id_user =3D 4; >=20 > Should be written as: > select * from directory where __mod_cons_hash(id_user,4) =3D 4%4; >=20 > Then it should just work. >=20 > --=20 > Jesper >=20 The where clause you wrote selects all the directory records that have a id_user % 4 equivalent to 0 like 0, 4, 8, 16 ... etc. It does use the indexes but it is not was I want to select. --=20 Sylvain Rabot --=-PQSDrBU3FRXbsPNyH9pD Content-Type: application/pgp-signature; name="signature.asc" Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.10 (GNU/Linux) iQEcBAABAgAGBQJNUX2sAAoJECLlHVUnhaoWyAEIAMqZlmOOXAejKyN9K1ArzmPR x0ge8FrIBhFFh5qM+32dr7WXFXul2d4VwtlbkOjSUQTTs/h/9qsCI6gFk7Q1KgbJ J+E512UhhOTfdu/zEWww5oXONUqY9xhizG+sZRShv/Oow7AmoM0wRp2E4OzJIAdz sKUDga3Zl4XyYR/4eLHeHR7JM+Y9MNMSSZ79+mOdPqpEjOoKwHerkwUrB38vBa9V afJFh6Ynsg4jJAJ28ZKPxmejG0pfZZ5KsUW9Y20azoPqw3uQTIfBPaEG3D5oXPZK rTL5IzELwlUoPpv5jnQzWGA0+Y71bHHM7d4ljF5Ot7JjgVTyGcPlqaxPgwuYVzk= =ftWX -----END PGP SIGNATURE----- --=-PQSDrBU3FRXbsPNyH9pD--