On Mon, Feb 12, 2024 at 3:55 AM Peter Eisentraut <peter@eisentraut.org> wrote:
> Have you checked that the generated queries can use indexes and have
> suitable performance? Do you have example execution plans maybe?
This took longer than expected, but I wrote a long blog post about it
here: https://illuminatedcomputing.com/posts/2024/09/benchmarking-temporal-foreign-keys/
The short answer is that yes we use the index, and the query plan is
reasonable. I compared performance against two alternate
implementations, and range_agg was fastest most of the time. When you
have a lot of invalid FK checks, the implementation in Snodgrass's
book wins, because it can short-circuit the plan and return a false
result without executing most of it. But that seems like an unusual
situation, and we should optimize for mostly-valid FK checks instead.
There are some more experiments I'd like to do (see the end of that
post), but for now I plan to prioritize getting the FOR PORTION OF
patch ready to commit. But if there is anything you'd like to know
more urgently, let me know.
Yours,
--
Paul ~{:-)
pj@illuminatedcomputing.com