here are the attachments.
Not sure about the statistics question, I have done a vacuum analyze on every table in the database.
On Thu, 2006-05-18 at 09:12 -0700, Tomeh, Husam wrote:
It looks like you forgot to attach the query sample. Have you collected statistics on your tables/indexes to help the planner select a better plan?
--
Husam
http://firstdba.googlepages.com
From: pgsql-admin-owner@xxxxxxxxxxxxxx [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On Behalf Of Warren Little
Sent: Thursday, May 18, 2006 9:06 AM
To: pgsql-admin@xxxxxxxxxxxxxx
Subject: [ADMIN] query optimization - mysql vs postgresql
Hello,
my team is in the process of migrating some data from a mysql (5.0) database to our core postgres (8.1.3) database.
We are running into some performance issues with the postgres versions of the queries.
MySQL takes about 150ms to run the query where postgres is taking 2500ms.
The servers for the two database clusters are identical (dual amd 64bit dual core opteron 4GB ram scsi raid array Suse 9.x)
The table schemas have not changed (to the best of my knowledge) and the indexes were created to mimic the mysql versions as well.
I have attached one particular query along with the explain output.
Does anyone see anything in the explain that might help in diagnosing the problem.
thx
Warren J. Little
CTO
Meridias Capital
1018 West Atherton Dr
Salt Lake City, UT 84123
Ph 866.369.7763
**********************************************************************
This message contains confidential information intended only for the use of the addressee(s) named above and may contain information that is legally privileged. If you are not the addressee, or the person responsible for delivering it to the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is strictly prohibited. If you have received this message by mistake, please immediately notify us by replying to the message and delete the original message immediately thereafter.
Thank you.
FADLD Tag
**********************************************************************
|
Warren J. Little CTO Meridias Capital 1018 West Atherton Dr Salt Lake City, UT 84123 Ph 866.369.7763 |
;; This buffer is for notes you don't want to save, and for Lisp evaluation.
;; If you want to create a file, visit that file with C-x C-f,
;; then enter the text in that file's own buffer.
SELECT *,
MAX(cashoutMaxAmt) cashoutMaxAmt,
product.prodKey AS prodKey,
UNIX_TIMESTAMP(product.lastModifiedTs) AS lastModifiedTs,
product.comment AS prodComment,
productGeneral.comment AS condComment FROM product, productCondition, productLockTerm, productGeneral LEFT JOIN productPropertyUse ON (productGeneral.prodGeneralKey=productPropertyUse.prodGeneralKey) LEFT JOIN productPrepay ON (productGeneral.prodGeneralKey=productPrepay.prodGeneralKey) LEFT JOIN productLoanPurpose ON (productGeneral.prodGeneralKey=productLoanPurpose.prodGeneralKey) LEFT JOIN productDocLevel ON (productGeneral.prodGeneralKey=productDocLevel.prodGeneralKey) LEFT JOIN productPropertyType ON (productGeneral.prodGeneralKey=productPropertyType.prodGeneralKey) LEFT JOIN productState ON (productGeneral.prodGeneralKey=productState.prodGeneralKey) LEFT JOIN productMortgageLate AS mortgageLate0 ON (productGeneral.mortLateKey =mortgageLate0.prodMortLateKey) LEFT JOIN productMortgageLate AS mortgageLate12 ON (productGeneral.mortLate12Key=mortgageLate12.prodMortLateKey) LEFT JOIN productMortgageLate AS mortgageLate24 ON (productGeneral.mortLate24Key=mortgageLate24.prodMortLateKey) WHERE product.prodKey = productLockTerm.prodKey AND product.prodKey = productCondition.prodKey AND productGeneral.prodGeneralKey = productCondition.prodGeneralKey AND lockTerm = 'B30' AND productGeneral.disable = 'E' AND product.disable = 'E' AND productLockTerm .disable = 'E' AND lienPri = '1' AND exception='YES' AND ((loanAmtFrom=0 AND loanAmtTo=0) || (loanAmtFrom IS NULL AND loanAmtTo IS NULL) || (loanAmtTo >= '400000' AND loanAmtFrom <= '400000')) AND ((ltvFromPct=0 AND ltvToPct=0) || (ltvFromPct IS NULL AND ltvToPct IS NULL) || (ltvToPct >= '80.000' AND ltvFromPct <= '80.000')) AND ((cltvFromPct=0 AND cltvToPct=0) || (cltvFromPct IS NULL AND cltvToPct IS NULL) || (cltvToPct >= '100.000' AND cltvFromPct <= '100.000')) AND ((crdscrFrom=0 AND crdscrTo=0) || (crdscrFrom IS NULL AND crdscrTo IS NULL) || (crdscrTo >= '720' AND crdscrFrom <= '720')) AND ((totalLienMinAmt=0 AND totalLienMaxAmt=0) || (totalLienMinAmt IS NULL AND totalLienMaxAmt IS NULL) || (totalLienMaxAmt >= '400000' AND totalLienMinAmt <= '400000')) AND ((secondaryFinance='NO' AND '80.000'='100.000' ) || (secondaryFinance='YES' AND ((maxLtvSecondary >='80.000') || ((maxLtvSecondary IS NULL || maxLtvSecondary=0 || '80.000'='100.000') ) ))) AND ((frontRatioPct=0) || (frontRatioPct IS NULL) || (frontRatioPct < '0.000')) AND ((backRatioPct=0) || (backRatioPct IS NULL) || (backRatioPct < '10.000')) AND ((impnd ='NA') || (impnd ='BOTH') || (impnd = 'YES')) AND ((mortIns ='NA') || (mortIns ='BOTH') || (mortIns = 'NO')) AND ((frgnNatl ='NA') || (frgnNatl ='BOTH') || (frgnNatl = 'NO')) AND (intOnly = 'NO') AND ((propUseApl ='NO') || (propUseApl ='YES' AND propUse = 'PRIM')) AND ((propTypeApl='NO') || (propTypeApl='YES' AND (propType = 'DETACH') )) AND ((loanPurpApl='NO') || (loanPurpApl='YES' AND loanPurp = 'P')) AND ((docLevelApl='NO') || (docLevelApl='YES' AND docLevel = 'AF')) AND ((stateApl ='NO') || (stateApl ='YES' AND state = 'UT')) AND mortgageLate0.mortLate30 >= '0' AND mortgageLate0.mortLate60 >= '0' AND mortgageLate0.mortLate90 >= '0' AND mortgageLate12.mortLate30 >= '0' AND mortgageLate12.mortLate60 >= '0' AND mortgageLate12.mortLate90 >= '0' AND ((prepayApl ='NO') || (prepay='P0')) AND prodCat = '14' GROUP BY product.prodKey, prepay ORDER BY prodCode ASC
QUERY PLAN
Sort (cost=104034.78..104034.78 rows=1 width=217) (actual time=11431.083..11431.089 rows=4 loops=1)
Sort Key: product.prod_code
-> GroupAggregate (cost=104034.72..104034.77 rows=1 width=217) (actual time=11431.021..11431.044 rows=4 loops=1)
-> Sort (cost=104034.72..104034.72 rows=1 width=217) (actual time=11430.976..11430.981 rows=4 loops=1)
Sort Key: product.prod_key, product_prepay.prepay, product."comment", product_general."comment", product.prod_code, product.guideline, product.lien_pri, product_condition.front_ratio_pct, product_condition.back_ratio_pct, product_condition.chap_7_bankr_discharge_mo, product_condition.chap_13_bankr_discharge_mo, product_condition.fclose_discharge_mo
-> Nested Loop (cost=0.00..104034.71 rows=1 width=217) (actual time=6719.403..11430.930 rows=4 loops=1)
-> Nested Loop (cost=0.00..104031.67 rows=1 width=225) (actual time=6719.383..11430.856 rows=4 loops=1)
-> Nested Loop (cost=0.00..104028.64 rows=1 width=233) (actual time=6719.327..11430.695 rows=4 loops=1)
-> Nested Loop (cost=0.00..104020.28 rows=1 width=241) (actual time=6719.252..11430.561 rows=4 loops=1)
-> Merge Join (cost=0.00..103983.12 rows=11 width=128) (actual time=6719.148..11430.385 rows=4 loops=1)
Merge Cond: ("outer".prod_general_key = "inner".prod_general_key)
-> Merge Left Join (cost=0.00..99852.29 rows=69650 width=89) (actual time=0.724..11376.116 rows=2443 loops=1)
Merge Cond: ("outer".prod_general_key = "inner".prod_general_key)
Filter: ((("outer".state_apl)::text = 'NO'::text) OR ((("outer".state_apl)::text = 'YES'::text) AND (("inner".state)::text = 'NV'::text)))
-> Merge Left Join (cost=0.00..26472.83 rows=5878 width=95) (actual time=0.484..2634.270 rows=2770 loops=1)
Merge Cond: ("outer".prod_general_key = "inner".prod_general_key)
Filter: ((("outer".prop_type_apl)::text = 'NO'::text) OR ((("outer".prop_type_apl)::text = 'YES'::text) AND (("inner".prop_type)::text = 'DETACH'::text)))
-> Merge Left Join (cost=0.00..15298.89 rows=3041 width=101) (actual time=0.408..1394.324 rows=3711 loops=1)
Merge Cond: ("outer".prod_general_key = "inner".prod_general_key)
Filter: ((("outer".doc_level_apl)::text = 'NO'::text) OR ((("outer".doc_level_apl)::text = 'YES'::text) AND (("inner".doc_level)::text = 'AF'::text)))
-> Merge Left Join (cost=0.00..13822.28 rows=3041 width=107) (actual time=0.341..1198.880 rows=5639 loops=1)
Merge Cond: ("outer".prod_general_key = "inner".prod_general_key)
Filter: ((("outer".loan_purp_apl)::text = 'NO'::text) OR ((("outer".loan_purp_apl)::text = 'YES'::text) AND (("inner".loan_purp)::text = 'P'::text)))
-> Merge Left Join (cost=0.00..11473.97 rows=3041 width=113) (actual time=0.261..891.512 rows=7587 loops=1)
Merge Cond: ("outer".prod_general_key = "inner".prod_general_key)
Filter: ((("outer".prepay_apl)::text = 'NO'::text) OR (("inner".prepay)::text = 'P0'::text))
-> Merge Left Join (cost=0.00..8179.19 rows=3041 width=113) (actual time=0.191..459.599 rows=10103 loops=1)
Merge Cond: ("outer".prod_general_key = "inner".prod_general_key)
Filter: ((("outer".prop_use_apl)::text = 'NO'::text) OR ((("outer".prop_use_apl)::text = 'YES'::text) AND (("inner".prop_use)::text = 'PRIM'::text)))
-> Index Scan using product_general_pkey on product_general (cost=0.00..6726.08 rows=3041 width=119) (actual time=0.123..231.697 rows=15535 loops=1)
Filter: ((("disable")::text = 'E'::text) AND (((loan_amt_from = 0::numeric) AND (loan_amt_to = 0::numeric)) OR ((loan_amt_from IS NULL) AND (loan_amt_to IS NULL)) OR ((loan_amt_to >= 400000::numeric) AND (loan_amt_from <= 400000::numeric))) AND (((ltv_from_pct = 0::numeric) AND (ltv_to_pct = 0::numeric)) OR ((ltv_from_pct IS NULL) AND (ltv_to_pct IS NULL)) OR ((ltv_to_pct >= 80.000) AND (ltv_from_pct <= 80.000))) AND (((cltv_from_pct = 0::numeric) AND (cltv_to_pct = 0::numeric)) OR ((cltv_from_pct IS NULL) AND (cltv_to_pct IS NULL)) OR ((cltv_to_pct >= 80.000) AND (cltv_from_pct <= 80.000))) AND (((crd_scr_from = 0) AND (crd_scr_to = 0)) OR ((crd_scr_from IS NULL) AND (crd_scr_to IS NULL)) OR ((crd_scr_to >= 800) AND (crd_scr_from <= 800))) AND (((impnd)::text = 'NA'::text) OR ((impnd)::text = 'BOTH'::text) OR ((impnd)::text = 'YES'::text)) AND (((mort_ins)::text = 'NA'::text) OR ((mort_ins)::text = 'BOTH'::text) OR ((mort_ins)::text = 'NO'::text)) AND (((frgn_natl)::text = 'NA'::text) OR ((frgn_natl)::text = 'BOTH'::text) OR ((frgn_natl)::text = 'NO'::text)))
-> Index Scan using product_property_use_pkey on product_property_use (cost=0.00..1308.44 rows=40404 width=16) (actual time=0.056..91.965 rows=40405 loops=1)
-> Index Scan using product_prepay_pkey on product_prepay (cost=0.00..2976.11 rows=95288 width=14) (actual time=0.062..210.527 rows=95288 loops=1)
-> Index Scan using product_loan_purpose_pkey on product_loan_purpose (cost=0.00..2112.61 rows=67243 width=15) (actual time=0.063..149.291 rows=67240 loops=1)
-> Index Scan using product_doc_level_pkey on product_doc_level (cost=0.00..1325.69 rows=42249 width=14) (actual time=0.055..94.493 rows=42249 loops=1)
-> Index Scan using product_property_type_pkey on product_property_type (cost=0.00..10145.64 rows=300912 width=17) (actual time=0.063..659.075 rows=300912 loops=1)
-> Index Scan using product_state_pkey on product_state (cost=0.00..64240.87 rows=2159903 width=14) (actual time=0.074..4651.861 rows=2159466 loops=1)
-> Index Scan using product_condition_i2 on product_condition (cost=0.00..3481.60 rows=9 width=55) (actual time=13.411..47.081 rows=1275 loops=1)
Filter: (((exception)::text = 'YES'::text) AND (((total_lien_min_amt = 0::numeric) AND (total_lien_max_amt = 0::numeric)) OR ((total_lien_min_amt IS NULL) AND (total_lien_max_amt IS NULL)) OR ((total_lien_max_amt >= 400000::numeric) AND (total_lien_min_amt <= 400000::numeric))) AND (((secondary_finance)::text = 'NO'::text) OR ((secondary_finance)::text = 'YES'::text)) AND ((front_ratio_pct = 0::numeric) OR (front_ratio_pct IS NULL) OR (front_ratio_pct < 0.000)) AND ((back_ratio_pct = 0::numeric) OR (back_ratio_pct IS NULL) OR (back_ratio_pct < 10.000)))
-> Index Scan using product_pkey on product (cost=0.00..3.37 rows=1 width=113) (actual time=0.030..0.033 rows=1 loops=4)
Index Cond: (product.prod_key = "outer".prod_key)
Filter: ((("disable")::text = 'E'::text) AND ((lien_pri)::text = '1'::text) AND ((int_only)::text = 'NO'::text) AND ((prod_cat)::text = '14'::text))
-> Index Scan using product_lock_term_i0 on product_lock_term (cost=0.00..8.35 rows=1 width=8) (actual time=0.022..0.025 rows=1 loops=4)
Index Cond: ("outer".prod_key = product_lock_term.prod_key)
Filter: (((lock_term)::text = 'B30'::text) AND (("disable")::text = 'E'::text))
-> Index Scan using product_mortgage_late_pkey on product_mortgage_late mortgagelate12 (cost=0.00..3.02 rows=1 width=8) (actual time=0.029..0.031 rows=1 loops=4)
Index Cond: ("outer".mort_late_12_key = mortgagelate12.prod_mort_late_key)
Filter: ((mort_late_30 >= 0) AND (mort_late_60 >= 0) AND (mort_late_90 >= 0))
-> Index Scan using product_mortgage_late_pkey on product_mortgage_late mortgagelate0 (cost=0.00..3.02 rows=1 width=8) (actual time=0.008..0.010 rows=1 loops=4)
Index Cond: ("outer".mort_late_key = mortgagelate0.prod_mort_late_key)
Filter: ((mort_late_30 >= 0) AND (mort_late_60 >= 0) AND (mort_late_90 >= 0))
Total runtime: 11431.640 ms
