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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Mohamed Hashim <nmdhashim(at)gmail(dot)com>
Cc: tv(at)fuzzy(dot)cz, ktm(at)rice(dot)edu, Marcus Engene <mengpg2(at)engene(dot)se>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Performance Problem with postgresql 9.03, 8GB RAM, Quadcore Processor Server--Need help!!!!!!!
Date: 2011-11-02 14:26:50
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general pgsql-performance

Mohamed Hashim <nmdhashim(at)gmail(dot)com> writes:
> The below are the sample query which i put for particular one bill_id

> 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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Samba 2011-11-02 15:25:32 equivalent to "replication_timeout" on standby server
Previous Message Tom Lane 2011-11-02 14:11:54 Re: does reindex need exclusive table access?

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2011-11-02 14:38:39 Re: Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?
Previous Message Merlin Moncure 2011-11-02 14:26:28 Re: Intel 710 pgbench write latencies