From: Luis Cornide Arce <lcornide@almabioinfo.com>
Cc: PGSL-PERFORMANCE LIST <pgsql-performance@postgresql.org>
Subject: Re: Why is not using the index
Date: Thu, 11 Aug 2005 15:59:31 +0200
Message-ID: <42FB59C3.5020809@almabioinfo.com> (raw)
In-Reply-To: <42FB4946.6030105@archonet.com>
References: <42FB3791.2060200@almabioinfo.com>
<42FB4946.6030105@archonet.com>
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lcornide@almabioinfo.com
Subject: Re: Why is not using the index
In-Reply-To: <42FB59C3.5020809@almabioinfo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox