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

Hi Hashim,

After I upgraded from Postgres 8.3/8.4 to 9.0 I had all sorts of
problems with queries with many joins. Queries that used to take 1ms
suddenly take half a minute for no apparent reason.

I have 72GB which I think makes the planner go bonkers and be too eager
doing a seq scan. I tried to compensate with ridiculously low
cpu_index_tuple_cost but it had little effect.

If I were you, I would try to remove some of the joined tables and see
what happens. When does it start to run very slowly? How does the plan
look right before it's super slow?

One workaround I've done is if something looking like this....

select
...
from
table_linking_massive_table tlmt
,massive_table mt
,some_table1 st1
,some_table2 st2
,some_table3 st3
,some_table4 st4
where
tlmt.group_id = 123223 AND
mt.id = tmlt.massive_table AND
st1.massive_table = mt.id AND
st2.massive_table = mt.id AND
st3.massive_table = mt.id AND
st4.massive_table = mt.id

...suddenly gets slow, it has helped to rewrite it as....

select
...
from
(
select
...
from
table_linking_massive_table tlmt
,massive_table mt
where
tlmt.group_id = 123223 AND
mt.id = tmlt.massive_table AND
) as mt
,some_table1 st1
,some_table2 st2
,some_table3 st3
,some_table4 st4
where
tlmt.group_id = 123223 AND
mt.id = tmlt.massive_table AND
st1.massive_table = mt.id AND
st2.massive_table = mt.id AND
st3.massive_table = mt.id AND
st4.massive_table = mt.id

This seems to force Postgres to evaluate the mt subselect first and not
get ideas about how to join. It was a few years ago since I used Oracle
but if I remember correctly Oracle looked at the order of the things in
the where section. In this example Oracle would be encourage to use tlmt
as base table and take it from there. It doesn't seem to me that
Postgres cares about this order. Not caring would possibly be more
forgiving with automatically generated sql but it also implies the
planner always makes the best decisions which it obviously is not. I
might be talking rubbish here, these are my empirical observations.

I'm sure you'll get better answers, but this is what I've done.

I assume you have done your analyze & indexing correctly etc.

Best regards,
Marcus

On 11/1/11 4:03 , Mohamed Hashim wrote:
> 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
> <mailto: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 <mailto:haramrae(at)gmail(dot)com>> wrote:
>
> On 28 October 2011 09:02, Mohamed Hashim <nmdhashim(at)gmail(dot)com
> <mailto: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