X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 6DA0CD1C4C1 for ; Wed, 6 Aug 2003 12:43:24 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 18465-08 for ; Wed, 6 Aug 2003 09:42:05 -0300 (ADT) Received: from fw.wubn.net (unknown [213.156.64.226]) by svr1.postgresql.org (Postfix) with ESMTP id 0F381D1C4BD for ; Wed, 6 Aug 2003 09:43:07 -0300 (ADT) Received: from (172.24.4.34) by fw.wubn.net (8.12.9/8.11.3) with ESMTP id h76Ch7Pt002774 for ; Wed, 6 Aug 2003 15:43:08 +0300 (EEST) (envelope-from yamazurak@Lviv.Bank.Gov.UA) Received: from (172.24.4.57) by MTA (8.11.5/8.11.2) with ESMTP id h76Ch6f18473 for ; Wed, 6 Aug 2003 15:43:06 +0300 Message-ID: <3F30F7CF.6090900@lviv.bank.gov.ua> Date: Wed, 06 Aug 2003 15:42:55 +0300 From: Yaroslav Mazurak Organization: National Bank of Ukraine Lviv OU User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.4) Gecko/20030624 X-Accept-Language: uk, ru, en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: PostgreSQL performance problem -> tuning References: <3F30AF9E.2080607@lviv.bank.gov.ua> <200308061030.36610.dev@archonet.com> In-Reply-To: <200308061030.36610.dev@archonet.com> Content-Type: multipart/signed; protocol="application/x-pkcs7-signature"; micalg=sha1; boundary="------------ms000008090800050008060007" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/98 X-Sequence-Number: 2915 This is a cryptographically signed message in MIME format. --------------ms000008090800050008060007 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Hi All! First, thanks for answers. Richard Huxton wrote: > On Wednesday 06 August 2003 08:34, Yaroslav Mazurak wrote: > Version 7.3.4 is just out - probably worth upgrading as soon as it's > convenient. Has version 7.3.4 significant performance upgrade relative to 7.3.2? I've downloaded version 7.3.4, but not installed yet. >>sort_mem = 131072 > This sort_mem value is *very* large - that's 131MB for *each sort* that gets > done. I'd suggest trying something in the range 1,000-10,000. What's probably > happening with the error above is that PG is allocating ridiculous amounts of > memory, the machines going into swap and everything eventually grinds to a > halt. What mean "each sort"? Each query with SORT clause or some internal (invisible to user) sorts too (I can't imagine: indexed search or whatever else)? I'm reduced sort_mem to 16M. >>fsync = false > I'd turn fsync back on - unless you don't mind losing your data after a crash. This is temporary performance solution - I want get SELECT query result first, but current performance is too low. >>enable_seqscan = false > Don't tinker with these in a live system, they're only really for > testing/debugging. This is another strange behavior of PostgreSQL - he don't use some created indexes (seq_scan only) after ANALYZE too. OK, I'm turned on this option back. >>effective_cache_size = 65536 > So you typically get about 256MB cache usage in top/free? No, top shows 12-20Mb. I'm reduced effective_cache_size to 4K blocks (16M?). >> SELECT statement is: >> >>SELECT showcalc('B00204', dd, r020, t071) AS s04 >>FROM v_file02wide >>WHERE a011 = 3 >> AND inrepdate(data) >> AND SUBSTR(ncks, 2, 2) IN ('NL', 'NM') >> AND r030 = 980; > Hmm - mostly views and function calls, OK - I'll read on. My data are distributed accross multiple tables to integrity and avoid redundancy. During SELECT query these data rejoined to be presented in "human-readable" form. :) "SUBSTR" returns about 25 records, I'm too lazy for write 25 numbers. :) I'm also worried for errors. >>(cost=174200202474.99..174200202474.99 rows=1 width=143) -> Hash Join > ^^^^^^^ > This is a BIG cost estimate and you've got lots more like them. I'm guessing > it's because of the sort_mem / enable_seqscan settings you have. The numbers > don't make sense to me - it sounds like you've pushed the cost estimator into > a very strange corner. I think that cost estimator "pushed into very strange corner" by himself. >> Function showcalc definition is: >>CREATE OR REPLACE FUNCTION showcalc(VARCHAR(10), VARCHAR(2), VARCHAR(4), >>NUMERIC(16)) RETURNS NUMERIC(16) >>LANGUAGE SQL AS ' >>-- Parameters: code, dd, r020, t071 >> SELECT COALESCE( >> (SELECT sc.koef * $4 >> FROM showing AS s NATURAL JOIN showcomp AS sc >> WHERE s.kod LIKE $1 >> AND NOT SUBSTR(acc_mask, 1, 1) LIKE ''['' >> AND SUBSTR(acc_mask, 1, 4) LIKE $3 >> AND SUBSTR(acc_mask, 5, 1) LIKE SUBSTR($2, 1, 1)), > Obviously, you could use = for these 3 rather than LIKE ^^^ > Same below too. >> (SELECT SUM(sc.koef * COALESCE(showcalc(SUBSTR(acc_mask, 2, >>LENGTH(acc_mask) - 2), $2, $3, $4), 0)) >> FROM showing AS s NATURAL JOIN showcomp AS sc >> WHERE s.kod LIKE $1 >> AND SUBSTR(acc_mask, 1, 1) LIKE ''[''), >> 0) AS showing; >>'; OK, all unnecessary "LIKEs" replaced by "=", JOIN removed too: CREATE OR REPLACE FUNCTION showcalc(VARCHAR(10), VARCHAR(2), VARCHAR(4), NUMERIC(16)) RETURNS NUMERIC(16) LANGUAGE SQL AS ' -- Parameters: code, dd, r020, t071 SELECT COALESCE( (SELECT sc.koef * $4 FROM showing AS s, showcomp AS sc WHERE sc.kod = s.kod AND s.kod LIKE $1 AND NOT SUBSTR(acc_mask, 1, 1) = ''['' AND SUBSTR(acc_mask, 1, 4) = $3 AND SUBSTR(acc_mask, 5, 1) = SUBSTR($2, 1, 1)), (SELECT SUM(sc.koef * COALESCE(showcalc(SUBSTR(acc_mask, 2, LENGTH(acc_mask) - 2), $2, $3, $4), 0)) FROM showing AS s, showcomp AS sc WHERE sc.kod = s.kod AND s.kod = $1 AND SUBSTR(acc_mask, 1, 1) = ''[''), 0) AS showing; '; >> View v_file02wide is: >>CREATE VIEW v_file02wide AS >>SELECT id_a011 AS a011, data, obl.ko, obl.nazva AS oblast, b030, >>banx.box AS ncks, banx.nazva AS bank, >> epr.dd, r020, r030, a3, r030.nazva AS valuta, k041, >> -- Sum equivalent in national currency >> t071 * get_kurs(id_r030, data) AS t070, >> t071 >>FROM v_file02 AS vf02 >> JOIN kod_obl AS obl USING(id_obl) >> JOIN (dov_bank NATURAL JOIN dov_tvbv) AS banx >> ON banx.id_v = vf02.id_v >> AND data BETWEEN COALESCE(banx.dataa, data) >> AND COALESCE(banx.datab, data) >> JOIN ek_pok_r AS epr USING(id_dd) >> JOIN kl_r020 USING(id_r020) >> JOIN kl_r030 AS r030 USING(id_r030) >> JOIN kl_k041 USING(id_k041); > You might want to rewrite the view so it doesn't use explicit JOIN statements, > i.e FROM a,b WHERE a.id=b.ref rather than FROM a JOIN b ON id=ref > At the moment, this will force PG into making the joins in the order you write > them (I think this is changed in v7.4) I think this is a important remark. Can "JOIN" significantly reduce performance of SELECT statement relative to ", WHERE"? OK, I'm changed VIEW to this text: CREATE VIEW v_file02 AS SELECT filenum, data, id_a011, id_v, id_obl, id_dd, id_r020, id_r030, id_k041, t071 FROM filexxr, file02 WHERE file02.id_r = filexxr.id_r; CREATE VIEW v_file02wide AS SELECT id_a011 AS a011, data, obl.ko, obl.nazva AS oblast, b030, banx.box AS ncks, banx.nazva AS bank, epr.dd, r020, vf02.id_r030 AS r030, a3, kl_r030.nazva AS valuta, k041, -- Sum equivalent in national currency t071 * get_kurs(vf02.id_r030, data) AS t070, t071 FROM v_file02 AS vf02, kod_obl AS obl, v_banx AS banx, ek_pok_r AS epr, kl_r020, kl_r030, kl_k041 WHERE obl.id_obl = vf02.id_obl AND banx.id_v = vf02.id_v AND data BETWEEN COALESCE(banx.dataa, data) AND COALESCE(banx.datab, data) AND epr.id_dd = vf02.id_dd AND kl_r020.id_r020 = vf02.id_r020 AND kl_r030.id_r030 = vf02.id_r030 AND kl_k041.id_k041 = vf02.id_k041; Now (with configuration and view definition changed) "SELECT COUNT(*) FROM v_file02wide;" executes about 6 minutes and 45 seconds instead of 30 seconds (previous). Another annoying "feature" is impossibility writing "SELECT * FROM..." - duplicate column names error. In NATURAL JOIN joined columns hiding automatically. :-| >> Function inrepdate is: >>CREATE OR REPLACE FUNCTION inrepdate(DATE) RETURNS BOOL >>LANGUAGE SQL AS ' >> -- Returns true if given date is in repdate >> SELECT (SELECT COUNT(*) FROM repdate >> WHERE $1 BETWEEN COALESCE(data1, CURRENT_DATE) >> AND COALESCE(data2, CURRENT_DATE)) >> >> > 0; > You can probably replace this with: > SELECT true FROM repdate WHERE $1 ... > You'll need to look at where it's used though. Hmm... table repdate contain date intervals. For example: data1 data2 2003-01-01 2003-01-10 2003-05-07 2003-05-24 ... I need single value (true or false) about given date as parameter - report includes given date or not. COUNT used as aggregate function for this. Can you write this function more simpler? BTW, I prefer SQL language if possible, then PL/pgSQL. This may be mistake? >> Table has indexes almost for all selected fields. > That's not going to help you for the SUBSTR(...) stuff, although you could use > functional indexes (see manuals/list archives for details). Yes, I'm using functional indexes, but not in this case... now in this case too! :) > First thing is to get those two configuration settings somewhere sane, then we > can tune properly. You might like the document at: > http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html Thanks, it's interesting. Current query plan: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=188411.98..188411.98 rows=1 width=151) -> Hash Join (cost=186572.19..188398.39 rows=5437 width=151) Hash Cond: ("outer".id_obl = "inner".id_obl) -> Hash Join (cost=186570.65..188301.70 rows=5437 width=147) Hash Cond: ("outer".id_dd = "inner".id_dd) -> Hash Join (cost=186569.45..188205.34 rows=5437 width=137) Hash Cond: ("outer".id_k041 = "inner".id_k041) -> Hash Join (cost=186568.40..188109.14 rows=5437 width=133) Hash Cond: ("outer".id_r020 = "inner".id_r020) -> Hash Join (cost=186499.15..187944.74 rows=5437 width=122) Hash Cond: ("outer".id_r030 = "inner".id_r030) -> Merge Join (cost=186493.55..187843.99 rows=5437 width=118) Merge Cond: ("outer".id_v = "inner".id_v) Join Filter: (("outer".data >= CASE WHEN ("inner".dataa IS NOT NULL) THEN "inner".dataa WHEN ("outer".data IS NOT NULL) THEN "outer".data ELSE NULL::date END) AND ("outer".data <= CASE WHEN ("inner".datab IS NOT NULL) THEN "inner".datab WHEN ("outer".data IS NOT NULL) THEN "outer".data ELSE NULL::date END)) -> Sort (cost=29324.30..29568.97 rows=97870 width=61) Sort Key: filexxr.id_v -> Hash Join (cost=632.67..21211.53 rows=97870 width=61) Hash Cond: ("outer".id_r = "inner".id_r) -> Seq Scan on file02 (cost=0.00..16888.16 rows=493464 width=32) Filter: (id_r030 = 980) -> Hash (cost=615.41..615.41 rows=6903 width=29) -> Index Scan using index_filexxr_a011 on filexxr (cost=0.00..615.41 rows=6903 width=29) Index Cond: (id_a011 = 3) Filter: inrepdate(data) -> Sort (cost=157169.25..157172.17 rows=1167 width=57) Sort Key: v.id_v -> Hash Join (cost=1.18..157109.80 rows=1167 width=57) Hash Cond: ("outer".id_oz = "inner".id_oz) -> Merge Join (cost=0.00..157088.20 rows=1167 width=53) Merge Cond: ("outer".id_bnk = "inner".id_bnk) -> Index Scan using dov_bank_pkey on dov_bank b (cost=0.00..261328.45 rows=1450 width=17) Filter: (subplan) SubPlan -> Materialize (cost=90.02..90.02 rows=29 width=11) -> Seq Scan on dov_bank (cost=0.00..90.02 rows=29 width=11) Filter: ((dov_bank_box_22(box) = 'NL'::character varying) OR (dov_bank_box_22(box) = 'NM'::character varying)) -> Index Scan using index_dov_tvbv_bnk on dov_tvbv v (cost=0.00..142.42 rows=2334 width=36) -> Hash (cost=1.14..1.14 rows=14 width=4) -> Seq Scan on ozkb o (cost=0.00..1.14 rows=14 width=4) -> Hash (cost=5.08..5.08 rows=208 width=4) -> Seq Scan on kl_r030 (cost=0.00..5.08 rows=208 width=4) -> Hash (cost=64.00..64.00 rows=2100 width=11) -> Seq Scan on kl_r020 (cost=0.00..64.00 rows=2100 width=11) -> Hash (cost=1.04..1.04 rows=4 width=4) -> Seq Scan on kl_k041 (cost=0.00..1.04 rows=4 width=4) -> Hash (cost=1.16..1.16 rows=16 width=10) -> Seq Scan on ek_pok_r epr (cost=0.00..1.16 rows=16 width=10) -> Hash (cost=1.43..1.43 rows=43 width=4) -> Seq Scan on kod_obl obl (cost=0.00..1.43 rows=43 width=4) (49 rows) Now (2K shared_buffers blocks, 16K effective_cache_size blocks, 16Mb sort_mem) PostgreSQL uses much less memory, about 64M... it's not good, I want using all available RAM if possible - PostgreSQL is the main task on this PC. May set effective_cache_size to 192M (48K blocks) be better? I don't understand exactly: effective_cache_size tells PostgreSQL about OS cache size or about available free RAM? With best regards Yaroslav Mazurak. --------------ms000008090800050008060007 Content-Type: application/x-pkcs7-signature; name="smime.p7s" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="smime.p7s" Content-Description: S/MIME Cryptographic Signature MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEH AQAAoIIGUjCCAxIwggJ7oAMCAQICAwmhhDANBgkqhkiG9w0BAQQFADCBkjEL MAkGA1UEBhMCWkExFTATBgNVBAgTDFdlc3Rlcm4gQ2FwZTESMBAGA1UEBxMJ Q2FwZSBUb3duMQ8wDQYDVQQKEwZUaGF3dGUxHTAbBgNVBAsTFENlcnRpZmlj YXRlIFNlcnZpY2VzMSgwJgYDVQQDEx9QZXJzb25hbCBGcmVlbWFpbCBSU0Eg MjAwMC44LjMwMB4XDTAzMDQwMTA5MDYxNVoXDTA0MDMzMTA5MDYxNVowTDEf MB0GA1UEAxMWVGhhd3RlIEZyZWVtYWlsIE1lbWJlcjEpMCcGCSqGSIb3DQEJ ARYaeWFtYXp1cmFrQGx2aXYuYmFuay5nb3YudWEwggEiMA0GCSqGSIb3DQEB AQUAA4IBDwAwggEKAoIBAQC2KQYjqR5t4kSI8h5CVPC8s+NNE80YXKNUCosu VbjaKPm1/M/QzzP+jqfPqI0fSsS9LZWF3ypuDtduU+gCnuOktDl8ygOohCNk eXXX6DTCMAoO6VpYf3/QqEB1MMa9O90sZrYfL1PTwNQ8KDddfEUuIO1pznRl JCUzSUpgzNH3CHu9z0zwUfOeGNmzTgzVgY5N8QUU9UouKFfTVHEJWtAr+/ZU 96RIEMNp+E8N3uMXoKdXRmGuhqMW9gHq9a4lZIk7wsCoKSyfVWCk2w515Z0X 2zN2ITTFtvAxzBLZDbSSJU6FnPQzDgD2Der0aDd2inxu646HM85qdVnv+MXg Y1wtAgMBAAGjNzA1MCUGA1UdEQQeMByBGnlhbWF6dXJha0Bsdml2LmJhbmsu Z292LnVhMAwGA1UdEwEB/wQCMAAwDQYJKoZIhvcNAQEEBQADgYEAK+ak7q4u l9palI2Lq6jZ+5/ljdeWhU0oI+Utu1MfaZQzthKfbQ/CYMJDf7x8cTIbRrqT L5axjDikuzcXwGe0H0O8Jlm68pgjcOOEZNs/PXE7Pd9A7D+Z46qij7Js0RXa 39FSYYxnLwF+/u2eb8v1ORvyQULWJMzNSZ7c8XKaOj0wggM4MIICoaADAgEC AhBmRXK3zHT1z2N2RYTQLpEBMA0GCSqGSIb3DQEBBAUAMIHRMQswCQYDVQQG EwJaQTEVMBMGA1UECBMMV2VzdGVybiBDYXBlMRIwEAYDVQQHEwlDYXBlIFRv d24xGjAYBgNVBAoTEVRoYXd0ZSBDb25zdWx0aW5nMSgwJgYDVQQLEx9DZXJ0 aWZpY2F0aW9uIFNlcnZpY2VzIERpdmlzaW9uMSQwIgYDVQQDExtUaGF3dGUg UGVyc29uYWwgRnJlZW1haWwgQ0ExKzApBgkqhkiG9w0BCQEWHHBlcnNvbmFs LWZyZWVtYWlsQHRoYXd0ZS5jb20wHhcNMDAwODMwMDAwMDAwWhcNMDQwODI3 MjM1OTU5WjCBkjELMAkGA1UEBhMCWkExFTATBgNVBAgTDFdlc3Rlcm4gQ2Fw ZTESMBAGA1UEBxMJQ2FwZSBUb3duMQ8wDQYDVQQKEwZUaGF3dGUxHTAbBgNV BAsTFENlcnRpZmljYXRlIFNlcnZpY2VzMSgwJgYDVQQDEx9QZXJzb25hbCBG cmVlbWFpbCBSU0EgMjAwMC44LjMwMIGfMA0GCSqGSIb3DQEBAQUAA4GNADCB iQKBgQDeMzKmY8cJJUU+0m54J2eBxdqIGYKXDuNEKYpjNSptcDz63K737nRv MLwzkH/5NHGgo22Y8cNPomXbDfpL8dbdYaX5hc1VmjUanZJ1qCeu2HL5ugL2 17CR3hzpq+AYA6h8Q0JQUYeDPPA5tJtUihOH/7ObnUlmAC0JieyUa+mhaQID AQABo04wTDApBgNVHREEIjAgpB4wHDEaMBgGA1UEAxMRUHJpdmF0ZUxhYmVs MS0yOTcwEgYDVR0TAQH/BAgwBgEB/wIBADALBgNVHQ8EBAMCAQYwDQYJKoZI hvcNAQEEBQADgYEAMbFLR135AXHl9VNsXXnWPZjAJhNigSKnEvgilegbSbcn ewQ5uvzm8iTrkfq97A0qOPdQVahs9w2tTBu8A/S166JHn2yiDFiNMUIJEWyw GmnRKxKyQF1q+XnQ6i4l3Yrk/NsNH50C81rbyjz2ROomaYd/SJ7OpZ/nhNjJ YmKtBcYxggJ2MIICcgIBATCBmjCBkjELMAkGA1UEBhMCWkExFTATBgNVBAgT DFdlc3Rlcm4gQ2FwZTESMBAGA1UEBxMJQ2FwZSBUb3duMQ8wDQYDVQQKEwZU aGF3dGUxHTAbBgNVBAsTFENlcnRpZmljYXRlIFNlcnZpY2VzMSgwJgYDVQQD Ex9QZXJzb25hbCBGcmVlbWFpbCBSU0EgMjAwMC44LjMwAgMJoYQwCQYFKw4D AhoFAKCBsTAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJ BTEPFw0wMzA4MDYxMjQyNTVaMCMGCSqGSIb3DQEJBDEWBBRzpeGWs9A5dsXs o0yJWonTue5xWDBSBgkqhkiG9w0BCQ8xRTBDMAoGCCqGSIb3DQMHMA4GCCqG SIb3DQMCAgIAgDANBggqhkiG9w0DAgIBQDAHBgUrDgMCBzANBggqhkiG9w0D AgIBKDANBgkqhkiG9w0BAQEFAASCAQBQPuzOte9gd9BXiI8gPdiDfywbWvfa NuhlbTESyB0yvFl0jq5/uZgTaTdtH48OfSOG2DymB6kbPanQEhaKgcYKrw/V xhsacZQ/LJxg4ANVz9+eJvKpiEmXvGcKVCS8NHx2Y76Im3Dz5VZ3wp+ZIEFf 3+j1A+nDr1sXE0n8uYHpZV/Lq2au3DmlIdajgbVfym7qe6DSCaeL9aSFH9a6 QOP188f47EUPaoa8e2LEn1cjrBxqSlt8KbSLqAntS9jbePkPb3takp16HlwG PJqIOa8Ysg/DSumUAEmIEAH+qjeEELnO8i785f5Or9Gs+3Gy5oYXkiXysGr5 5siBXhonYzU8AAAAAAAA --------------ms000008090800050008060007--