Message-ID: <4FCE1E4A.60704@optionshouse.com>
Date: Tue, 5 Jun 2012 09:57:14 -0500
From: Shaun Thomas <sthomas@optionshouse.com>
Reply-To: <sthomas@optionshouse.com>
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; Linux i686;
	rv:12.0) Gecko/20120430 Thunderbird/12.0.1
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Sequencial scan in a JOIN
References: <BLU161-W1125BE7D5AED110518DEFFDD0C0@phx.gbl>,
	<4FCE0681.5040306@optionshouse.com>
	<BLU161-W5750D54FD724EB2412564CDD0C0@phx.gbl>,
	<4FCE1160.4070003@optionshouse.com>
	<BLU161-W356C999A18D1059F121CB8DD0C0@phx.gbl>
In-Reply-To: <BLU161-W356C999A18D1059F121CB8DD0C0@phx.gbl>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
content-transfer-encoding: 7bit

On 06/05/2012 09:41 AM, Andrew Jaimes wrote:

> The second query ran better than the first one:

That's what I figured. Ok, so looking back to your original message again:

CREATE INDEX i08_a_activity
   ON a_activity
   USING btree
   (activequeueid , vstatus , ventrydate );

Based on the query here, it doesn't appear that vstatus or ventrydate 
are doing you any good in that index. Nor would your query even really 
make use of them anyway, considering their catch-all equalities. If you 
can make a clone of a_activity, could you try this index instead with 
your original query:

CREATE INDEX idx_a_activity_queue
     ON a_activity_clone (activequeueid);

Then compare to this:

CREATE INDEX idx_a_activity_queue_sbuid
     ON a_activity_clone (activequeueid, sbuid);

And the results of this query would also be handy:

SELECT attname, n_distinct
   FROM pg_stats
  WHERE tablename='a_activity';

Generally you want to order your composite indexes in order of 
uniqueness, if you even want to make a composite index in the first 
place. I noticed in both cases, it's basically ignoring sbuid aside from 
the implied hash to exclude non-matches.

-- 
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email