From: David Gagnon <dgagnon@siunik.com>
Cc: pgsql-performance@postgresql.org
Subject: Why the planner is not using the INDEX .
Date: Mon, 04 Jul 2005 15:57:49 -0400
Message-ID: <42C994BD.3000909@siunik.com> (raw)
In-Reply-To: <20050630235821.GA2163@calvin.surfutopia.net>
References: <20050630222451.GA17628@calvin.surfutopia.net>
<42C473BA.9090305@commandprompt.com>
<20050630235821.GA2163@calvin.surfutopia.net>
Hi all,
If you can just help my understanding the choice of the planner.
Here is the Query:
explain analyse SELECT IRNUM FROM IR
INNER JOIN IT ON IT.ITIRNUM = ANY ('{1000, 2000}') AND
IT.ITYPNUM = 'M' AND IR.IRYPNUM = IT.ITYPNUM AND IR.IRNUM = IT.ITIRNUM
WHERE IRNUM = ANY ('{1000, 2000}') and IRYPNUM = 'M'
Here is the Query plan:
QUERY PLAN
Hash Join (cost=1142.47..5581.75 rows=87 width=4) (actual
time=125.000..203.000 rows=2 loops=1)
Hash Cond: ("outer".itirnum = "inner".irnum)
-> Seq Scan on it (cost=0.00..3093.45 rows=31646 width=9) (actual
time=0.000..78.000 rows=2 loops=1)
Filter: ((itirnum = ANY ('{1000,2000}'::integer[])) AND
((itypnum)::text = 'M'::text))
-> Hash (cost=1142.09..1142.09 rows=151 width=37) (actual
time=125.000..125.000 rows=0 loops=1)
-> Index Scan using ir_pk on ir (cost=0.00..1142.09 rows=151
width=37) (actual time=0.000..125.000 rows=2 loops=1)
Index Cond: ((irypnum)::text = 'M'::text)
Filter: (irnum = ANY ('{1000,2000}'::integer[]))
Total runtime: 203.000 ms
I don't understand why the planner do a Seq Scan (Seq Scan on table
IT ..) instead of passing by the followin index:
ALTER TABLE IT ADD CONSTRAINT IT_IR_FK foreign key (ITYPNUM,ITIRNUM)
references IR (IRYPNUM, IRNUM) ON UPDATE CASCADE;
I tried some stuff but I'm not able to change this behavior. The IT and
IR table may be quite huge (from 20k to 1600k rows) so I think doing a
SEQ SCAN is not a good idea.. am I wrong? Is this query plan is oki for
you ?
Thanks for your help.
/David
P.S.: I'm using postgresql 8.0.3 on windows and I change those setting
in my postgresql.conf :
shared_buffers = 12000 # min 16, at least max_connections*2, 8KB each
work_mem = 15000 # min 64, size in KB
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dgagnon@siunik.com
Subject: Re: Why the planner is not using the INDEX .
In-Reply-To: <42C994BD.3000909@siunik.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox