Bad plan using join on VALUES - Mailing list pgsql-general

    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]