I have two schemas, both with the exact same DDL. One performs great and
the other has problems with a specific query:
SELECT *
FROM
branch_active_vw
WHERE branch_id = get_branch_for_zip ( '22151' )
ORDER BY branch_name
;
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.
The explain plans are as follows:
Fast Schema:
1. Sort (cost=17.50..17.51 rows=1 width=680) (actual time=
2838.583..2838.586 rows=1 loops=1)
2. Sort Key: branch.branch_name
3. -> Nested Loop Left Join (cost=0.00..17.49 rows=1 width=680) (actual
time=2838.060..2838.093 rows=1 loops=1)
4. Join Filter: ("inner".branch_group_id = "outer".branch_group_id)
5. -> Nested Loop (cost=0.00..11.45 rows=1 width=647) (actual
time=2837.776..2837.804 rows=1 loops=1)
6. -> Nested Loop (cost=0.00..7.88 rows=1 width=618) (actual
time=2837.697..2837.716 rows=1 loops=1)
7. Join Filter: ("inner".locale_id = "outer".locale_id)
8. -> Nested Loop (cost=0.00..6.86 rows=1 width=598)
(actual time=2837.666..2837.676 rows=1 loops=1)
9. Join Filter: ("inner".corp_id =
"outer".corp_id)
10. -> Index Scan using branch_pkey on branch
(cost=0.00..5.84 rows=1 width=560) (actual time=2837.621..2837.624 rows=1
loops=1)
11. Index Cond: (branch_id =
get_branch_for_zip('22151'::character varying))
12. Filter: ((start_day <= now()) AND
((end_day IS NULL) OR (end_day >= now())))
13. -> Seq Scan on corp (cost=0.00..1.01 rows=1
width=46) (actual time=0.015..0.017 rows=1 loops=1)
14. -> Seq Scan on locale (cost=0.00..1.01 rows=1
width=28) (actual time=0.014..0.016 rows=1 loops=1)
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)
16. Index Cond: (branch_address_zip_cd.zip_cd_id =
"outer".branch_address_zip_id)
17. -> Seq Scan on branch_group (cost=0.00..1.07 rows=7 width=41)
(actual time=0.013..0.029 rows=7 loops=1)
18. SubPlan
19. -> Seq Scan on branch_area (cost=0.00..4.89 rows=1 width=6)
(actual time=0.132..0.137 rows=2 loops=1)
20. Filter: (branch_id = $0)
21. Total runtime: 2839.044 ms
Slow Schema:
Sort (cost=12.77..12.78 rows=1 width=1380) (actual time=
157492.513..157492.515 rows=1 loops=1)
1. Sort Key: branch.branch_name
2. -> Nested Loop Left Join (cost=0.00..12.76 rows=1 width=1380) (actual
time=130130.384..157492.484 rows=1 loops=1)
3. Join Filter: ("inner".branch_group_id = "outer".branch_group_id)
4. -> Nested Loop (cost=0.00..10.34 rows=1 width=1360) (actual
time=130130.157..157492.253 rows=1 loops=1)
5. Join Filter: ("inner".locale_id = "outer".locale_id)
6. -> Nested Loop (cost=0.00..9.31 rows=1 width=1340)
(actual time=130130.127..157492.213 rows=1 loops=1)
7. -> Nested Loop (cost=0.00..5.75 rows=1 width=1311)
(actual time=130130.042..157492.119 rows=1 loops=1)
8. Join Filter: ("inner".corp_id =
"outer".corp_id)
9. -> Seq Scan on branch (cost=0.00..4.72 rows=1
width=1281) (actual time=130129.988..157492.057 rows=1 loops=1)
10. Filter: ((start_day <= now()) AND
((end_day IS NULL) OR (end_day >= now())) AND (branch_id =
get_branch_for_zip('22151'::character varying)))
11. -> Seq Scan on corp (cost=0.00..1.01 rows=1
width=38) (actual time=0.022..0.024 rows=1 loops=1)
12. -> 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.070..0.073 rows=1 loops=1)
13. Index Cond: (branch_address_zip_cd.zip_cd_id =
"outer".branch_address_zip_id)
14. -> Seq Scan on locale (cost=0.00..1.01 rows=1 width=28)
(actual time=0.013..0.015 rows=1 loops=1)
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)
16. SubPlan
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)
18. Filter: (branch_id = $0)
19. Total runtime: 157492.890 ms
The problem is that lines 10-12 on the fast schema show an index scan while
lines 9-10 of the slow schema show a sequence scan. The sequence scan of
the branch_id, combined with 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.
Any direction would be deeply appreciated.
Thanks!
Aaron
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
