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@xxxxxxxxxxxxx> wrote:
Marcus Engene <mengpg2@xxxxxxxxx> writes:Could we see a concrete test case, rather than hand waving? If there's
> 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.
really a problem in 9.0, it's impossible to fix it on so little detail.
The only way that should make a difference is if the total number
> One workaround I've done is if something looking like this....
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
--
Regards
Mohamed Hashim.N
Mobile:09894587678
