Sequence vs. Index Scan - Mailing list pgsql-sql

I have two schemas, both with the exact same DDL.  One performs great and the other has problems with a specific
query:<br/><br />SELECT *<br />FROM<br />    branch_active_vw<br />WHERE branch_id = get_branch_for_zip ( '22151' ) <br
/>ORDERBY branch_name<br />;<br /><br />I am not defining the view here because the explain plans show the real
problem. I can post the view as well if it will help.<br /><br />The explain plans are as follows:<br /><br />Fast
Schema:<br />1. Sort  (cost=17.50..17.51 rows=1 width=680) (actual time=2838.583..2838.586 rows=1 loops=1)<br />2.  
SortKey: branch.branch_name<br />3.   ->  Nested Loop Left Join  (cost=0.00..17.49 rows=1 width=680) (actual time=
2838.060..2838.093rows=1 loops=1)<br />4.         Join Filter: ("inner".branch_group_id = "outer".branch_group_id)<br
/>5.        ->  Nested Loop  (cost=0.00..11.45 rows=1 width=647) (actual time=2837.776..2837.804 rows=1 loops=1)<br
/>6.              ->  Nested Loop  (cost=0.00..7.88 rows=1 width=618) (actual time=2837.697..2837.716 rows=1
loops=1)<br/>7.                     Join Filter: ("inner".locale_id = "outer".locale_id) <br />8.                    
-> Nested Loop  (cost=0.00..6.86 rows=1 width=598) (actual time=2837.666..2837.676 rows=1 loops=1)<br />9. 
                        Join Filter: ("inner".corp_id = "outer".corp_id) <br />10.                          -> 
IndexScan using branch_pkey on branch  (cost=0.00..5.84 rows=1 width=560) (actual time=2837.621..2837.624 rows=1
loops=1)<br/>11.                                Index Cond: (branch_id = get_branch_for_zip('22151'::character
varying))<br />12.                                Filter: ((start_day <= now()) AND ((end_day IS NULL) OR (end_day
>=now())))<br />13.                          ->  Seq Scan on corp  (cost=0.00..1.01 rows=1 width=46) (actual
time=0.015..0.017 rows=1 loops=1)<br />14.                    ->  Seq Scan on locale  (cost=0.00..1.01 rows=1
width=28)(actual time=0.014..0.016 rows=1 loops=1)<br />15.              ->  Index Scan using zip_cd_pkey on zip_cd
branch_address_zip_cd (cost= 0.00..3.55 rows=1 width=37) (actual time=0.066..0.069 rows=1 loops=1)<br />16.
                  Index Cond: (branch_address_zip_cd.zip_cd_id = "outer".branch_address_zip_id)<br />17.        -> 
SeqScan on branch_group  (cost= 0.00..1.07 rows=7 width=41) (actual time=0.013..0.029 rows=7 loops=1)<br />18.       
SubPlan<br/>19.          ->  Seq Scan on branch_area  (cost=0.00..4.89 rows=1 width=6) (actual time=0.132..0.137
rows=2loops=1)<br />20.                Filter: (branch_id = $0) <br />21. Total runtime: 2839.044 ms<br clear="all"
/><br/>Slow Schema:<br />Sort  (cost=12.77..12.78 rows=1 width=1380) (actual time=157492.513..157492.515 rows=1
loops=1)<br/>1.   Sort Key: branch.branch_name<br />2.   ->  Nested Loop Left Join  (cost= 0.00..12.76 rows=1
width=1380)(actual time=130130.384..157492.484 rows=1 loops=1)<br />3.         Join Filter: ("inner".branch_group_id =
"outer".branch_group_id)<br/>4.         ->  Nested Loop  (cost= 0.00..10.34 rows=1 width=1360) (actual
time=130130.157..157492.253rows=1 loops=1)<br />5.               Join Filter: ("inner".locale_id =
"outer".locale_id)<br/>6.               ->  Nested Loop  (cost= 0.00..9.31 rows=1 width=1340) (actual
time=130130.127..157492.213rows=1 loops=1)<br />7.                     ->  Nested Loop  (cost=0.00..5.75 rows=1
width=1311)(actual time=130130.042..157492.119 rows=1 loops=1)<br />8.                           Join Filter:
("inner".corp_id= "outer".corp_id) <br />9.                           ->  Seq Scan on branch  (cost=0.00..4.72
rows=1width=1281) (actual time=130129.988..157492.057 rows=1 loops=1)<br />10.                                Filter:
((start_day<= now()) AND ((end_day IS NULL) OR (end_day >= now())) AND (branch_id =
get_branch_for_zip('22151'::charactervarying))) <br />11.                          ->  Seq Scan on corp 
(cost=0.00..1.01rows=1 width=38) (actual time=0.022..0.024 rows=1 loops=1)<br />12.                    ->  Index
Scanusing zip_cd_pkey on zip_cd branch_address_zip_cd  (cost= 0.00..3.55 rows=1 width=37) (actual time=0.070..0.073
rows=1loops=1)<br />13.                          Index Cond: (branch_address_zip_cd.zip_cd_id =
"outer".branch_address_zip_id)<br/>14.              ->  Seq Scan on locale  (cost= 0.00..1.01 rows=1 width=28)
(actualtime=0.013..0.015 rows=1 loops=1)<br />15.        ->  Seq Scan on branch_group  (cost=0.00..1.01 rows=1
width=28)(actual time=0.013..0.015 rows=1 loops=1)<br />16.        SubPlan<br />17.          ->  Seq Scan on
branch_area (cost= 0.00..1.40 rows=1 width=5) (actual time=0.077..0.084 rows=1 loops=1)<br />18.                Filter:
(branch_id= $0)<br />19. Total runtime: 157492.890 ms<br /><br />The problem is that lines 10-12 on the fast schema
showan index scan while lines 9-10  of the slow schema show a sequence scan.  The sequence scan of the branch_id,
combinedwith the rest of the filter takes forever.  I have checked and there IS an index, specifically a primary key
index,on the branch_id in both schemas so I cannot figure out why the optimizer is looking at these differently.  In
fact,the table the branch_id comes from has the exact same indices and foreign keys on both schemas. <br /><br />Any
directionwould be deeply appreciated.<br /><br />Thanks!<br />Aaron<br /><br />-- <br
/>==================================================================<br/>   Aaron Bono<br />   Aranya Software
Technologies,Inc.<br />   <a href="http://www.aranya.com"> http://www.aranya.com</a><br />   <a
href="http://codeelixir.com">http://codeelixir.com</a><br
/>==================================================================