Skip site navigation (1) Skip section navigation (2)

Re: Sequencial scan in a JOIN

From: Shaun Thomas <sthomas(at)optionshouse(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Sequencial scan in a JOIN
Date: 2012-06-05 14:57:14
Message-ID: 4FCE1E4A.60704@optionshouse.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)optionshouse(dot)com

______________________________________________

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

pgsql-performance by date

Next:From: Alejandro CarrilloDate: 2012-06-05 16:12:34
Subject: Missing block Magic
Previous:From: Shaun ThomasDate: 2012-06-05 14:02:08
Subject: Re: Sequencial scan in a JOIN

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group