Re: Poor performance due to parallel seq scan on indexed date field

On Wed, Jun 21, 2023 at 2:40 PM Wells Oliver <wells(dot)oliver(at)gmail(dot)com> 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

>