User-Agent: K-9 Mail for Android
In-Reply-To: <53AB35CC.10202@optionshouse.com>
References: <53AB2CB1.8080201@comcast.net> <53AB35CC.10202@optionshouse.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="----797JFTNG5K4Q5KXPN6AN47A8W9C9G7"
Content-Transfer-Encoding: 8bit
Subject: Re: how to improve perf of 131MM row table?
From: Aaron Weber <aweber@comcast.net>
Date: Wed, 25 Jun 2014 17:40:47 -0400
To: Shaun Thomas <sthomas@optionshouse.com>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Message-ID: <1801a5d4-c3f1-4c0c-9c2c-ffb9d7f723a3@email.android.com>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

------797JFTNG5K4Q5KXPN6AN47A8W9C9G7
Content-Transfer-Encoding: 8bit
Content-Type: text/plain;
 charset=UTF-8

I will gather the other data tonight. Thank you. 

In the meantime, I guess I wasn't clear about some other particulars 
The query's where clause is only an "IN", with a list of id's (those I mentioned are the PK), and the join is explicitly on the PK (so, indexed). 

Thus, there should be only the explicit matches to the in clause returned, and if postgresql isn't using the unique index on that column, I would be very shocked (to the point I would suggest there is a bug somewhere). 

An IN with 50 int values took 23sec to return (by way of example). 

Thanks again. 
-- 
Aaron

On June 25, 2014 4:49:16 PM EDT, Shaun Thomas <sthomas@optionshouse.com> wrote:
>On 06/25/2014 03:10 PM, AJ Weber wrote:
>
>> I have a relatively sizable postgresql 9.0.2 DB with a few large
>tables
>> (keep in mind "large" is relative, I'm sure there are plenty larger
>out
>> there).
>
>Regardless of any help we might offer regarding this, you need to 
>upgrade your installation to 9.0.17. You are behind by several 
>performance, security, and integrity bugfixes, some of which address 
>critical corruption bugs related to replication.
>
>> One of my queries that seems to be bogging-down performance is a join
>> between two tables on each of their BIGINT PK's (so they have default
>> unique constraint/PK indexes on them).  One table is a detail table
>for
>> the other.
>
>This isn't enough information. Just knowing the relative sizes of the 
>tables doesn't tell us which columns are indexed, whether or not the 
>query is using those indexes, how many rows usually match, which
>queries 
>are performing badly, and so on.
>
>Please refer to this page to ask performance related questions:
>
>https://wiki.postgresql.org/wiki/Slow_Query_Questions
>
>Without much of this information, we'd only be speculating.
>
>-- 
>Shaun Thomas
>OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL,
>60604
>312-676-8870
>sthomas@optionshouse.com
>
>______________________________________________
>
>See http://www.peak6.com/email_disclaimer/ for terms and conditions
>related to this email

------797JFTNG5K4Q5KXPN6AN47A8W9C9G7
Content-Type: text/html;
 charset=utf-8
Content-Transfer-Encoding: 8bit

<html><head></head><body>I will gather the other data tonight. Thank you. <br>
<br>
In the meantime, I guess I wasn&#39;t clear about some other particulars <br>
The query&#39;s where clause is only an &quot;IN&quot;, with a list of id&#39;s (those I mentioned are the PK), and the join is explicitly on the PK (so, indexed). <br>
<br>
Thus, there should be only the explicit matches to the in clause returned, and if postgresql isn&#39;t using the unique index on that column, I would be very shocked (to the point I would suggest there is a bug somewhere). <br>
<br>
An IN with 50 int values took 23sec to return (by way of example). <br>
<br>
Thanks again. <br>
-- <br>
Aaron<br><br><div class="gmail_quote">On June 25, 2014 4:49:16 PM EDT, Shaun Thomas &lt;sthomas@optionshouse.com&gt; wrote:<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<pre class="k9mail">On 06/25/2014 03:10 PM, AJ Weber wrote:<br /><br /><blockquote class="gmail_quote" style="margin: 0pt 0pt 1ex 0.8ex; border-left: 1px solid #729fcf; padding-left: 1ex;"> I have a relatively sizable postgresql 9.0.2 DB with a few large tables<br /> (keep in mind "large" is relative, I'm sure there are plenty larger out<br /> there).<br /></blockquote><br />Regardless of any help we might offer regarding this, you need to <br />upgrade your installation to 9.0.17. You are behind by several <br />performance, security, and integrity bugfixes, some of which address <br />critical corruption bugs related to replication.<br /><br /><blockquote class="gmail_quote" style="margin: 0pt 0pt 1ex 0.8ex; border-left: 1px solid #729fcf; padding-left: 1ex;"> One of my queries that seems to be bogging-down performance is a join<br /> between two tables on each of their BIGINT PK's (so they have default<br /> unique constraint/PK indexes on them).  One table is a detail table
for<br /> the other.<br /></blockquote><br />This isn't enough information. Just knowing the relative sizes of the <br />tables doesn't tell us which columns are indexed, whether or not the <br />query is using those indexes, how many rows usually match, which queries <br />are performing badly, and so on.<br /><br />Please refer to this page to ask performance related questions:<br /><br /><a href="https://wiki.postgresql.org/wiki/Slow_Query_Questions">https://wiki.postgresql.org/wiki/Slow_Query_Questions</a><br /><br />Without much of this information, we'd only be speculating.<br /></pre></blockquote></div></body></html>
------797JFTNG5K4Q5KXPN6AN47A8W9C9G7--