Thanks for your help. It is true we could get rid of it but we still want to use the functional index on the date range as we understand it is supposed to be a better look up - we also have other date range look ups on tables that seem to be degrading. I have found a solution to the problem. The postgres default_statistics_target is 100 and when we upped it to 10000 the estimate was good. We could not have set the default to 10000 on production but there appeared to be no way to change the value for the function index as statistics is set per column. However, in a post answered by Tom Lane in 2012 he gives a way to set the value for the statistics target on the functional index (https://www.postgresql.org/message-id/6668.1351105908%40sss.pgh.pa.us)
Thanks. On Mon, 24 Jan 2022 at 17:43, Michael Lewis <mle...@entrata.com> wrote: > > If interval_end_date is always 1 day ahead, why store it at all? > > Dependencies on a custom stats object wouldn't do anything I don't think > because they are offset. They are 100% correlated, but not in a way that any > of the existing stat types capture as far as I can figure.
