Does anyone have any suggestions on this? I did not get any response
from the admin list.
Thanks,
Chris
-- Forwarded message --
From: Chris Hoover [EMAIL PROTECTED]
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 = 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=false;
prob_db=#explain analyze select * from clm_com;
QUERY PLAN
--
--
Subquery Scan clm_com (cost=1057975.45..1169021.26 rows=126910
width=366) (actual time=142307.99..225997.22 rows=1268649 loops=1)
- Unique (cost=1057975.45..1169021.26 rows=126910 width=366)
(actual time=142307.96..206082.30 rows=1268649 loops=1)
- Sort (cost=1057975.45..1061148.19 rows=1269095
width=366) (actual time=142307.95..156019.01 rows=1268649 loops=1)
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=0.00..565541.46 rows=1269095
width=366) (actual time=464.89..130638.06 rows=1268649 loops=1)
Merge Cond: (outer.inv_nbr = inner.inv_nbr)
Join Filter: (outer.inv_qfr = inner.inv_qfr)
- Index Scan using clmcom1_inv_nbr_iview_idx on
clmcom1 (cost=0.00..380534.32 rows=1269095 width=270) (actual
time=0.27..82159.37 rows=1268649 loops=1)
- Index Scan using clmcom2_inv_nbr_iview_idx on
clmcom2 (cost=0.00..159636.25 rows=1271198 width=96) (actual
time=464.56..21774.02 rows=1494019 loops=1)
Total runtime: 227369.39 msec
(10 rows)
On 7/27/05, Chris Hoover [EMAIL PROTECTED] 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_code,
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
