Re: Performance Problem with postgresql 9.03, 8GB RAM,Quadcore Processor Server--Need help!!!!!!!

Dear All

Thanks for your suggestions & replies.

The below are the sample query which i put for particular one bill_id

EXPLAIN ANALYZE SELECT abd.bill_no as
bill_no,to_char(abd.bill_date,'dd/mm/yyyy') AS date,mp.product_desc as
product_desc,std.quantity,std.area,rip.price AS rate
FROM acc_bill_items_106 abi
JOIN acc_bill_details_106 abd ON abd.bill_id=abi.bill_id
JOIN stk_source ss ON ss.source_detail[1]=1 and
ss.source_detail[2]=abi.item_id
JOIN stock_transaction_detail_106 std ON std.stock_id=ss.stock_id
JOIN stock_details_106 sd106 ON sd106.stock_id=std.stock_id
JOIN master_product_106 mp ON mp.product_id= sd106.product_id
JOIN receipt_item_price_106 rip ON rip.receipt_item_id=abi.item_id
WHERE abi.bill_id=12680;

QUERY
PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..7230339.59 rows=54369 width=39) (actual
time=158156.895..158157.206 rows=1 loops=1)
-> Nested Loop (cost=0.00..7149579.10 rows=8192 width=32) (actual
time=158156.863..158157.172 rows=1 loops=1)
-> Nested Loop (cost=0.00..7119922.60 rows=8192 width=27)
(actual time=158156.855..158157.164 rows=1 loops=1)
-> Nested Loop (cost=0.00..7086865.70 rows=8192 width=19)
(actual time=158156.835..158157.143 rows=1 loops=1)
Join Filter: (abi.item_id = ss.source_detail[2])
-> Nested Loop (cost=0.00..604.54 rows=2 width=23)
(actual time=2.782..2.786 rows=1 loops=1)
-> Index Scan using acc_bill_details_106_pkey
on acc_bill_details_106 abd (cost=0.00..6.29 rows=1 width=12) (actual
time=0.010..0.012 rows=1 loops=1)
Index Cond: (bill_id = 12680)
-> Nested Loop (cost=0.00..598.19 rows=2
width=19) (actual time=2.770..2.772 rows=1 loops=1)
Join Filter: (abi.item_id =
rip.receipt_item_id)
-> Seq Scan on receipt_item_price_106
rip (cost=0.00..162.48 rows=4216 width=11) (actual time=0.005..0.562
rows=4218 loops=1)
-> Materialize (cost=0.00..140.59 rows=2
width=8) (actual time=0.000..0.000 rows=1 loops=4218)
-> Seq Scan on acc_bill_items_106
abi (cost=0.00..140.58 rows=2 width=8) (actual time=0.412..0.412 rows=1
loops=1)
Filter: (bill_id = 12680)
-> Materialize (cost=0.00..7024562.68 rows=819222
width=33) (actual time=0.035..153869.575 rows=19010943 loops=1)
-> Append (cost=0.00..7014065.57 rows=819222
width=33) (actual time=0.034..145403.828 rows=19010943 loops=1)
-> Seq Scan on stk_source ss
(cost=0.00..45.10 rows=5 width=36) (actual time=0.001..0.001 rows=0 loops=1)
Filter: (source_detail[1] = 1)
-> Seq Scan on stk_source ss
(cost=0.00..22226.32 rows=2596 width=33) (actual time=0.033..118.019
rows=66356 loops=1)
Filter: (source_detail[1] = 1)
-> Seq Scan on stk_source ss
(cost=0.00..90405.31 rows=10559 width=33) (actual time=0.010..490.712
rows=288779 loops=1)
Filter: (source_detail[1] = 1)
-> Seq Scan on stk_source ss
(cost=0.00..6901388.84 rows=806062 width=33) (actual
time=13.382..142493.302 rows=18655808 loops=1)
Filter: (source_detail[1] = 1)
-> Index Scan using sd106_stock_id_idx on stock_details_106
sd106 (cost=0.00..4.00 rows=1 width=8) (actual time=0.014..0.014 rows=1
loops=1)
Index Cond: (sd106.stock_id = ss.stock_id)
-> Index Scan using master_product_pkey on master_product_106 mp
(cost=0.00..3.59 rows=1 width=13) (actual time=0.006..0.006 rows=1 loops=1)
Index Cond: (mp.product_id = sd106.product_id)
-> Index Scan using std106_stock_id_idx on stock_transaction_detail_106
std (cost=0.00..9.70 rows=4 width=19) (actual time=0.007..0.009 rows=1
loops=1)
Index Cond: (std.stock_id = ss.stock_id)
Total runtime: 158240.795 ms

<http://goog_1591150719>*http://explain.depesz.com/s/Tyc

*Similarly i have used the queries on various details pages and views that
too if i go for one month transactions its taking so much times.

I will try to upgrade to latest version and will try to tune more my
queries so changing the conf settings wouldn't help for better performance??

Thanks & Regards
Hashim

On Tue, Nov 1, 2011 at 7:13 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Marcus Engene <mengpg2(at)engene(dot)se> writes:
> > After I upgraded from Postgres 8.3/8.4 to 9.0 I had all sorts of
> > problems with queries with many joins. Queries that used to take 1ms
> > suddenly take half a minute for no apparent reason.
>
> Could we see a concrete test case, rather than hand waving? If there's
> really a problem in 9.0, it's impossible to fix it on so little detail.
>
> > One workaround I've done is if something looking like this....
>
> The only way that should make a difference is if the total number
> of tables in the query exceeds from_collapse_limit (or maybe
> join_collapse_limit, depending on exactly how you wrote the query).
> Perhaps you'd been running with nonstandard values of those settings
> in 8.x, and forgot to transfer them into the new DB?
>
> regards, tom lane
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

--
Regards
Mohamed Hashim.N
Mobile:09894587678