Re: SQL:2011 application time

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