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 C822E52E87 for ; Wed, 27 Jul 2005 13:29:20 -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 04284-02 for ; Wed, 27 Jul 2005 16:29:16 +0000 (GMT) Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.203]) by svr1.postgresql.org (Postfix) with ESMTP id 87BE452E52 for ; Wed, 27 Jul 2005 13:29:15 -0300 (ADT) Received: by zproxy.gmail.com with SMTP id 8so169217nzo for ; Wed, 27 Jul 2005 09:29:15 -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:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=P8e4w7oXjC0mSb023HnNcF+pzI0BLq9wi4mySnMtyJNKIEOmsgTZMddlHx02WYCSY545ZnRK+iehHOg3Gu/5STzfG2bsy/+S1Kb9OAYegjxvnZ/eRnryZmM1qiIk/naadKGRofBZvpTT+sN5BhlOvp7mpNpck2zHbq2JelVw5Uc= Received: by 10.36.7.20 with SMTP id 20mr1087241nzg; Wed, 27 Jul 2005 09:29:15 -0700 (PDT) Received: by 10.36.25.13 with HTTP; Wed, 27 Jul 2005 09:29:14 -0700 (PDT) Message-ID: <1d219a6f050727092927746db1@mail.gmail.com> Date: Wed, 27 Jul 2005 12:29:14 -0400 From: Chris Hoover Reply-To: Chris Hoover To: pgsql-admin@postgresql.org Subject: Re: Help with view performance problem In-Reply-To: <1d219a6f05072708122cf6abeb@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <1d219a6f05072708122cf6abeb@mail.gmail.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.024 tagged_above=0 required=5 tests=RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200507/226 X-Sequence-Number: 18282 I did some more testing, and ran the explain analyze on the problem.=20 In my session I did a set enable_hashjoin =3D false and then ran the analyze. This caused it to use the indexes as I have been expecting it to do. Now, how can I get it to use the indexes w/o manipulating the environment? What make postgresql want to sequentially scan and use a hash join? thanks, Chris explain analyze with set_hashjoin=3Dfalse; prob_db=3D#explain analyze select * from clm_com; =20 QUERY PLAN ---------------------------------------------------------------------------= ------------------------------------------------------------------- ---------------------------------------------------------------------------= ------------------------------------------------------------------- ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------ Subquery Scan clm_com (cost=3D1057975.45..1169021.26 rows=3D126910 width=3D366) (actual time=3D142307.99..225997.22 rows=3D1268649 loops=3D1) -> Unique (cost=3D1057975.45..1169021.26 rows=3D126910 width=3D366) (actual time=3D142307.96..206082.30 rows=3D1268649 loops=3D1) -> Sort (cost=3D1057975.45..1061148.19 rows=3D1269095 width=3D366) (actual time=3D142307.95..156019.01 rows=3D1268649 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..565541.46 rows=3D1269095 width=3D366) (actual time=3D464.89..130638.06 rows=3D1268649 loops=3D1) Merge Cond: ("outer".inv_nbr =3D "inner".inv_nbr) Join Filter: ("outer".inv_qfr =3D "inner".inv_qfr) -> Index Scan using clmcom1_inv_nbr_iview_idx on clmcom1 (cost=3D0.00..380534.32 rows=3D1269095 width=3D270) (actual time=3D0.27..82159.37 rows=3D1268649 loops=3D1) -> Index Scan using clmcom2_inv_nbr_iview_idx on clmcom2 (cost=3D0.00..159636.25 rows=3D1271198 width=3D96) (actual time=3D464.56..21774.02 rows=3D1494019 loops=3D1) Total runtime: 227369.39 msec (10 rows) On 7/27/05, Chris Hoover wrote: > 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? >=20 > 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. >=20 > Thanks, >=20 > Chris > PG - 7.3.4 > RH 2.1 >=20 >=20 > Here is the view definition: > SELECT DISTINCT clmcom1.inv_nbr AS inventory_number, > clmcom1.inv_qfr AS inventory_qualifier, > clmcom1.pat_addr_1 AS patient_address_1, > clmcom1.pat_addr_2 AS patient_address_2, > clmcom1.pat_city AS patient_city, > clmcom1.pat_cntry AS patient_country, > clmcom1.pat_dob AS patient_date_of_birth, > clmcom1.pat_gender_cd AS patient_gender_cod= e, > clmcom1.pat_info_pregnancy_ind AS pregnancy= _ind, > clmcom1.pat_state AS patient_state, > clmcom1.pat_suffix AS patient_suffix, > clmcom1.pat_zip AS patient_zip_code, > clmcom1.payto_addr_1 AS payto_address_1, > clmcom1.payto_addr_2 AS payto_address_2, > clmcom1.payto_city, > clmcom1.payto_cntry AS payto_country, > clmcom1.payto_f_name AS payto_first_name, > clmcom1.payto_m_name AS payto_middle_name, > clmcom1.payto_state, > clmcom1.payto_zip AS payto_zip_code, > clmcom1.clm_tot_clm_chgs AS total_claim_cha= rge, > 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, > clmcom1.bill_prim_id AS billing_prov_primar= y_id, > clmcom1.clm_tot_ncov_chgs AS total_ncov_cha= rge, > clmcom2.contract_amt AS contract_amount, > clmcom2.svc_fac_or_lab_name, > clmcom2.svc_fac_addr_1 AS svc_fac_address_1= , > clmcom2.svc_fac_addr_2 AS svc_fac_address_2= , > clmcom2.svc_fac_city, > clmcom2.svc_fac_zip AS svc_fac_zip_code > 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, > 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; >=20 >=20 > Here is the explain analyze from the problem db: > prob_db=3D# explain analyze select * from clm_com; >=20 >=20 >=20 > QUERY PLAN >=20 >=20 >=20 > -------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------- > 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=3D126958= 2 > width=3D96) (actual time=3D16987.45..16987.45 rows=3D0 loops=3D1) > -> Seq Scan on clmcom2 > (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) > ~ >=20 >=20 >=20 > 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=3D3= 59) > (actual time=3D73045.36..79974.37 rows=3D122494 loops=3D1) > -> Unique (cost=3D78780.59..89498.29 rows=3D12249 width=3D359) (actu= al > 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 > (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 > (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) >