From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mohamed Hashim <nmdhashim@gmail.com>
Cc: tv@fuzzy.cz, ktm@rice.edu, Marcus Engene <mengpg2@engene.se>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance Problem with postgresql 9.03, 8GB RAM, Quadcore Processor Server--Need help!!!!!!!
Date: Wed, 02 Nov 2011 10:26:50 -0400
Message-ID: <29599.1320244010@sss.pgh.pa.us> (raw)
In-Reply-To: <CACBfhZNoq_hjZyMnBTnrxR8rif31QvJ5dtdgQMdPwO0+x2AKpQ@mail.gmail.com>
References: <CACBfhZPO1GJv4nVunPXGA2EZq923On-CBGtxyhtQYAaSMCuBEQ@mail.gmail.com>
<CAF-3MvOjY17pVZXSWgYDB5dMy-B07KiVYexR4RRutxZyXeKjbA@mail.gmail.com>
<CACBfhZNBwjB=3xQrvqy6ManJUaRyKEOoV8dMv7jJYPtXn9XFcQ@mail.gmail.com>
<CACBfhZNr5qnMN0=8hp1Fb3hA8jwoEXnJAahua1JT5f5gXT9tuA@mail.gmail.com>
<4EAFC29A.40509@engene.se>
<24830.1320154980@sss.pgh.pa.us>
<CACBfhZNoq_hjZyMnBTnrxR8rif31QvJ5dtdgQMdPwO0+x2AKpQ@mail.gmail.com>
Mohamed Hashim <nmdhashim@gmail.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, nmdhashim@gmail.com, mengpg2@engene.se
Subject: Re: Performance Problem with postgresql 9.03, 8GB RAM, Quadcore Processor Server--Need help!!!!!!!
In-Reply-To: <29599.1320244010@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox