Re: Why is not using the index

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?