X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C312A52C45 for ; Wed, 27 Jul 2005 12:12:35 -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 76070-10 for ; Wed, 27 Jul 2005 15:12:28 +0000 (GMT) Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.207]) by svr1.postgresql.org (Postfix) with ESMTP id 8DB7E52BC5 for ; Wed, 27 Jul 2005 12:12:27 -0300 (ADT) Received: by zproxy.gmail.com with SMTP id 13so177137nzn for ; Wed, 27 Jul 2005 08:12:27 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; b=jP5pTM/Kw5CCnHGTd15Jb+rUJ8yLJYNzeSxd27A9iRgfl1zVcMzY06fUXUU/CG4KGAticXT68cF0uSnUlPK1kkDEw4obp96Z7/NITmxUquxrcc+bFBRhL60bdqyAA93iNLm+rSd9caaeVkjryONWv35UwBO+dt3TZE2iEgh1aBg= Received: by 10.36.177.1 with SMTP id z1mr983068nze; Wed, 27 Jul 2005 08:12:27 -0700 (PDT) Received: by 10.36.25.13 with HTTP; Wed, 27 Jul 2005 08:12:27 -0700 (PDT) Message-ID: <1d219a6f05072708122cf6abeb@mail.gmail.com> Date: Wed, 27 Jul 2005 11:12:27 -0400 From: Chris Hoover Reply-To: Chris Hoover To: pgsql-admin@postgresql.org Subject: Help with view performance problem Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.024 tagged_above=0 required=5 tests=AWL, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200507/222 X-Sequence-Number: 18278 I am having a problem with a view on one of my db's. This view is trying to sequentially can the 2 tables it is accessing. However, when I explain the view on most of my other db's (all have the same schema's), it is using the indexes. Can anyone please help me understand why postgres is choosing to sequenially scan both tables? Both tables in the view have a primary key defined on inv_nbr, inv_qfr. Vacuum and analyze have been run on the tables in question to try and make sure stats are up to date. Thanks, Chris PG - 7.3.4 RH 2.1 Here is the view definition: SELECT DISTINCT clmcom1.inv_nbr AS inventory_number,=20 clmcom1.inv_qfr AS inventory_qualifier,=20 clmcom1.pat_addr_1 AS patient_address_1,=20 clmcom1.pat_addr_2 AS patient_address_2,=20 clmcom1.pat_city AS patient_city,=20 clmcom1.pat_cntry AS patient_country,=20 clmcom1.pat_dob AS patient_date_of_birth,=20 clmcom1.pat_gender_cd AS patient_gender_code, clmcom1.pat_info_pregnancy_ind AS pregnancy_i= nd, clmcom1.pat_state AS patient_state,=20 clmcom1.pat_suffix AS patient_suffix,=20 clmcom1.pat_zip AS patient_zip_code,=20 clmcom1.payto_addr_1 AS payto_address_1,=20 clmcom1.payto_addr_2 AS payto_address_2,=20 clmcom1.payto_city,=20 clmcom1.payto_cntry AS payto_country,=20 clmcom1.payto_f_name AS payto_first_name,=20 clmcom1.payto_m_name AS payto_middle_name, clmcom1.payto_state,=20 clmcom1.payto_zip AS payto_zip_code,=20 clmcom1.clm_tot_clm_chgs AS total_claim_charg= e,=20 clmcom1.bill_l_name_org AS billing_last_name_or_org, clmcom1.clm_delay_rsn_cd AS claim_delay_reason_code, clmcom1.clm_submit_rsn_cd AS claim_submit_reason_code, clmcom1.payto_l_name_org AS payto_last_name_or_org, clmcom1.payto_prim_id AS payto_primary_id, = =20 clmcom1.bill_prim_id AS billing_prov_primary_= id, clmcom1.clm_tot_ncov_chgs AS total_ncov_charg= e,=20 clmcom2.contract_amt AS contract_amount,=20 clmcom2.svc_fac_or_lab_name,=20 clmcom2.svc_fac_addr_1 AS svc_fac_address_1,= =20 clmcom2.svc_fac_addr_2 AS svc_fac_address_2,= =20 clmcom2.svc_fac_city,=20 clmcom2.svc_fac_zip AS svc_fac_zip_code=20 FROM (clmcom1 LEFT JOIN clmcom2 ON (((clmcom1.inv_nbr =3D clmcom2.inv_nbr) AND =20 (clmcom1.inv_qfr =3D clmcom2.inv_qfr)))) ORDER BY clmcom1.inv_nbr,=20 clmcom1.inv_qfr,=20 clmcom1.pat_addr_1,=20 clmcom1.pat_addr_2,=20 clmcom1.pat_city,=20 clmcom1.pat_cntry, clmcom1.pat_dob,=20 clmcom1.pat_gender_cd,=20 clmcom1.pat_info_pregnancy_ind,=20 clmcom1.pat_state,=20 clmcom1.pat_suffix,=20 clmcom1.pat_zip,=20 clmcom1.payto_addr_1,=20 clmcom1.payto_addr_2,=20 clmcom1.payto_city,=20 clmcom1.payto_cntry, clmcom1.payto_f_name,=20 clmcom1.payto_m_name,=20 clmcom1.payto_state,=20 clmcom1.payto_zip,=20 clmcom1.clm_tot_clm_chgs,=20 clmcom1.bill_l_name_org,=20 clmcom1.clm_delay_rsn_cd,=20 clmcom1.clm_submit_rsn_cd,=20 clmcom1.payto_l_name_org,=20 clmcom1.payto_prim_id,=20 clmcom1.bill_prim_id,=20 clmcom1.clm_tot_ncov_chgs,=20 clmcom2.contract_amt,=20 clmcom2.svc_fac_or_lab_name,=20 clmcom2.svc_fac_addr_1,=20 clmcom2.svc_fac_addr_2,=20 clmcom2.svc_fac_city,=20 clmcom2.svc_fac_zip; Here is the explain analyze from the problem db: prob_db=3D# explain analyze select * from clm_com; =20 QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------- Subquery Scan clm_com (cost=3D1039824.35..1150697.61 rows=3D126712 width=3D367) (actual time=3D311792.78..405819.03 rows=3D1266114 loops=3D1) -> Unique (cost=3D1039824.35..1150697.61 rows=3D126712 width=3D367) (actual time=3D311792.74..386313.14 rows=3D1266114 loops=3D1) -> Sort (cost=3D1039824.35..1042992.16 rows=3D1267123 width=3D367) (actual time=3D311792.74..338189.48 rows=3D1266114 loops=3D1) Sort Key: clmcom1.inv_nbr, clmcom1.inv_qfr, clmcom1.pat_addr_1, clmcom1.pat_addr_2, clmcom1.pat_city, clmcom1.pat_cntry, clmcom1.pat_dob, clmcom1.pat_gender_cd, clmcom1.pat_info_pregnancy_ind, clmcom1.pat_state, clmcom1.pat_suffix, clmcom1.pat_zip, clmcom1.payto_addr_1, clmcom1.payto_addr_2, clmcom1.payto_city, clmcom1.payto_cntry, clmcom1.payto_f_name, clmcom1.payto_m_name, clmcom1.payto_state, clmcom1.payto_zip, clmcom1.clm_tot_clm_chgs, clmcom1.bill_l_name_org, clmcom1.clm_delay_rsn_cd, clmcom1.clm_submit_rsn_cd, clmcom1.payto_l_name_org, clmcom1.payto_prim_id, clmcom1.bill_prim_id, clmcom1.clm_tot_ncov_chgs, clmcom2.contract_amt, clmcom2.svc_fac_or_lab_name, clmcom2.svc_fac_addr_1, clmcom2.svc_fac_addr_2, clmcom2.svc_fac_city, clmcom2.svc_fac_zip -> Hash Join (cost=3D132972.78..548171.70 rows=3D1267123 width=3D367) (actual time=3D16999.32..179359.43 rows=3D1266114 loops=3D1) Hash Cond: ("outer".inv_nbr =3D "inner".inv_nbr) Join Filter: ("outer".inv_qfr =3D "inner".inv_qfr) -> Seq Scan on clmcom1 (cost=3D0.00..267017.23 rows=3D1267123 width=3D271) (actual time=3D0.11..84711.83 rows=3D1266114 loops=3D1) -> Hash (cost=3D111200.82..111200.82 rows=3D1269582 width=3D96) (actual time=3D16987.45..16987.45 rows=3D0 loops=3D1) -> Seq Scan on clmcom2=20 (cost=3D0.00..111200.82 rows=3D1269582 width=3D96) (actual time=3D0.07..12164.81 rows=3D1266108 loops=3D1) Total runtime: 407317.47 msec (11 rows) ~ Here is the explain analyze from a good db (on the same postgres cluster); good_db=3D# explain analyze select * from clm_com; =20 =20 =20 =20 =20 QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------- Subquery Scan clm_com (cost=3D78780.59..89498.29 rows=3D12249 width=3D359= ) (actual time=3D73045.36..79974.37 rows=3D122494 loops=3D1) -> Unique (cost=3D78780.59..89498.29 rows=3D12249 width=3D359) (actual time=3D73045.28..78031.99 rows=3D122494 loops=3D1) -> Sort (cost=3D78780.59..79086.81 rows=3D122488 width=3D359) (actual time=3D73045.28..73362.94 rows=3D122494 loops=3D1) Sort Key: clmcom1.inv_nbr, clmcom1.inv_qfr, clmcom1.pat_addr_1, clmcom1.pat_addr_2, clmcom1.pat_city, clmcom1.pat_cntry, clmcom1.pat_dob, clmcom1.pat_gender_cd, clmcom1.pat_info_pregnancy_ind, clmcom1.pat_state, clmcom1.pat_suffix, clmcom1.pat_zip, clmcom1.payto_addr_1, clmcom1.payto_addr_2, clmcom1.payto_city, clmcom1.payto_cntry, clmcom1.payto_f_name, clmcom1.payto_m_name, clmcom1.payto_state, clmcom1.payto_zip, clmcom1.clm_tot_clm_chgs, clmcom1.bill_l_name_org, clmcom1.clm_delay_rsn_cd, clmcom1.clm_submit_rsn_cd, clmcom1.payto_l_name_org, clmcom1.payto_prim_id, clmcom1.bill_prim_id, clmcom1.clm_tot_ncov_chgs, clmcom2.contract_amt, clmcom2.svc_fac_or_lab_name, clmcom2.svc_fac_addr_1, clmcom2.svc_fac_addr_2, clmcom2.svc_fac_city, clmcom2.svc_fac_zip -> Merge Join (cost=3D0.00..56945.12 rows=3D122488 width=3D359) (actual time=3D54.76..71635.65 rows=3D122494 loops=3D1) Merge Cond: (("outer".inv_nbr =3D "inner".inv_nbr) AND ("outer".inv_qfr =3D "inner".inv_qfr)) -> Index Scan using clmcom1_pkey on clmcom1=20 (cost=3D0.00..38645.61 rows=3D122488 width=3D267) (actual time=3D25.60..49142.16 rows=3D122494 loops=3D1) -> Index Scan using clmcom2_pkey on clmcom2=20 (cost=3D0.00..16004.08 rows=3D122488 width=3D92) (actual time=3D29.09..19418.94 rows=3D122494 loops=3D1) Total runtime: 80162.26 msec (9 rows)