Re: Non-Stored Generated Columns

On Thu, Feb 29, 2024 at 11:58 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> On Thu, 2024-02-29 at 10:55 +0100, Dominique Devienne wrote:
> > On Thu, Feb 29, 2024 at 10:03 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
> wrote:
> > > You could use conditional indexes, but then you have to make sure that
> > > the optimizer knows it can use these indexes.
> >
> > I'm not following. Are you saying the planner is not good at that on its
> own?
> > I need to do something from my end???
>
> I wasn't sure, but now I tested: a conditional index can also be used
> by a cascading delete or update. So that's not a worry.
>

Great. Thanks a bunch for confirming for me!

> > Something I maybe didn't make clear. The XArc virtual columns are never
> accessed.
>
> Yes, they are. The query planner considers all indexes.
>

Not sure if I'm missing some terminology or something, to understand your
point.
The XArc columns are never explicitly SELECT'd by our code.
Nor are they used in any of our WHERE clauses.

So yes, the additional indexes our PFK pattern introduces make the pool of
indexes considered larger.
But I'm sure indexes on columns "not used at all in a statement" are
eliminated early and easily/cheaply,
w/o even getting into more complex consideration like statistics and co.
Aren't they? Thanks, --DD