[BUGS] BUG #1182: Index choice

#1

The following bug has been logged online:

Bug reference: 1182
Logged by: Damien Guitard

Email address: damien.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

Quote:

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):

Quote:

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):

Quote:

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)

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

[BUGS] BUG #1182: Index choice

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

[BUGS] BUG #1182: Index choice - 06-24-2004 , 03:44 AM