Message-ID: <42FB4946.6030105@archonet.com>
Date: Thu, 11 Aug 2005 13:49:10 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317)
MIME-Version: 1.0
To: Luis Cornide Arce <lcornide@almabioinfo.com>
Cc: PGSL-PERFORMANCE LIST <pgsql-performance@postgresql.org>
Subject: Re: Why is not using the index
References: <42FB3791.2060200@almabioinfo.com>
In-Reply-To: <42FB3791.2060200@almabioinfo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Luis Cornide Arce wrote:
> Hi everyone,
> 
> I have some problems with a quite long query and the plan postgreSQL is 
> choosing. The query joins 12 tables and in the WHERE clause I use a IN 
> expression with a lot of identifiers (up to 2000). The problem is that 
> the planner is proposing a seq_scan on two tables 2M rows each 
> (internalexpressionprofile and expressionprofile)
> 
> I have just try this query (after doing a vacuum analyze), in the 'IN' 
> clause there are 1552 identifiers, and the query should return 14K rows.
> I'm using a PostgreSQL 8.0.2 on a SuSE 8.1 with 1GB of RAM.

> WHERE epg.expprogeneid IN (80174,84567,...) AND 
> epg.expprogeneid=epro.expprogeneid

->  Hash Join
	(cost=15413.58..78079.33 rows=24339 width=134)
	(actual time=1489.347..5721.306 rows=41904 loops=1)
	Hash Cond: ("outer".expprogeneid = "inner".expprogeneid)
    ->  Seq Scan on expressionprofile epro
		(cost=0.00..48263.24 rows=2831824 width=8)
		(actual time=0.039..3097.656 rows=2839676 loops=1)

->  Index Scan using
expprogene_pk, expprogene_pk, [......] on expprogene epg
(cost=0.00..10698.83 rows=1546 width=8) (actual time=0.027..15.907
rows=1552 loops=1)
	Index Cond: ((expprogeneid = 80174) OR (expprogeneid = 84567)
	OR (expprogeneid = 83608) OR [OR ....])

OK - it looks like the "IN" clause is using your index. The fact that 
it's using a Seq-scan on "expressionprofile epro" looks odd though, 
especially since it expects 24339 matches (out of 2.8 million rows - 
that should favour an index).

Of course, I've not considered the context of the rest of the query, but 
I'd expect the index to be used.

Do you have any unusual config settings?
-- 
   Richard Huxton
   Archonet Ltd