Re: Index/Seq Scan Problem

Found my own problem! I didn't cast the values as bigint on the where.

Sorry to waste your time!

Chris

----- Original Message -----
From: "Chris Cox" <cjcox(at)optushome(dot)com(dot)au>
To: <pgsql-general(at)postgresql(dot)org>
Sent: Monday, April 14, 2003 11:42 AM
Subject: [GENERAL] Index/Seq Scan Problem

> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster