I don't see evidence of a bug here (so far), but a couple of things are
worthy of mention:
- The estimated number of rows is much higher than the actual. A likely
cause of this under-estimation
is that the columns are correlated. If so, then the estimate may be
improved in PG10+ by collecting
multi-column statistics on (id, time) or (id, time, isValid), but that
doesn't help you on PG9.6
- For the historic partitions that are no longer being updated, you could
cluster the heap by the index,
and possibly disable the bitmap heap scan. If either or both of these
changes improve scan performance
significantly on the 2017 data, consider partitioning by month and
clustering historic monthly partitions.
-----
Jim Finnerty, AWS, Amazon Aurora PostgreSQL
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html