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 1B53A52846
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon,  4 Jul 2005 17:27:51 -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 94578-06
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon,  4 Jul 2005 20:27:42 +0000 (GMT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP id 3E24052808
	for <pgsql-performance@postgresql.org>;
	Mon,  4 Jul 2005 17:27:41 -0300 (ADT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 12CB535578; Mon,  4 Jul 2005 13:27:41 -0700 (PDT)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 10DAF354C0; Mon,  4 Jul 2005 13:27:41 -0700 (PDT)
Date: Mon, 4 Jul 2005 13:27:41 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: David Gagnon <dgagnon@siunik.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Why the planner is not using the INDEX .
In-Reply-To: <42C994BD.3000909@siunik.com>
Message-ID: <20050704132446.O14579@megazone.bigpanda.com>
References: <20050630222451.GA17628@calvin.surfutopia.net>
	<42C473BA.9090305@commandprompt.com>
	<20050630235821.GA2163@calvin.surfutopia.net>
	<42C994BD.3000909@siunik.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200507/32
X-Sequence-Number: 13273

On Mon, 4 Jul 2005, David Gagnon wrote:

>   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;

That doesn't create an index on IT.  Primary keys (and unique constraints)
create indexes, but not foreign keys.  Did you also create an index on
those fields?

Also it looks like it's way overestimating the number of rows that
condition would succeed for.  You might consider raising the statistics
targets on those columns and reanalyzing.