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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Mohamed Hashim <nmdhashim@xxxxxxxxx> writes:
> 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;

All the time seems to be going into the seqscan on stk_source and its
child tables.  It looks like it would help if "ss.source_detail[1]=1 and
ss.source_detail[2]=abi.item_id" were indexable (particularly the
latter).  Which probably means you need to rethink your data
representation.  Putting things that you need to index on into an array
is not a very good design.  I suppose you can do it if you're absolutely
set on it (functional indexes on (source_detail[1]) and (source_detail[2]))
but it appears to suck from a notational point of view too.  Six months
from now, when you look at this code, are you going to remember what's
the difference between source_detail[1] and source_detail[2]?  Not
without consulting your notes, I bet.

			regards, tom lane

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux