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.



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?

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?


Reply via email to