X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id DEA5CEFFB0 for ; Fri, 6 Jun 2003 18:53:30 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 68521-08 for ; Fri, 6 Jun 2003 18:53:30 +0000 (GMT) Received: from imo-r01.mx.aol.com (imo-r01.mx.aol.com [152.163.225.97]) by svr1.postgresql.org (Postfix) with ESMTP id A45B7EFF9B for ; Fri, 6 Jun 2003 18:53:29 +0000 (GMT) Received: from yusuf0478@netscape.net by imo-r01.mx.aol.com (mail_out_v36.3.) id k.3.88f5d7b (16216) for ; Fri, 6 Jun 2003 14:53:25 -0400 (EDT) Received: from netscape.net (66-163-11-108.ip.tor.radiant.net [66.163.11.108]) by air-in01.mx.aol.com (v94.27) with ESMTP id MAILININ14-3f583ee0e32419a; Fri, 06 Jun 2003 14:53:24 -0400 Message-ID: <3EE0E322.50201@netscape.net> Date: Fri, 06 Jun 2003 14:53:22 -0400 From: Yusuf User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.0.2) Gecko/20030208 Netscape/7.02 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance Subject: [Fwd: Re: Enabling and disabling run time configuration parameters.] Content-Type: multipart/mixed; boundary="------------030801010800060509010101" X-Mailer: Unknown (No Version) X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=FROM_ENDS_IN_NUMS, FWD_MSG, USER_AGENT_MOZILLA_UA, X_ACCEPT_LANG X-Spam-Level: X-Archive-Number: 200306/24 X-Sequence-Number: 2108 --------------030801010800060509010101 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit --------- THE QUERY ---------------- select sum(item.charge) as currentCharges , sum(item.gst) as gst , sum(item.pst) as pst , sum(item.hst) as hst , sum(item.qst) as qst , sum(item.federaltax) as federalTax , sum(item.statetax) as stateTax , sum(item.localtax) as localTax , sum(item.othertax) as otherTax , consaccount.latePaymentCharge as latePaymentCharges , consaccount.PreviousBalance as balanceForward , consaccount.dateinserted as dateInserted , consaccount.userDateInserted as dateEntered , consaccount.issueDate as invoiceDate , consaccount.dueDate as dateDue , consaccount.consAccount_Id as consolidatedAccountId , consaccount.invoiceNumber as invoiceNumber , consaccountinfo.name as consolidatedAccountNumber , consaccount.vendor_Id as vendorId , consaccount.client_Id as clientId , consaccount.ponumber as ponumber , consaccount.ismanualentry as isManualEntry , consaccount_approvedby_user.approvedby_user_id as approved , consaccount_allocatedby_user.allocatedby_user_id as allocated , consaccount_paidby_user.paidby_user_id as paid , consaccountinfo.consaccountinfo_id as consAccountInfoId , consaccount_paidby_user.amountpaid as amountPaid from consaccount inner join consaccountinfo on consaccount.consAccountInfo_Id = consaccountinfo.ConsAccountInfo_Id left join consaccount_allocatedby_user on consaccount.consaccount_id = consaccount_allocatedby_user.consaccount_id left join consaccount_approvedby_user on consaccount.consaccount_id = consaccount_approvedby_user.consaccount_id left join consaccount_paidby_user on consaccount.consaccount_id = consaccount_paidby_user.consaccount_id inner join account on consaccount.consAccount_Id = account.ConsAccount_Id inner join phone on account.account_Id = phone.Account_Id inner join item on phone.phone_Id = item.Phone_Id where consaccount.consaccount_id in (36,37,38,40,41,42,43,44,45,48,16,49,50,15,14) group by consaccountinfo.name , consaccountinfo.consaccountinfo_id , consaccount.invoicenumber , consaccount.consaccount_id , consaccount.dateinserted , consaccount.userDateInserted , consaccount.duedate , consaccount.issuedate , consaccount.previousbalance , consaccount.latepaymentcharge , consaccount.vendor_id , consaccount.client_id , consaccount.ponumber , consaccount.ismanualentry , consaccount_approvedby_user.approvedby_user_id , consaccount_allocatedby_user.allocatedby_user_id , consaccount_paidby_user.paidby_user_id , consaccount.isManualEntry , consaccount_paidby_user.amountpaid order by consaccount.invoicenumber asc; ----------- THE QUERY PLAN ----------- QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=5938.90..5939.30 rows=161 width=256) (actual time=45187.21..45187.30 rows=15 loops=1) Sort Key: consaccount.invoicenumber -> Aggregate (cost=5820.52..5933.01 rows=161 width=256) (actual time=42859.84..45186.91 rows=15 loops=1) -> Group (cost=5820.52..5896.85 rows=1607 width=256) (actual time=42729.90..44465.25 rows=39078 loops=1) -> Sort (cost=5820.52..5824.54 rows=1607 width=256) (actual time=42729.85..43018.41 rows=39078 loops=1) Sort Key: consaccountinfo.name, consaccountinfo.consaccountinfo_id, consaccount.invoicenumber, consaccount.consaccount_id, consaccount.dateinserted, consaccount.userdateinserted, consaccount.duedate, consaccount.issuedate, consaccount.previousbalance, consaccount.latepaymentcharge, consaccount.vendor_id, consaccount.client_id, consaccount.ponumber, consaccount.ismanualentry, consaccount_approvedby_user.approvedby_user_id, consaccount_allocatedby_user.allocatedby_user_id, consaccount_paidby_user.paidby_user_id, consaccount_paidby_user.amountpaid -> Hash Join (cost=3208.20..5734.94 rows=1607 width=256) (actual time=7787.49..38027.69 rows=39078 loops=1) Hash Cond: ("outer".phone_id = "inner".phone_id) -> Seq Scan on item (cost=0.00..2140.77 rows=73177 width=95) (actual time=0.07..977.20 rows=73177 loops=1) -> Hash (cost=3200.10..3200.10 rows=3239 width=161) (actual time=7785.54..7785.54 rows=0 loops=1) -> Hash Join (cost=149.32..3200.10 rows=3239 width=161) (actual time=156.50..6589.78 rows=139977 loops=1) Hash Cond: ("outer".account_id = "inner".account_id) -> Seq Scan on phone (cost=0.00..2272.86 rows=147486 width=8) (actual time=0.12..1211.95 rows=147486 loops=1) -> Hash (cost=149.07..149.07 rows=103 width=153) (actual time=156.29..156.29 rows=0 loops=1) -> Hash Join (cost=51.60..149.07 rows=103 width=153) (actual time=13.62..128.92 rows=3412 loops=1) Hash Cond: ("outer".consaccount_id = "inner".consaccount_id) -> Seq Scan on account (cost=0.00..72.79 rows=4679 width=8) (actual time=0.02..36.21 rows=4679 loops=1) -> Hash (cost=51.56..51.56 rows=15 width=145) (actual time=7.27..7.27 rows=0 loops=1) -> Hash Join (cost=44.42..51.56 rows=15 width=145) (actual time=5.80..7.15 rows=15 loops=1) Hash Cond: ("outer".consaccount_id = "inner".consaccount_id) -> Hash Join (cost=44.41..51.48 rows=15 width=117) (actual time=5.71..6.76 rows=15 loops=1) Hash Cond: ("outer".consaccount_id = "inner".consaccount_id) -> Hash Join (cost=44.41..51.41 rows=15 width=109) (actual time=5.60..6.35 rows=15 loops=1) Hash Cond: ("outer".consaccount_id = "inner".consaccount_id) -> Merge Join (cost=43.40..50.32 rows=15 width=101) (actual time=5.37..5.82 rows=15 loops=1) Merge Cond: ("outer".consaccountinfo_id = "inner".consaccountinfo_id) -> Index Scan using consaccountinfo_pkey on consaccountinfo (cost=0.00..6.17 rows=197 width=18) (actual time=0.20..0.27 rows=7 loops=1) -> Sort (cost=43.40..43.44 rows=15 width=83) (actual time=5.06..5.15 rows=15 loops=1) Sort Key: consaccount.consaccountinfo_id -> Seq Scan on consaccount (cost=0.00..43.11 rows=15 width=83) (actual time=0.09..4.87 rows=15 loops=1) Filter: ((consaccount_id = 36) OR (consaccount_id = 37) OR (consaccount_id = 38) OR (consaccount_id = 40) OR (consaccount_id = 41) OR (consaccount_id = 42) OR (consaccount_id = 43) OR (consaccount_id = 44) OR (consaccount_id = 45) OR (consaccount_id = 48) OR (consaccount_id = 16) OR (consaccount_id = 49) OR (consaccount_id = 50) OR (consaccount_id = 15) OR (consaccount_id = 14)) -> Hash (cost=1.01..1.01 rows=1 width=8) (actual time=0.13..0.13 rows=0 loops=1) -> Seq Scan on consaccount_allocatedby_user (cost=0.00..1.01 rows=1 width=8) (actual time=0.09..0.10 rows=1 loops=1) -> Hash (cost=0.00..0.00 rows=1 width=8) (actual time=0.02..0.02 rows=0 loops=1) -> Seq Scan on consaccount_approvedby_user (cost=0.00..0.00 rows=1 width=8) (actual time=0.01..0.01 rows=0 loops=1) -> Hash (cost=0.00..0.00 rows=1 width=28) (actual time=0.02..0.02 rows=0 loops=1) -> Seq Scan on consaccount_paidby_user (cost=0.00..0.00 rows=1 width=28) (actual time=0.01..0.01 rows=0 loops=1) Total runtime: 45189.45 msec (38 rows) The total time when hashjoin=off and mergejoin=off is ~ 13.2 seconds ----------- ABOUT MY MACHINE ----------- The size of the database when I check PGDATA\base is about 400 MB FreeBSD Mem: 26M Active, 1695M Inact, 155M Wired, 52M Cache, 199M Buf, 82M Free Swap: 4080M Total, 8K Used, 4080M Free ----------- MY POSTGRES CONFIGURATION ----------- cpu_index_tuple_cost | 0.001 cpu_operator_cost | 0.0025 cpu_tuple_cost | 0.01 effective_cache_size | 1000 enable_hashjoin | on enable_indexscan | on enable_mergejoin | on enable_nestloop | on enable_seqscan | on enable_sort | on max_connections | 40 shared_buffers | 500 sort_mem | 1024 random_page_cost | 4 What should I set the config parameters to be, to improve performance? I've attached my schema. --------------030801010800060509010101 Content-Type: text/plain; name="schema.txt" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="schema.txt" -- TABLES -- CONSACCOUNTINFO { consaccountinfo_id serial primary key, description } - has about 200 records CONSACCOUNT { consaccount_id serial primary key, consaccountinfo_id integer references Consaccountinfo(consaccountinfo_id), } - 700 records ACCOUNT { account_id serial primary key, consaccount_id integer references Consaccount(consaccount_id), ... } - 4700 records PHONE { phone_id serial primary key, account_id integer references account(account_id), ... } - 150 000 records - index on account_id ITEM { item_id serial primary key phone_id integer references phone(phone_id), ... } - 70 000 records - index on phone_id CONSACCOUNT_ALLOCATED { consaccount_id integer references Consaccount(consaccount_id), ... } - 1 record CONSACCOUNT_APPROVED{ consaccount_id integer references Consaccount(consaccount_id), ... } - 2 records CONSACCOUNT_PAID{ consaccount_id integer references Consaccount(consaccount_id), ... } - 2 records --------------030801010800060509010101--