When I execute the following two queries, the results differ -- with the
only change being that another table is joined (a 1-1 join that should not
affect the results -- I reduced down a much larger query that was
exhibiting the behavior to what appears to be the cause). I know that
views have some limitations, and two of the relations used are views, so I
belive that that may be the problem, but I want to be sure...
Thanks
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
football=# SELECT play.play_id as play_id, year.correct_picks
as ytd_correct_picks FROM tblweek_correct correct, tblplayer play,
tblyear_correct year WHERE play.play_id = year.play_id AND play.play_id =
correct.play_id AND correct.game_week = 6 AND play.play_id = 4;
play_id | ytd_correct_picks
---------+-------------------
4 | 141
(1 row)
football=# SELECT play.play_id as play_id, year.correct_picks as
ytd_correct_picks FROM tblplayer play, tblyear_correct year WHERE
play.play_id = year.play_id AND play.play_id = 4;
play_id | ytd_correct_picks
---------+-------------------
4 | 47
(1 row)
football=# EXPLAIN SELECT play.play_id as play_id, year.correct_picks as
ytd_correct_picks FROM tblweek_correct correct, tblplayer play,
tblyear_correct year WHERE play.play_id = year.play_id AND play.play_id =
correct.play_id AND correct.game_week = 6 AND play.play_id = 4;
NOTICE: QUERY PLAN:
Aggregate (cost=108.40..108.41 rows=0 width=64)
-> Group (cost=108.40..108.40 rows=1 width=64)
-> Sort (cost=108.40..108.40 rows=1 width=64)
-> Nested Loop (cost=0.00..108.39 rows=1 width=64)
-> Nested Loop (cost=0.00..106.36 rows=1 width=56)
-> Nested Loop (cost=0.00..104.33 rows=1
width=52)
-> Nested Loop (cost=0.00..16.54 rows=1
width=40)
-> Nested Loop (cost=0.00..14.75
rows=1 width=36)
-> Nested Loop
(cost=0.00..13.30 rows=1 width=32)
-> Nested Loop
(cost=0.00..11.52 rows=1 width=28)
-> Nested Loop
(cost=0.00..5.11 rows=1 width=16)
-> Seq Scan
on tblgame game (cost=0.00..2.08 rows=1 width=8)
-> Index
Scan using tblgame_winner_pkey on tblgame_winner winner (cost=0.00..2.01
rows=1 width=8)
-> Index Scan
using tblpick_gameid_playid on tblpick pick (cost=0.00..5.05 rows=3
width=12)
-> Seq Scan on
tblplayer play (cost=0.00..1.35 rows=35 width=4)
-> Seq Scan on tblplayer play
(cost=0.00..1.44 rows=1 width=4)
-> Seq Scan on tblplayer play
(cost=0.00..1.35 rows=35 width=4)
-> Seq Scan on tblpick pick
(cost=0.00..51.24 rows=2924 width=12)
-> Index Scan using tblgame_pkey on tblgame
game (cost=0.00..2.01 rows=1 width=4)
-> Index Scan using tblgame_winner_pkey on
tblgame_winner winner (cost=0.00..2.01 rows=1 width=8)
EXPLAIN
football=# EXPLAIN SELECT play.play_id as play_id, year.correct_picks as
ytd_correct_picks FROM tblplayer play, tblyear_correct year WHERE
play.play_id = year.play_id AND play.play_id = 4;
NOTICE: QUERY PLAN:
Aggregate (cost=101.12..101.12 rows=0 width=32)
-> Group (cost=101.12..101.12 rows=1 width=32)
-> Sort (cost=101.12..101.12 rows=1 width=32)
-> Hash Join (cost=96.27..101.11 rows=1 width=32)
-> Seq Scan on tblgame_winner winner
(cost=0.00..1.72 rows=72 width=8)
-> Hash (cost=96.26..96.26 rows=4 width=24)
-> Hash Join (cost=3.40..96.26 rows=4
width=24)
-> Nested Loop (cost=0.00..91.02 rows=19
width=20)
-> Nested Loop
(cost=0.00..3.23 rows=1 width=8)
-> Seq Scan on tblplayer play
(cost=0.00..1.44 rows=1 width=4)
-> Seq Scan on tblplayer play
(cost=0.00..1.35 rows=35 width=4)
-> Seq Scan on tblpick pick
(cost=0.00..51.24 rows=2924 width=12)
-> Hash (cost=1.86..1.86 rows=86
width=4)
-> Seq Scan on tblgame game
(cost=0.00..1.86 rows=86 width=4)
EXPLAIN
football=# \d tblweek_correct
View "tblweek_correct"
Attribute | Type | Modifier
---------------+---------+----------
play_id | integer |
game_week | integer |
correct_picks | integer |
View definition: SELECT play.play_id, game.game_week, count(*) AS
correct_picks FROM tblgame game, tblpick pick, tblgame_winner winner,
tblplayer play WHERE ((((pick.play_id = play.play_id) AND (game.game_id =
pick.game_id)) AND (pick.team_id = winner.team_id)) AND (game.game_id =
winner.game_id)) GROUP BY play.play_id, game.game_week;
football=# \d tblyear_correct
View "tblyear_correct"
Attribute | Type | Modifier
---------------+---------+----------
play_id | integer |
correct_picks | integer |
View definition: SELECT play.play_id, count(*) AS correct_picks FROM
tblgame game, tblpick pick, tblgame_winner winner, tblplayer play WHERE
((((pick.play_id = play.play_id) AND (game.game_id =
pick.game_id)) AND (pick.team_id = winner.team_id)) AND (game.game_id =
winner.game_id)) GROUP BY play.play_id;
football=# \d tblplayer play
Table "tblplayer"
Attribute | Type | Modifier
---------------+-------------+---------------------------------------------------------
play_id | integer | not null default
nextval('tblplayer_play_id_seq'::text)
play_name | varchar(30) | not null
play_username | varchar(16) | not null
play_password | varchar(16) | not null
play_online | boolean | default 'f'
Indices: idx_play_username,
tblplayer_pkey
\d: extra argument 'play' ignored
football=# select version();
version
--------------------------------------------------------
PostgreSQL 7.0.2 on i386-pc-bsdi3.1, compiled by gcc2
(1 row)
