pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: M. D. <lists@turnkey.bz>
To: pgsql-performance@postgresql.org
Subject: Re: slow query on tables with new columns added.
Date: Mon, 26 Sep 2011 14:13:10 -0600
Message-ID: <4E80DCD6.6080401@turnkey.bz> (raw)
In-Reply-To: <CAP_rww=w1fHxy01-hMciengN=6ovxtZWN4FgRDNdywb-Htm3iQ@mail.gmail.com>
References: <4E7CC6B9.2040404@turnkey.bz>
	<CAP_rww=w1fHxy01-hMciengN=6ovxtZWN4FgRDNdywb-Htm3iQ@mail.gmail.com>

<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>



view thread (4+ messages)  latest in thread

Message-ID: <4E80DCD6.6080401@turnkey.bz>
Permalink:  ../4E80DCD6.6080401@turnkey.bz/
Also on:    postgresql.org/message-id/4E80DCD6.6080401@turnkey.bz

 · 

reply
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: lists@turnkey.bz
  Subject: Re: slow query on tables with new columns added.
  In-Reply-To: <4E80DCD6.6080401@turnkey.bz>

* 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