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.40rows=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.12rows=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 |
notnull default
nextval('tblplayer_play_id_seq'::text)play_name | varchar(30) | not nullplay_username | varchar(16) | not
nullplay_password| varchar(16) | not nullplay_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)