Hacker News Reader

Comment by bluedino | Hacker News Reader

In articles like these, I wish they would go a little deeper and says why the system does what it does. Perhaps the default behavior is geared towards low-memory servers, or avoids disk writes, or the data is usually expected to be of a certain type.

Perhaps the system "isn't very smart" like it says, or it was originally intended for other purposes.

View on HN

I definitely agree with you, but in this case the Postgres team itself frames the suboptimal behavior as such:

> However, PostgreSQL's planner is currently not very smart about such cases.

(From https://www.postgresql.org/docs/10/indexes-index-only-scans....)

I suspect this is just a rough edge. Many types of functions may not be consistent, and therefore would need to be re-executed on the original data. Perhaps some of the planner functionality here predates function notation like `IMMUTABLE`.

I don't know the internals here, but I think I can make a plausible guess here. The index-only scan is generally triggered only when every column in your query is contained in the relevant index. In this case the index is a functional index, so it's not only plain columns in there. The function needs the data column, but the functional index already stores the result of the function. To the query planner it looks like the query needs the data column, while it actually doesn't need it.

This is a bit of an uncommon case that would need special handling in the query planner. So it's not that odd that it is not implemented yet, though I would suspect that it'll be added at some point.

Reply on news.ycombinator.com