Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 830C0B5D803
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 26 Sep 2011 18:14:29 -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 76228-05
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 26 Sep 2011 21:14:22 +0000 (UTC)
X-Greylist: delayed 01:01:03.84296 by SQLgrey-1.8.0-rc2
Received: from dime159.dizinc.com (dime159.dizinc.com [66.7.216.77])
	by mail.postgresql.org (Postfix) with ESMTP id E7982B5D801
	for <pgsql-performance@postgresql.org>;
	Mon, 26 Sep 2011 18:14:22 -0300 (ADT)
Received: from [200.32.232.149] (port=47889 helo=[192.168.54.128])
	by dime159.dizinc.com with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.69)
	(envelope-from <lists@turnkey.bz>) id 1R8HYA-00009K-1I
	for pgsql-performance@postgresql.org; Mon, 26 Sep 2011 16:13:14 -0400
Message-ID: <4E80DCD6.6080401@turnkey.bz>
Date: Mon, 26 Sep 2011 14:13:10 -0600
From: "M. D." <lists@turnkey.bz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:6.0.2) Gecko/20110906 Thunderbird/6.0.2
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: slow query on tables with new columns added.
References: <4E7CC6B9.2040404@turnkey.bz>
	<CAP_rww=w1fHxy01-hMciengN=6ovxtZWN4FgRDNdywb-Htm3iQ@mail.gmail.com>
In-Reply-To: 
 <CAP_rww=w1fHxy01-hMciengN=6ovxtZWN4FgRDNdywb-Htm3iQ@mail.gmail.com>
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
X-AntiAbuse: This header was added to track abuse,
	please include it with any abuse report
X-AntiAbuse: Primary Hostname - dime159.dizinc.com
X-AntiAbuse: Original Domain - postgresql.org
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - turnkey.bz
X-Source: 
X-Source-Args: 
X-Source-Dir: 
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.082 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	HTML_MESSAGE=0.001, MIME_HTML_ONLY=0.723,
	TO_NO_BRKTS_HTML_ONLY=1.258
X-Spam-Level: 
X-Archive-Number: 201109/373
X-Sequence-Number: 45149

<html>
  <head>
    <meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    I have full access to the database, but no access to the application
    source code.  If creating an index will help, I can do that, but
    with the columns I don't see it helping as I don't have access to
    the application source to change that. <br>
    <br>
    So yes, by changing settings, I would like to know if there's any
    memory settings I can change to help or create an index.  There is
    an index on the customer_id column in the gltx table, so I'm not
    sure what else could be done.<br>
    <br>
    If there was a way to create a select trigger, I would do it and
    return 0 for both columns on that customer_id as it should always be
    0.<br>
    <br>
    <br>
    On 09/24/2011 12:10 AM, Filip Rembiałkowski wrote:
    <blockquote
cite="mid:CAP_rww=w1fHxy01-hMciengN=6ovxtZWN4FgRDNdywb-Htm3iQ@mail.gmail.com"
      type="cite"><br>
      <div class="gmail_quote">2011/9/23 M. D. <span dir="ltr">&lt;<a
            moz-do-not-send="true" href="mailto:lists@turnkey.bz">lists@turnkey.bz</a>&gt;</span><br>
        <blockquote class="gmail_quote" style="margin:0 0 0
          .8ex;border-left:1px #ccc solid;padding-left:1ex;">
          <br>
          I did a software upgrade, and with it came a new feature where
          when selecting a customer it queries for the sum of a few
          columns.  This takes 7 seconds for the 'Cash Sale' customer -
          by far the most active customer. I'd like to see if it's
          possible to get it down a bit by changing settings.<br>
          <br>
        </blockquote>
        <div><br>
          To make things clear before we search for a solution. You
          wrote "by changing settings". Is it the only option? Can't you
          change the query in software? Can't you change database schema
          (add indexes etc)?<br>
           
          <br>
          <br>
        </div>
        <blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt
          0.8ex; border-left: 1px solid rgb(204, 204, 204);
          padding-left: 1ex;">
          Query:<br>
          explain analyse select sum(item_points),sum(disc_points) from
          invoice left join gltx on invoice.invoice_id = gltx.gltx_id<br>
          where gltx.inactive_on is null and gltx.posted = 'Y' and
          gltx.customer_id = 'A0ZQ2gsACIsEKLI638ikyg'<br>
        </blockquote>
        <div><br>
          Aside from other things, you know that LEFT join here is
          useless? - planner should collapse it to normal join but I'd
          check.<br>
          <br>
          <br>
          Filip<br>
          <br>
        </div>
      </div>
    </blockquote>
    <br>
  </body>
</html>