Postgres Pro
Downloads
Home   >   mailing lists

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

From Mohamed Hashim
Subject Re: Performance Problem with postgresql 9.03, 8GB RAM,Quadcore Processor Server--Need help!!!!!!!
Date
Msg-id CACBfhZNoq_hjZyMnBTnrxR8rif31QvJ5dtdgQMdPwO0+x2AKpQ@mail.gmail.com
Whole thread Raw
In response to Re: Performance Problem with postgresql 9.03, 8GB RAM,Quadcore Processor Server--Need help!!!!!!!  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Performance Problem with postgresql 9.03, 8GB RAM,Quadcore Processor Server--Need help!!!!!!!  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Performance Problem with postgresql 9.03, 8GB RAM,Quadcore Processor Server--Need help!!!!!!!  (Mario Weilguni <roadrunner6@gmx.at>)
List pgsql-performance
Tree view
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://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@sss.pgh.pa.us> wrote:
Marcus Engene <mengpg2@engene.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



--
Regards
Mohamed Hashim.N
Mobile:09894587678

pgsql-performance by date:

Previous
From: Craig Ringer
Date:
Subject: Re: procedure takes much more time than its query statement
Next
From: "Sabin Coanda"
Date:
Subject: Re: procedure takes much more time than its query statement