Am 02.11.2011 08:12, schrieb Mohamed Hashim:
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;
First I would try this:
explain analyze select * from stk_source where source_detail[1] = 1;
explain analyze select * from stk_source where source_detail[2] =
12356;
Both times you'll get sequential scans, and that's the root of the
problem. Oh, you mentioned that you use partitioning, but there
seems to be no condition for that.
You should really rethink your database schema, at least try to pull
out all indexable fields out of that int[] into columns, and use
indices on those fields.
Regards
Mario
|
