On Thu, 2024-02-29 at 10:55 +0100, Dominique Devienne wrote: > On Thu, Feb 29, 2024 at 10:03 AM Laurenz Albe <laurenz.albe@cybertec.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