From: Richard Huxton <dev@archonet.com>
To: 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 13:49:10 +0100
Message-ID: <42FB4946.6030105@archonet.com> (raw)
In-Reply-To: <42FB3791.2060200@almabioinfo.com>
References: <42FB3791.2060200@almabioinfo.com>
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
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: dev@archonet.com, lcornide@almabioinfo.com
Subject: Re: Why is not using the index
In-Reply-To: <42FB4946.6030105@archonet.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