From: Sylvain Rabot <sylvain@abstraction.fr>
To: Jesper Krogh <jesper@krogh.cc>
Cc: pgsql-performance@postgresql.org
Subject: Re: Indexes with condition using immutable functions applied to column not used
Date: Tue, 08 Feb 2011 18:30:25 +0100
Message-ID: <1297186225.2497.4.camel@kheops> (raw)
In-Reply-To: <4D50D16A.1060802@krogh.cc>
References: <1297124098.2549.65.camel@kheops>
<4D50D16A.1060802@krogh.cc>
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.directory USING btree (id_user) WHERE __mod_cons_hash(id_user, 4) = 0;
> > CREATE INDEX directory_id_user_mod_cons_hash_1_btree_idx ON mike.directory USING btree (id_user) WHERE __mod_cons_hash(id_user, 4) = 1;
> > CREATE INDEX directory_id_user_mod_cons_hash_2_btree_idx ON mike.directory USING btree (id_user) WHERE __mod_cons_hash(id_user, 4) = 2;
> > CREATE INDEX directory_id_user_mod_cons_hash_3_btree_idx ON mike.directory USING btree (id_user) WHERE __mod_cons_hash(id_user, 4) = 3;
> >
>
> > mike=# EXPLAIN ANALYZE SELECT * FROM directory WHERE id_user = 4;
>
> Should be written as:
> select * from directory where __mod_cons_hash(id_user,4) = 4%4;
>
> Then it should just work.
>
> --
> Jesper
>
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.
--
Sylvain Rabot <sylvain@abstraction.fr>
Attachments:
signature.asc (application/pgp-signature, 489B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sylvain@abstraction.fr, jesper@krogh.cc
Subject: Re: Indexes with condition using immutable functions applied to column not used
In-Reply-To: <1297186225.2497.4.camel@kheops>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox