Table definition and problem query is below. I'm surprised...
caillaudangers=> \d relations
Table « public.relations »
Colonne | Type | Modificateurs
------------+---------+--------------------------------------------------------
parent_id | integer | not null
child_id | integer | not null
klass | integer | not null
id | integer | not null default
nextval('relations_id_seq'::regclass)
sort_order | integer |
data | bytea |
tree_vis | boolean |
main_path | boolean |
index_id | integer |
Index :
« relations_pkey » PRIMARY KEY, btree (id)
« relations_unique » UNIQUE, btree (parent_id, child_id)
« relations_child » btree (child_id)
« relations_sort » btree (parent_id, klass, sort_order) CLUSTER
« relations_tree » btree (parent_id, klass, sort_order) WHERE tree_vis
= true
Contraintes de clés étrangères :
« klass_fk » FOREIGN KEY (klass) REFERENCES relation_klasses(id)
« relations_child_id_fkey » FOREIGN KEY (child_id) REFERENCES
nodes(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
« relations_node_id_fkey » FOREIGN KEY (parent_id) REFERENCES
nodes(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
caillaudangers=> CLUSTER relations; ANALYZE relations;
CLUSTER
ANALYZE
caillaudangers=> EXPLAIN ANALYZE SELECT * FROM relations WHERE child_id IN
( [60 integers] );
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on relations (cost=176.93..383.43 rows=236 width=58)
(actual time=0.298..0.482 rows=350 loops=1)
Recheck Cond: (child_id = ANY (' [60 integers]
-> Bitmap Index Scan on relations_child (cost=0.00..176.87 rows=236
width=0) (actual time=0.281..0.281 rows=350 loops=1)
Index Cond: (child_id = ANY [60 integers]
Total runtime: 0.582 ms
(5 lignes)
OK, Screaming fast ! (and it doesn't use the CLUSTER)
caillaudangers=> explain analyze SELECT target.* FROM relations AS target,
(VALUES (8695), (8743), (10309), (22000), (22980), (23016), (8683),
(25092), (13369), (13377), (13375), (13371), (13373), (25126), (10629),
(13363), (13365), (22820), (15705), (13367), (8759), (8783), (8815),
(23018), (8781), (8765), (23012), (23036), (23046), (8803), (25226),
(22940), (8771), (8769), (13335), (23004), (22816), (23062), (8805),
(8727), (13379), (23064), (23032), (24976), (8807), (23024), (8787),
(23028), (8809), (8735), (23042), (8813), (8731), (22964), (8755), (8697),
(8749), (22974), (8733), (8715)) AS source WHERE target.child_id =
source.column1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=1.50..542.68 rows=216 width=58) (actual
time=0.395..45.402 rows=350 loops=1)
Hash Cond: (target.child_id = "*VALUES*".column1)
-> Seq Scan on relations target (cost=0.00..440.29 rows=26329
width=58) (actual time=0.011..8.213 rows=26329 loops=1)
-> Hash (cost=0.75..0.75 rows=60 width=4) (actual time=0.096..0.096
rows=60 loops=1)
-> Values Scan on "*VALUES*" (cost=0.00..0.75 rows=60 width=4)
(actual time=0.001..0.049 rows=60 loops=1)
Total runtime: 45.594 ms
Argh. Where did my index go ? [shoots self in foot]
