BUG #1182: Index choice

The following bug has been logged online:

Bug reference: 1182
Logged by: Damien Guitard

Email address: damien(dot)guitard(at)regis-dgac(dot)net

PostgreSQL version: 7.4

Operating system: Linux Redhat 7.2

Description: Index choice

Details:

Hi,
(sorry for my english)
I've seen a difference between the index choice when the where contains 'IS
TRUE' or '= TRUE':
(7.4.2)
Is that normal ?

ged4=# \d domaine_users
> Table "params.domaine_users"
> Column | Type | Modifiers
--------------+---------+--------------------------------------------------
> --------
> id | integer | not null default
> nextval('"domaine_users_id_seq"'::text)
> id_domaine | integer | not null
> id_user | integer | not null
> is_dom_admin | boolean | not null default false
> actif | boolean |
> date_sys | date | not null default now()
> Indexes:
> "domaine_users_pkey" primary key, btree (id)
> "domaine_users_idx01" unique, btree (id_domaine, id_user)
> "domaine_users_idx02" btree (id_user, id_domaine)
> "domaine_users_idx03" btree (actif, id_domaine)
> Foreign-key constraints:
> "domaine_users__id_domaine" FOREIGN KEY (id_domaine) REFERENCES
> domaines(id) ON UPDATE CASCADE ON DELETE CASCADE
> "domaine_users__id_user" FOREIGN KEY (id_user) REFERENCES
> users(id) ON UPDATE CASCADE ON DELETE CASCADE

First query (with = TRUE):

> ged4=# EXPLAIN SELECT id_user FROM domaine_users INNER JOIN users ON
> (domaine_users.id_user=users.id) WHERE actif = TRUE AND id_domaine=3
> AND
> actuel IS TRUE ORDER BY 1;
> QUERY PLAN
---------------------------------------------------------------------------
> --------------------------
> Sort (cost=27.68..27.68 rows=2 width=4)
> Sort Key: domaine_users.id_user
> -> Nested Loop (cost=0.00..27.67 rows=2 width=4)
> -> Index Scan using domaine_users_idx03 on domaine_users
> (cost=0.00..9.64 rows=3 width=4)
> Index Cond: ((actif = true) AND (id_domaine = 3))
> -> Index Scan using users_pkey on users (cost=0.00..6.00
> rows=1 width=4)
> Index Cond: ("outer".id_user = users.id)
> Filter: (actuel IS TRUE)
>

Second query (with IS TRUE):

> ged4=# EXPLAIN SELECT id_user FROM domaine_users INNER JOIN users ON
> (domaine_users.id_user=users.id) WHERE actif IS TRUE AND id_domaine=3
> AND actuel IS TRUE ORDER BY 1;
> QUERY PLAN
---------------------------------------------------------------------------
> ---------------------------
> Sort (cost=33.23..33.23 rows=2 width=4)
> Sort Key: domaine_users.id_user
> -> Nested Loop (cost=0.00..33.22 rows=2 width=4)
> -> Index Scan using domaine_users_idx01 on domaine_users
> (cost=0.00..15.18 rows=3 width=4)
> Index Cond: (id_domaine = 3)
> Filter: (actif IS TRUE)
> -> Index Scan using users_pkey on users (cost=0.00..6.00
> rows=1 width=4)
> Index Cond: ("outer".id_user = users.id)
> Filter: (actuel IS TRUE)
>
>