Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 2D9AA9FB2A4 for ; Sat, 5 May 2007 18:06:00 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 16019-04 for ; Sat, 5 May 2007 18:05:52 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.241]) by postgresql.org (Postfix) with ESMTP id 552AB9FB2C4 for ; Sat, 5 May 2007 18:05:54 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id d17so29339and for ; Sat, 05 May 2007 14:05:53 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:sender:to:subject:mime-version:content-type:x-google-sender-auth; b=ASMYPhph+IrG78J1WekOAl2b7wnaXXCUgAlhLVx3wWKDsqB/+Y44nOwiRMibEfCNz/mpfLJtpYM6qg13s7s32Z2PLaE9SBFN6LWyHPJNc8z184srGXeYKFxemcUWl4KdSVDjhKcTRgKICKwYN6IQUbdreHet0EYz+NGeyNRpMFg= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:sender:to:subject:mime-version:content-type:x-google-sender-auth; b=c4vDzkSmPfpy4VhfcmHqnnUEYQIKoJ8Hun5zXqO6VkI6mWGAk6pzVVIbx3Yile+AEiAcpJGu+8mle1+HRTL1pqK5K8sN4HcT0uRNBayNkDsx59YXCrqogjBI/rpzcoi04CW3pRPC2jz/UXK2wvRCoeXQArk736JqRBDAuwTu05g= Received: by 10.100.248.18 with SMTP id v18mr3725935anh.1178399152941; Sat, 05 May 2007 14:05:52 -0700 (PDT) Received: by 10.100.144.18 with HTTP; Sat, 5 May 2007 14:05:52 -0700 (PDT) Message-ID: Date: Sat, 5 May 2007 16:05:52 -0500 From: "Aaron Bono" To: "PostgreSQL SQL List" Subject: Sequence vs. Index Scan MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_70026_32737427.1178399152866" X-Google-Sender-Auth: 7a135fd482da04cc X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/25 X-Sequence-Number: 28306 ------=_Part_70026_32737427.1178399152866 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 ================================================================== ------=_Part_70026_32737427.1178399152866 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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
================================================================== ------=_Part_70026_32737427.1178399152866--