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 <t...@sss.pgh.pa.us> wrote: > Marcus Engene <meng...@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