BUG #1182: Index choice - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      1182
Logged by:          Damien Guitard

Email address:      damien.guitard@regis-dgac.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)
>
>