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

Any idea or suggestions how to improve my database best
performance.................???

Regards
Hashim

On Sat, Oct 29, 2011 at 9:40 AM, Mohamed Hashim <nmdhashim(at)gmail(dot)com> wrote:

> Thanks Alban & Gregg.
>
>
> i will describe little more about that table
>
>
> - We are using PHP application with Apache server & Postgresql 9.0.3
> in a dedicated server.
> - stk_source table is mainly used to track the transactions from
> parent to child
>
> Table "_100410.stk_source"
> Column | Type |
> Modifiers
> -----------------------+-----------+-----------------------------------------------------
>
> source_id | integer | not null default
> nextval('source_id_seq'::regclass)
> stock_id | integer |
> source_detail | integer[] |
> transaction_reference | integer |
> is_user_set | boolean | default false
>
>
> We store transaction_type and transaction_id in source_detail column which
> is an interger array for each transactions
>
> We use various functions to get the info based on transaction type
>
> For eg:
>
> In function to get the batch details we have used as
>
> FOR batch_id_rec in select distinct(batch_id) from order_status_batches
> osb join batch_status_stock bss on osb.status_id=bss.batch_status_id where
> stock_id in (select source_detail[2] from stk_source where stock_id IN
> (SELECT
> std_i.stock_id
>
> FROM order_details_shipments
> ods
>
> JOIN shipment_pack_stock sps ON sps.pack_id=ods.pack_id AND
> ods.order_id=sps.order_id AND ods.item_id=sps.item_id
> JOIN stock_transaction_detail_106 std ON
> std.transaction_id=sps.transaction_id
> JOIN stock_transaction_detail_106 std_i ON std.stock_id =
> std_i.stock_id AND std_i.transaction_type = 'i'::bpchar
> WHERE shipment_item_id=$1 ) and source_detail[1]=3) LOOP
>
> ...............................
>
> ................................
>
> ......................................
>
> Similarly we have used in php pages and views
>
> 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_table abd ON abd.bill_id=abi.bill_id AND
> abd.bill_status='act'
> JOIN stk_source_table ss ON ss.source_detail[2]=abi.item_id and
> ss.source_detail[1]=1
> JOIN stock_transaction_detail_106_table std ON std.stock_id=ss.stock_id
> JOIN stock_details_106_table sd106 ON sd106.stock_id=std.stock_id
> JOIN master_product_106_table mp ON mp.product_id= sd106.product_id
> JOIN receipt_item_price_106_table rip ON
> rip.receipt_item_id=abi.item_id
> WHERE abi.bill_id=$bill_id AND std.transaction_type='o' ;
>
> So where ever we have JOIN or used in functions the performance is very
> low some times query returns results takes more than 45 mints.
>
> Normally if we fetch Select * from some_table..........it returns very
> fast because it has less records.
>
> But when i put Select * from stk_source or to find the actual_cost
>
> EXPLAIN ANALYZE SELECT * FROM stk_source;
>
> i couln't able to retrieve the planner details waited for more than 50 to
> 60 mints
>
> so question is in spite of having good server with high configuration and
> also changed the postgresql configuration settings then why the system is
> crawling?
>
>
> *What are the other parameters have to look out or what are the other
> config settings to be change to have the best performance??*
>
> Kindly help to sort out this problem......
>
>
> Thanks in advance..................!!!!!!
>
> Regards
> Hashim
>
>
>
>
>
>
>
>
> On Fri, Oct 28, 2011 at 5:07 PM, Alban Hertroys <haramrae(at)gmail(dot)com>wrote:
>
>> On 28 October 2011 09:02, Mohamed Hashim <nmdhashim(at)gmail(dot)com> wrote:
>> > EXPLAIN select * from stk_source ;
>> > QUERY
>> > PLAN
>> >
>> -------------------------------------------------------------------------------------
>> > Result (cost=0.00..6575755.39 rows=163132513 width=42)
>> > -> Append (cost=0.00..6575755.39 rows=163132513 width=42)
>> > -> Seq Scan on stk_source (cost=0.00..42.40 rows=1080
>> width=45)
>> > -> Seq Scan on stk_source (cost=0.00..20928.37 rows=519179
>> > width=42)
>> > -> Seq Scan on stk_source (cost=0.00..85125.82 rows=2111794
>> > width=42)
>> > -> Seq Scan on stk_source (cost=0.00..6469658.80
>> rows=160500460
>> > width=42)
>>
>> That plan gives you the best possible performance given your query.
>> Your example probably doesn't fit the problem you're investigating.
>>
>> --
>> If you can't see the forest for the trees,
>> Cut the trees and you'll see there is no forest.
>>
>
>
>
> --
> Regards
> Mohamed Hashim.N
> Mobile:09894587678
>

--
Regards
Mohamed Hashim.N
Mobile:09894587678