Re: Index/Seq Scan Problem - Mailing list pgsql-general

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@optushome.com.au>
To: <pgsql-general@postgresql.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