Thread: 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
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
"Chris Cox" <cjcox@optushome.com.au> writes:
> Found my own problem! I didn't cast the values as bigint on the where.
Right-o. You shouldn't have to do that --- but we're still going round
and round about how to implicitly cast integer constants to the "right"
width. All of the proposals so far have proven to create new problems
:-(
regards, tom lane