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

Поиск
Список
Период
Сортировка
От Mario Weilguni
Тема Re: Performance Problem with postgresql 9.03, 8GB RAM,Quadcore Processor Server--Need help!!!!!!!
Дата
Msg-id 4EB2ACEF.8090305@gmx.at
обсуждение исходный текст
Ответ на Re: Performance Problem with postgresql 9.03, 8GB RAM,Quadcore Processor Server--Need help!!!!!!!  (Mohamed Hashim <nmdhashim@gmail.com>)
Ответы Re: Performance Problem with postgresql 9.03, 8GB RAM,Quadcore Processor Server--Need help!!!!!!!  ("Tomas Vondra" <tv@fuzzy.cz>)
Список pgsql-performance
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





В списке pgsql-performance по дате отправления:

Предыдущее
От: "ktm@rice.edu"
Дата:
Сообщение: Re: SSL encryption makes bytea transfer slow
Следующее
От: Rodrigo Gonzalez
Дата:
Сообщение: Re: function slower than the same code in an sql file