Skip site navigation (1) Skip section navigation (2)

Re: Indexes with condition using immutable functions applied to column not used

From: Sylvain Rabot <sylvain(at)abstraction(dot)fr>
To: Jesper Krogh <jesper(at)krogh(dot)cc>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Indexes with condition using immutable functions applied to column not used
Date: 2011-02-08 17:30:25
Message-ID: 1297186225.2497.4.camel@kheops (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)abstraction(dot)fr>

In response to

pgsql-performance by date

Next:From: Scott MarloweDate: 2011-02-08 17:31:25
Subject: Re: Really really slow select count(*)
Previous:From: Marti RaudseppDate: 2011-02-08 16:50:23
Subject: Re: Really really slow select count(*)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group