On Wed, Jun 21, 2023 at 2:40 PM Wells Oliver <wells.oliver@xxxxxxxxx> wrote:
It's just this.CREATE OR REPLACE VIEW vw_pitches AS
SELECT
p.year,
p.game_id,
p.game_date,
p.game_level,
...
from synergy.pitches as p
join alias.identity as idpitcher
on p.pitcher_identity_id = idpitcher.identity_id
left join alias.identity as idcatcher
on p.catcher_identity_id = idcatcher.identity_id
left join alias.identity as idbatter
on p.batter_identity_id = idbatter.identity_id;
Is there a reason the join to idpitcher is not a left join, like the other joins to alias.identity are? If it were, then this join could also be removed by the planner, and then you wouldn't need access to p.pitcher_identity_id which means it should be able to use an index-only scan on the game_date index.
Alternatively, if you made a multicolumn index over (game_date, pitcher_identity_id), then it could use that index as an index-only scan even with the existing view definition.
Cheers,
Jeff
