Re: Sequencial scan in a JOIN

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