From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Dominique Devienne <ddevienne@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Non-Stored Generated Columns
Date: Thu, 29 Feb 2024 10:03:41 +0100
Message-ID: <dec069f5b59bb4565fbed5f4d8cb704c4c2fcdb5.camel@cybertec.at> (raw)
In-Reply-To: <CAFCRh--HTTob61MKzvi8NocOBoEECnOA=ZdR94tN6X=2KrSEig@mail.gmail.com>
References: <CAFCRh-_DodmoxMAS5r1sh9sAeAKDKReDiSKYH=rMLyEE3TpzQA@mail.gmail.com>
<c634fdaa0e1795a6bf5fa6528a5750fc1c54c127.camel@cybertec.at>
<CAFCRh-_2Pz0ZE9QH4gr9qYQW9bEFsnXefZxryJyeD-AgcOQAsg@mail.gmail.com>
<2425903.1709147504@sss.pgh.pa.us>
<CAFCRh--HTTob61MKzvi8NocOBoEECnOA=ZdR94tN6X=2KrSEig@mail.gmail.com>
On Thu, 2024-02-29 at 08:55 +0100, Dominique Devienne wrote:
> On Wed, Feb 28, 2024 at 8:11 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Dominique Devienne <ddevienne@gmail.com> writes:
> > > Views can have foreign-keys?
> >
> > Surely you'd put the FK on the underlying table.
>
> Again, the FKs are on the *generated* columns. So
>
> > > Generated view columns be indexed?
> >
> > [...[ it's hard to see much use-case here
>
> The use-case is Polymorphic Foreign Key (aka PFKs).
>
> For NULL'able PFKs, you have two read-write concrete columns, the ID
> and the CODE (aka TYPE) of the relation.
> Then you have 1 generated column (that I call XArc) per possible CODE/TYPE
> value (for that specific relation), which is either ID or NULL, depending
> on the CODE.
> And the "regular" FK is on that generated (and ideally Non-Stored, the
> whole point of this thread), since it points to a single table now.
> And since these FKs are CASCADE'ing, you want them INDEX'ed of course.
I concede that that is a borderline use case.
But you may as well have the foreign key columns as actual columns
(or as stored generated columns).
If that's a handful or so, it shouldn't be a big problem.
If you have hundred types (hundred referenced tables), you'd end up
with hundreds of indexes, and that already looks like a very bad idea
(both DML and query planning time will be affected).
Polymorphic Foreign Keys are nigh impossible to model well in SQL,
and I doubt that non-stored generated columns will solve that.
> For NOT NULL PFKs, that more of a PITA [...]
See?
> So has Ron says. If SQLite and Oracle has them, that's not an accident.
Oracle has a lot of things that are not enviable...
I am sure there are some use cases for "virtual" generated columns, and
I don't think that a patch that tries to implement them will be rejected
out of hand. It's just that I don't think it is a very important feature.
> PS: Since we are on this subject, I'd like to take an opportunity to
> ask a question I've had for a long time, but didn't have time to research.
> For a given PFK, all its XArc columns are mutually-exclusive (by design,
> see above). So their indexes are full of NULLs.
> Are the NULLs stored in these indexes, consuming extra space? In our use
> case, we don't care about NULLs.
> So should be use an expression index to explicitly not index the NULLs?
> The sum of indexes for a given PFK should in theory be the same as a
> single index, whatever the cardinality of the PFK, "logically".
Yes, NULLs are stored in indexes, just like everything else.
You could use conditional indexes, but then you have to make sure that
the optimizer knows it can use these indexes.
The sum of the sizes of these indexes shouldn't exceed the size of an
unconditional index by much, but they would still be more expensive:
each statement would have to look through all the indexes to decide
which ones it can use and which ones not.
Yours,
Laurenz Albe
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: laurenz.albe@cybertec.at, ddevienne@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: Non-Stored Generated Columns
In-Reply-To: <dec069f5b59bb4565fbed5f4d8cb704c4c2fcdb5.camel@cybertec.at>
References: <CAFCRh-_DodmoxMAS5r1sh9sAeAKDKReDiSKYH=rMLyEE3TpzQA@mail.gmail.com> <c634fdaa0e1795a6bf5fa6528a5750fc1c54c127.camel@cybertec.at> <CAFCRh-_2Pz0ZE9QH4gr9qYQW9bEFsnXefZxryJyeD-AgcOQAsg@mail.gmail.com> <2425903.1709147504@sss.pgh.pa.us> <CAFCRh--HTTob61MKzvi8NocOBoEECnOA=ZdR94tN6X=2KrSEig@mail.gmail.com> <dec069f5b59bb4565fbed5f4d8cb704c4c2fcdb5.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox