PG cannot use index (combination primary key)

Hi everybody,

I new on this forum and cannot find anything related with this issue.

My problem is following:
I have a table:
CREATE TABLE rights (
userid integer not null REFERENCES user(userid),
fishid integer not null REFERENCES fish(speciesid),
boatid integer not null REFERENCES boat(boatid),
CONSTRAINT rights_pri PRIMARY KEY(userid,fishid,boatid)
);

There are some tens of rows in table.

I like to use table as:
SELECT 1
FROM rights
WHERE userid=107 AND
fishid=64 AND
boatid=2

EXPLAIN shows me, that PG cannot use index:
Seq Scan on rights (cost=0.00..3.47 rows=1 width=0)

Filter: ((userid = 107) AND (fishid = 64) AND (boatid = 2))

Any ideas why?

--
BRH

First of all try disabling seqscan (see here) and check if the index is picked up, if this is the case then tune your postgresql.conf

For ten rows PG will always use a sequential scan. An index scan will not pay of for that small number

Edit: removed part of my message. I didn't read the first posting closely enough

I disabled seqscan with
set enable_seqscan=false;

and it seems to make query to use index:
Index Scan using rigths_pri on rights (cost=0.00..8.27 rows=1 width=0)
Index Cond: ((userid = 107) AND (fishid = 64) AND (boatid = 2))

It is not maybe wise to change to postgresql.conf, because there is 12 other databases on same server.

What it the reason for this seqscan?

Check shammat's post

Yeps I read it. In fact, there is 181 rows in table.

This option, when false, decrease (bigger) query speed from 250 sek to 45 sek.

Anything else I can do?

Did you analyze your table?

I'm not sure, but I think I have.
How it should be done?

Using ANALYZE
http://www.postgresql.org/docs/current/static/sql-analyze.html

or using VACCUUM ANALYZE
http://www.postgresql.org/docs/current/static/sql-vacuum.html

Bingo!

Thank's shammat!
It seems that there is something, I haven't done by analyze. After running analyze to whole db, running time drops from 260 sek to 10 sek!

With verbose on analyse or vacuum, there are no extra messages than without verbose - why?

Sounds like you have disabled autovacuum, otherwise autovacuum should have taken care of that.

Which Postgres version are you running?

Yeps Autovacuum has been disabled, but vacuumdb is been running every day.

I'm running on 8.2.6

Then you should check the vacuumdb option maybe, they are not "agressive" enough.

autovacuum has been improved since then

You should really think about an upgrade to 8.4

You are right. We already have placed a project to upgrade.

Why verbose are not talking?

No idea, maybe a bug in 8.2 or something else. You might want to take that question to the PG mailing list.