X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id ED502528BB for ; Mon, 4 Jul 2005 16:55:40 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 92239-01 for ; Mon, 4 Jul 2005 19:55:38 +0000 (GMT) Received: from relais.videotron.ca (relais.videotron.ca [24.201.245.36]) by svr1.postgresql.org (Postfix) with ESMTP id 071FF5289B for ; Mon, 4 Jul 2005 16:55:37 -0300 (ADT) Received: from [192.168.1.101] ([24.202.23.128]) by VL-MO-MR011.ip.videotron.ca (iPlanet Messaging Server 5.2 HotFix 1.21 (built Sep 8 2003)) with ESMTP id <0IJ4008UCCIHLF@VL-MO-MR011.ip.videotron.ca> for pgsql-performance@postgresql.org; Mon, 04 Jul 2005 15:51:53 -0400 (EDT) Date: Mon, 04 Jul 2005 15:57:49 -0400 From: David Gagnon Subject: Why the planner is not using the INDEX . In-reply-to: <20050630235821.GA2163@calvin.surfutopia.net> Cc: pgsql-performance@postgresql.org Message-id: <42C994BD.3000909@siunik.com> MIME-version: 1.0 Content-type: multipart/alternative; boundary="Boundary_(ID_UtyElAxQHbaJrkD3SgFi5w)" X-Accept-Language: en-us, en User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) References: <20050630222451.GA17628@calvin.surfutopia.net> <42C473BA.9090305@commandprompt.com> <20050630235821.GA2163@calvin.surfutopia.net> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.169 tagged_above=0 required=5 tests=AWL, HTML_60_70, HTML_MESSAGE, HTML_TAG_EXIST_TBODY, HTML_TITLE_EMPTY X-Spam-Level: X-Archive-Number: 200507/31 X-Sequence-Number: 13272 This is a multi-part message in MIME format. --Boundary_(ID_UtyElAxQHbaJrkD3SgFi5w) Content-type: text/plain; charset=ISO-8859-1; format=flowed Content-transfer-encoding: 7BIT 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 --Boundary_(ID_UtyElAxQHbaJrkD3SgFi5w) Content-type: text/html; charset=ISO-8859-1 Content-transfer-encoding: 7BIT 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

--Boundary_(ID_UtyElAxQHbaJrkD3SgFi5w)--