On 3 Listopad 2011, 16:02, Mario Weilguni wrote:
> 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.
No doubt about that, querying tables using conditions on array columns is
not the best direction in most cases, especially when those tables are
huge.
Still, the interesting part here is that the OP claims this worked just
fine in the older version and after an upgrade the performance suddenly
dropped. This could be caused by many things, and we're just guessing
because we don't have any plans from the old version.
Tomas