[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-performance
Subject: Re: [PERFORM] Performance Problem with postgresql 9.03, 8GB
From: Mohamed Hashim <nmdhashim () gmail ! com>
Date: 2011-10-29 4:22:12
Message-ID: CACBfhZNBwjB=3xQrvqy6ManJUaRyKEOoV8dMv7jJYPtXn9XFcQ () mail ! gmail ! com
[Download RAW message or body]
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@gmail.com> wrote:
> On 28 October 2011 09:02, Mohamed Hashim <nmdhashim@gmail.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
[Attachment #3 (text/html)]
<font face="trebuchet ms,sans-serif">Thanks Alban & Gregg.<br><br><br>i will \
describe little more about that table<br><br></font><ul><li>We are using PHP \
application with Apache server & Postgresql 9.0.3 in a dedicated server.</li>
<li>stk_source table is mainly used to track the transactions from parent to \
child</li></ul> Table \
"_100410.stk_source"<br> Column | Type | \
Modifiers <br>
-----------------------+-----------+-----------------------------------------------------<br> \
source_id | integer | not null default \
nextval('source_id_seq'::regclass)<br> stock_id | integer | \
<br>
source_detail | integer[] | <br> transaction_reference | integer | <br> \
is_user_set | boolean | default false<br><br><br>We store \
transaction_type and transaction_id in source_detail column which is an interger \
array for each transactions<br>
<br>We use various functions to get the info based on transaction type <br><br>For \
eg:<br><br>In function to get the batch details we have used as<br><br>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 \
<br>
FROM order_details_shipments ods \
<br> 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 <br>
JOIN stock_transaction_detail_106 std ON \
std.transaction_id=sps.transaction_id<br> JOIN stock_transaction_detail_106 \
std_i ON std.stock_id = std_i.stock_id AND std_i.transaction_type = \
'i'::bpchar<br>
WHERE shipment_item_id=$1 ) and source_detail[1]=3) \
LOOP<br><br>...............................<br><br>................................<br><br>......................................<br><br>Similarly \
we have used in php pages and views<br>
<br>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,<br>FROM \
acc_bill_items_106 abi<br> JOIN acc_bill_details_106_table abd ON \
abd.bill_id=abi.bill_id AND abd.bill_status='act'<br>
JOIN stk_source_table ss ON ss.source_detail[2]=abi.item_id and \
ss.source_detail[1]=1<br> JOIN stock_transaction_detail_106_table std ON \
std.stock_id=ss.stock_id<br> JOIN stock_details_106_table sd106 ON \
sd106.stock_id=std.stock_id<br>
JOIN master_product_106_table mp ON mp.product_id= sd106.product_id<br> JOIN \
receipt_item_price_106_table rip ON rip.receipt_item_id=abi.item_id<br> WHERE \
abi.bill_id=$bill_id AND std.transaction_type='o' ;<br>
<br>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.<br><br>Normally if we fetch \
Select * from some_table..........it returns very fast because it has less \
records.<br>
<br>But when i put Select * from stk_source or to find the actual_cost<br><br>EXPLAIN \
ANALYZE SELECT * FROM stk_source;<br><br>i couln't able to retrieve the planner \
details waited for more than 50 to 60 mints<br><br>
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?<br><br><br><b>What are the other parameters have to look out or what are \
the other config settings to be change to have the best performance??</b><br>
<br>Kindly help to sort out this problem......<br><br><br>Thanks in \
advance..................!!!!!!<br><br>Regards<br>Hashim<br><br><br><br><br> \
<br><br><br><div class="gmail_quote">On Fri, Oct 28, 2011 at 5:07 PM, Alban Hertroys \
<span dir="ltr"><<a \
href="mailto:haramrae@gmail.com">haramrae@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex;"><div class="im">On 28 October 2011 09:02, Mohamed Hashim \
<<a href="mailto:nmdhashim@gmail.com">nmdhashim@gmail.com</a>> wrote:<br>
> EXPLAIN select * from stk_source ;<br>
> QUERY<br>
> PLAN<br>
> -------------------------------------------------------------------------------------<br>
> Result (cost=0.00..6575755.39 rows=163132513 width=42)<br>
> -> Append (cost=0.00..6575755.39 rows=163132513 width=42)<br>
> -> Seq Scan on stk_source (cost=0.00..42.40 rows=1080 \
width=45)<br> > -> Seq Scan on stk_source (cost=0.00..20928.37 \
rows=519179<br> > width=42)<br>
> -> Seq Scan on stk_source (cost=0.00..85125.82 rows=2111794<br>
> width=42)<br>
> -> Seq Scan on stk_source (cost=0.00..6469658.80 \
rows=160500460<br> > width=42)<br>
<br>
</div>That plan gives you the best possible performance given your query.<br>
Your example probably doesn't fit the problem you're investigating.<br>
<font color="#888888"><br>
--<br>
If you can't see the forest for the trees,<br>
Cut the trees and you'll see there is no forest.<br>
</font></blockquote></div><br><br clear="all"><br>-- <br><span \
style="font-family:trebuchet ms,sans-serif">Regards</span><br \
style="font-family:trebuchet ms,sans-serif"><span style="font-family:trebuchet \
ms,sans-serif">Mohamed Hashim.N</span><br style="font-family:trebuchet \
ms,sans-serif">
<span style="font-family:trebuchet ms,sans-serif">Mobile:09894587678</span><br>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic