X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 31281529C3 for ; Thu, 28 Jul 2005 11:38:30 -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 68310-08 for ; Thu, 28 Jul 2005 14:38:28 +0000 (GMT) Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.196]) by svr1.postgresql.org (Postfix) with ESMTP id 1B4F6529A2 for ; Thu, 28 Jul 2005 11:38:25 -0300 (ADT) Received: by zproxy.gmail.com with SMTP id 8so325879nzo for ; Thu, 28 Jul 2005 07:38:28 -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=Mw+vxWEIT6/opwXb2A2+XZJ7+eKPGM+cT3pnmdh2HxIn+ib1e6XA6HN7v3+x7HD4OBkRtU4GCPXapo6g8pHdTIcpeMXiFxsrbXdWhDkCvMcysvQzI4pJp9skHk8iZOmWL+q6+89PGEQshooTsEYyW8aKPHlz4IioTOIBr0Z+Y08= Received: by 10.36.71.12 with SMTP id t12mr1976998nza; Thu, 28 Jul 2005 07:38:07 -0700 (PDT) Received: by 10.36.25.13 with HTTP; Thu, 28 Jul 2005 07:38:06 -0700 (PDT) Message-ID: <1d219a6f0507280738791f0ea3@mail.gmail.com> Date: Thu, 28 Jul 2005 10:38:06 -0400 From: Chris Hoover Reply-To: Chris Hoover To: pgsql-performance@postgresql.org Subject: Fwd: Help with view performance problem In-Reply-To: <1d219a6f050727092927746db1@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> <1d219a6f050727092927746db1@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/438 X-Sequence-Number: 13679 Does anyone have any suggestions on this? I did not get any response from the admin list. Thanks, Chris ---------- Forwarded message ---------- From: Chris Hoover Date: Jul 27, 2005 12:29 PM Subject: Re: Help with view performance problem To: pgsql-admin@postgresql.org I did some more testing, and ran the explain analyze on the problem. 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; 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? > > 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, > 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 > > (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; > > > Here is the explain analyze from the problem db: > prob_db=3D# explain analyze select * from clm_com; > > > > 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=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) > ~ > > > > Here is the explain analyze from a good db (on the same postgres cluster)= ; > good_db=3D# explain analyze select * from clm_com; > > > > > > 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) >