Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 3933BB5DC22
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  3 Nov 2011 12:08:59 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 52551-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  3 Nov 2011 15:08:53 +0000 (UTC)
X-Greylist: delayed 00:06:41.461424 by SQLgrey-1.8.0-rc2
Received: from mailout-de.gmx.net (mailout-de.gmx.net [213.165.64.22])
	by mail.postgresql.org (Postfix) with SMTP id 336E9B5DC16
	for <pgsql-performance@postgresql.org>;
	Thu,  3 Nov 2011 12:08:51 -0300 (ADT)
Received: (qmail invoked by alias); 03 Nov 2011 15:02:08 -0000
Received: from office.icomedias.com (EHLO [10.192.1.141]) [109.73.147.5]
	by mail.gmx.net (mp072) with SMTP; 03 Nov 2011 16:02:08 +0100
X-Authenticated: #5984785
X-Provags-ID: V01U2FsdGVkX1+IN0xGj62I8zYT+xtHfKgs+O0FxbnKf2HOGk0pfJ
	H1rojorguWLZQh
Message-ID: <4EB2ACEF.8090305@gmx.at>
Date: Thu, 03 Nov 2011 16:02:07 +0100
From: Mario Weilguni <roadrunner6@gmx.at>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Performance Problem with postgresql 9.03, 8GB RAM,Quadcore
	Processor Server--Need help!!!!!!!
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>
In-Reply-To: 
 <CACBfhZNoq_hjZyMnBTnrxR8rif31QvJ5dtdgQMdPwO0+x2AKpQ@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------030802090207020804090902"
X-Y-GMX-Trusted: 0
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.798 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, FREEMAIL_ENVFROM_END_DIGIT=0.1, FREEMAIL_FROM=0.001,
	HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201111/48
X-Sequence-Number: 45559

This is a multi-part message in MIME format.
--------------030802090207020804090902
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Am 02.11.2011 08:12, schrieb Mohamed Hashim:
> Dear All
>
> Thanks for your suggestions & replies.
>
> 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;

First I would try this:
explain analyze select * from stk_source where source_detail[1] = 1;
explain analyze select * from stk_source where source_detail[2] = 12356;

Both times you'll get sequential scans, and that's the root of the 
problem. Oh, you mentioned that you use partitioning, but there seems to 
be no condition for that.

You should really rethink your database schema, at least try to pull out 
all indexable fields out of that int[] into columns, and use indices on 
those fields.

Regards
Mario






--------------030802090207020804090902
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    Am 02.11.2011 08:12, schrieb Mohamed Hashim:
    <blockquote
cite="mid:CACBfhZNoq_hjZyMnBTnrxR8rif31QvJ5dtdgQMdPwO0+x2AKpQ@mail.gmail.com"
      type="cite"><font face="trebuchet ms,sans-serif">Dear All<br>
        <br>
        Thanks for your suggestions &amp; replies.<br>
        <br>
        The below are the sample query which i put for particular one
        bill_id<br>
        <br>
        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<br>
        FROM acc_bill_items_106 abi<br>
        &nbsp;&nbsp;&nbsp; JOIN acc_bill_details_106 abd ON abd.bill_id=abi.bill_id<br>
        &nbsp;&nbsp;&nbsp; JOIN stk_source ss ON&nbsp; ss.source_detail[1]=1 and
        ss.source_detail[2]=abi.item_id<br>
        &nbsp;&nbsp;&nbsp; JOIN stock_transaction_detail_106 std ON
        std.stock_id=ss.stock_id<br>
        &nbsp;&nbsp;&nbsp; JOIN stock_details_106 sd106 ON sd106.stock_id=std.stock_id<br>
        &nbsp;&nbsp;&nbsp; JOIN master_product_106 mp ON mp.product_id=
        sd106.product_id<br>
        &nbsp;&nbsp;&nbsp; JOIN receipt_item_price_106 rip ON
        rip.receipt_item_id=abi.item_id<br>
        &nbsp;&nbsp;&nbsp; WHERE abi.bill_id=12680;</font><br>
    </blockquote>
    <br>
    First I would try this: <br>
    explain analyze select * from stk_source where source_detail[1] = 1;<br>
    explain analyze select * from stk_source where source_detail[2] =
    12356;<br>
    <br>
    Both times you'll get sequential scans, and that's the root of the
    problem. Oh, you mentioned that you use partitioning, but there
    seems to be no condition for that.<br>
    <br>
    You should really rethink your database schema, at least try to pull
    out all indexable fields out of that int[] into columns, and use
    indices on those fields.<br>
    <br>
    Regards<br>
    Mario<br>
    <br>
    <br>
    <br>
    <br>
    <br>
  </body>
</html>

--------------030802090207020804090902--