[Fwd: Re: Enabling and disabling run time configuration parameters.] - Mailing list pgsql-performance

---------  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.













-- 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