Date: Mon, 04 Jul 2005 20:29:50 -0400
From: David Gagnon <dgagnon@siunik.com>
Subject: Re: Why the planner is not using the INDEX .
In-reply-to: <20050704132446.O14579@megazone.bigpanda.com>
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: pgsql-performance@postgresql.org
Message-id: <42C9D47E.7090103@siunik.com>
MIME-version: 1.0
Content-type: multipart/alternative;
	boundary="Boundary_(ID_/5SMBe2bXvLrkUkYZhz9qA)"
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
References: <20050630222451.GA17628@calvin.surfutopia.net>
	<42C473BA.9090305@commandprompt.com>
	<20050630235821.GA2163@calvin.surfutopia.net>
	<42C994BD.3000909@siunik.com>
	<20050704132446.O14579@megazone.bigpanda.com>

This is a multi-part message in MIME format.

--Boundary_(ID_/5SMBe2bXvLrkUkYZhz9qA)
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7BIT

Thanks .. I miss that FK don't create indexed ...  since Primary key 
implicitly does ...

I'm a bit surprised of that behavior thought, since it means that if we 
delete a row from table A all tables (B,C,D) with FK pointing to this 
table (A) must be scanned. 
If there is no index on those tables it means we gone do all Sequantial 
scans. Than can cause significant performance problem!!!.

Is there a reason why implicit index aren't created when FK are 
declared.  I looked into the documentation and I haven't found a way to 
tell postgresql to automatically create an index when creating la FK.  
Does it means I need to manage it EXPLICITLY with create index statement 
?  Is there another way ?

Thanks for you help that simple answer will solve a lot of performance 
problem I have ...

/David


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

--Boundary_(ID_/5SMBe2bXvLrkUkYZhz9qA)
Content-type: text/html; charset=ISO-8859-1
Content-transfer-encoding: 7BIT

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Thanks .. I miss that FK don't create indexed ...&nbsp; since Primary key
implicitly does ...<br>
<br>
I'm a bit surprised of that behavior thought, since it means that if we
delete a row from table A all tables (B,C,D) with FK pointing to this
table (A) must be scanned.&nbsp; <br>
If there is no index on those tables it means we gone do all Sequantial
scans. Than can cause significant performance problem!!!.<br>
<br>
Is there a reason why implicit index aren't created when FK are
declared.&nbsp; I looked into the documentation and I haven't found a way to
tell postgresql to automatically create an index when creating la FK.&nbsp;
Does it means I need to manage it EXPLICITLY with create index
statement ?&nbsp; Is there another way ?<br>
<br>
Thanks for you help that simple answer will solve a lot of performance
problem I have ...<br>
<br>
/David<br>
<br>
<br>
<blockquote cite="mid20050704132446.O14579@megazone.bigpanda.com"
 type="cite">
  <pre wrap="">On Mon, 4 Jul 2005, David Gagnon wrote:

  </pre>
  <blockquote type="cite">
    <pre wrap="">  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)
  -&gt;  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))

  -&gt;  Hash  (cost=1142.09..1142.09 rows=151 width=37) (actual
time=125.000..125.000 rows=0 loops=1)
        -&gt;  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
    </pre>
  </blockquote>
  <pre wrap=""><!---->
  </pre>
  <blockquote type="cite">
    <pre wrap="">    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;
    </pre>
  </blockquote>
  <pre wrap=""><!---->
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.

  </pre>
</blockquote>
</body>
</html>

--Boundary_(ID_/5SMBe2bXvLrkUkYZhz9qA)--