Bad plan using join on VALUES

From: Listmail <lists(at)peufeu(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Bad plan using join on VALUES
Date: 2007-04-10 08:57:43
Message-ID: op.tqj8ihl0zcizji@apollo13
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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]


Responses

Browse pgsql-general by date

  From Date Subject
Next Message Listmail 2007-04-10 09:09:50 Re: [GENERAL] programmatic way to fetch latest release for a given major.minor version
Previous Message Dave Page 2007-04-10 08:52:52 Re: [GENERAL] programmatic way to fetch latest release for a given major.minor version