Query for listing expired amulets seems to degrage to seq scan

[moritzkiefer-da]

Sometimes (when the acs grows large?) the query for listing expired amulets seems to degrade to a sequential scan which then is slow as expected.

The approach we currently use for filtering out dead parties more generally just won't scale as even the best query plan we can get will do a linear filter over the expired amulets to filter out all the ones for dead parties and that list is just gonna keep growing.

Haven't thought too much about approaches for improving it. Adding the party to the index would help I guess but I don't think this magically makes a bunch of party not in increasinglyLongList scale well.

A perhaps better option would be that we have a "unavailable counter party" flag on each contract row that we can set. We then make the index only include contracts with that flag set to false which seems like it would actually give us the right query behavior.

That approach should also work generally for unavailable counter party handling maybe.

[rautenrieth-da]

For reference, here's how the query plan looks like if Postgres doesn't decide to use the sequential scan. At some point postgres decided to use this (better) plan, and the query run time immediately dropped by orders of magnitude. The data can't have changed in such a short time, so it's almost certainly the query planner estimates being off. The above plan used a 30sec sequential scan returning 80 rows, where it could have used an index instead.

Apart from the above suggestion, we could also look into why the estimates are off and add statistics.

[meiersi-da]

A perhaps better option would be that we have a "unavailable counter party" flag on each contract row that we can set. We then make the index only include contracts with that flag set to false which seems like it would actually give us the right query behavior.

I'm worried about that, as it leads to large amounts of updates when a party's availability flips in one or the other direction. Furthermore, I expect that we'll have larger numbers of unavailable parties as time goes on, which likely requires maintaining them in their own table and doing an anti-join.

I'd suggest to tread slightly slower along the following steps:

1. Work on understanding why Postgres switches to a SEQ scan; and how to avoid it. It might well be statistics and auto-analyze/vaccum config.
2. Experiment with the case where the unavailable party data is in a separate table.
   • I suspect that including the counter-parties in the index as an INCLUDE column might be quite helpful to avoid actual table reads for unavailable counter-parties (and to guide the planner)
3. Consider whether we should/can organize our trigger code using a temporary table that contains the actual work with the long-term unavailable parties filtered out. This table would be maintained by an outer loop in the trigger. This will require filtering the tasks that are done on read, but it might turn out that this is more well-behaved than using indices.
   • I'm not a big fan of this option, as it is complex and still an approximation. I wanted to mention it though, so we don't forget about it.