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


Well I have change the next setting in the postgresql.conf

shared_buffers= 16384
work_mem =32768
maintenance_work_mem= 65536
bgwriter_delay =800
bgwriter_maxpages= 100
wal_buffers =64
efective_cache_size= 20000

The rest of the settings are the default.

Thanks,

Luis

Richard Huxton escribió:

> 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?