[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] slow query on tables with new columns added.
From:       Filip_Rembiałkowski <plk.zuber () gmail ! com>
Date:       2011-09-26 23:45:59
Message-ID: CAP_rwwmUwW6a6CD+5AQXaOTo5==US5gN+Nz5vOhYDesSLqOkwA () mail ! gmail ! com
[Download RAW message or body]

2011/9/26 M. D. <lists@turnkey.bz>

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

I didn't respond earlier, because I actually don't see any easy way of
speeding up the query.

The memory settings seem fine for this size of data.

It does not look like you can change things by simply adding indexes. I
mean, you can certainly add a specially crafted partial index on
gltx.customer_id WHERE (gltx.inactive_on IS NULL) AND (gltx.posted = 'Y') -
this can earn you a few percent max.

The problem here might be the type of join columns - we can see they are
about 10 characters which is not an ideal choice (that's one of reasons why
I'm a fan of artificial integer pkeys).

You _could_ try running the query with enable_mergejoin = off and see what
happens.

You can check if the problem persists after dumping and reloading to another
db.

If app modification was possible, you could materialize the data _before_ it
must be queried - using summary table and appropriate triggers for keeping
it up-to-date.

Regarding your last comment - on that customer_id values should be 0 - if
it's a persistent business rule, I would try to create a CHECK to reflect
it. With some luck and fiddling, constraint_exclusion might come to help
with speeding up your query.

Also, if there is something special about customer_id distribution - table
partitioning might be an option.

Ok, that's a long list - hope this helps, and good luck.

After all you can throw more hardware at the problem - or hire some Pg
magician :-)

[Attachment #3 (text/html)]

<br><br><div class="gmail_quote">2011/9/26 M. D. <span dir="ltr">&lt;<a \
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;">


  
    
  
  <div bgcolor="#FFFFFF" text="#000000"><div class="im">
    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&#39;t see it helping as I don&#39;t have access to
    the application source to change that. <br>
    <br>
    So yes, by changing settings, I would like to know if there&#39;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&#39;m not
    sure what else could be done.<br>
    <br></div>
    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.<div class="im"><br><br></div></div></blockquote><div><br>Hi<br><br>I \
didn&#39;t respond earlier, because I actually don&#39;t see any easy way of speeding \
up the query.<br><br>The memory settings seem fine for this size of data.<br>

<br>It does not look like you can change things by simply adding indexes. I mean, you \
can certainly add a specially crafted partial index on gltx.customer_id WHERE \
(gltx.inactive_on IS NULL) AND (gltx.posted = &#39;Y&#39;) - this can earn you a few \
percent max.<br>

<br>The problem here might be the type of join columns - we can see
 they are about 10 characters which is not an ideal choice (that&#39;s one of 
reasons why I&#39;m a fan of artificial integer pkeys). <br>

<br>
You _could_ try running the query with enable_mergejoin = off and see what \
happens.<br> <br>You can check if the problem persists after dumping and reloading to \
another db.<br><br>If app modification was possible, you could materialize the data \
_before_ it must be queried - using summary table and appropriate triggers for \
keeping it up-to-date. <br>

<br>Regarding your last comment - on that customer_id values should be 0 - if \
it&#39;s a persistent business rule, I would try to create a CHECK to reflect it. \
With some luck and fiddling, constraint_exclusion might come to help   with speeding \
up your query.<br>

<br>Also, if there is something special about customer_id distribution - table \
partitioning might be an option.<br><br>Ok, that&#39;s a long list - hope this helps, \
and good luck.<br><br>After all you can throw more hardware at the problem - or hire \
some Pg magician :-)<br>

<br></div></div>



[prev in list] [next in list] [prev in thread] [next in thread]