Index/Seq Scan Problem - Mailing list pgsql-general

Hi all,

Now I know you're all cringing at the subject.  I've been trying to resolve this as
much as I can by reading through the many many other posts of similar problems, but
I'm at the end of my tether.

Here's the scenario.

Firstly, I'm using PostgreSQL 7.2 - I know it's a bit behind in the updates, and if
you think upgrading will solve the problem, great, but I have my doubts - particularly
being a production environment. I have a table with 597041 rows.  It contains 14
columns, 10 are int4, 4 are int8.  It has a three-column primary key on three of the
int8 columns.  The definition is as follows:

       Column       |  Type   |     Modifiers
--------------------+---------+--------------------
 playerid           | bigint  | not null
 teamid             | bigint  | not null
 gameid             | bigint  | not null
 completegameteamid | bigint  | not null default 0
 nsendoff           | integer | not null default 0
 nsinbin            | integer | not null default 0
 bcaptain           | integer | not null default 0
 bgoalkicker        | integer | not null default 0
 npts               | integer | not null default 0
 nfwdtries          | integer | not null default 0
 nfieldgoals        | integer | not null default 0
 ngoals             | integer | not null default 0
 ntries             | integer | not null default 0
 teammakeupid       | integer | not null

There are two indexes:
ix_completegameteam_gameteam (on gameid, teamid)
ix_completegameteam_game (on gameid)

Plus of course the primary key on gameid, teamid, playerid. completegameteamid used to
be the primary key using a sequence, but I got rid of it since it served no purpose.

Each combination of gameid, teamid has 0-17 rows associated with it, guaranteed.
Therefore, the index on gameid, teamid should, as far as I can tell, always return 17
or less rows, and should be easily the most efficient means to pick up the data.

The entire database has a regular schedule of VACUUM ANALYZE, run nightly.

Here's an explain result on a basic query:

explain analyze select * from completegameteam where gameid = 40292 and teamid = 1747;
NOTICE:  QUERY PLAN:

Seq Scan on completegameteam  (cost=0.00..16917.12 rows=1 width=72) (actual
time=330.82..799.46 rows=17 loops=1)
Total runtime: 799.58 msec

EXPLAIN

On increasing the statistics for gameid and teamid from 10 to 100 and doing an
analyze, the explain analyze changes:

explain analyze select * from completegameteam where gameid = 40292 and teamid = 1747;
NOTICE:  QUERY PLAN:

Seq Scan on completegameteam  (cost=0.00..16917.12 rows=1 width=72) (actual
time=378.29..1743.27 rows=17 loops=1)
Total runtime: 1743.39 msec

EXPLAIN

On turning off sequence scans:

=# set enable_seqscan = 0;
SET VARIABLE
# explain analyze select * from completegameteam where gameid = 40292 and teamid =
1747;
NOTICE:  QUERY PLAN:

Seq Scan on completegameteam  (cost=100000000.00..100016917.12 rows=1 width=72)
(actual time=330.05..2698.87 rows=17 loops=1)
Total runtime: 2698.97 msec

EXPLAIN

I'm really getting stuck on this.  I even ran a CLUSTER on the table on the gameid,
teamid index hoping that would help but to no avail.

Any advice?  Any more information I need to supply?

Thanks,

Chris