> > Queries stop getting re-optimized after 5 times, unless better plans are to > > be had. In the absence of schema changes or changing search path why is > > the snapshot needed? > > The snapshot has little to do with the query plan, usually. It's about > what view of the database the executed query will see, and particularly > about what view the parameter input functions will see, if they look. > > You could maybe argue that immutable input functions shouldn't need > snapshots, but there are enough not-immutable ones that I don't think > that gets you very far. In any case, we'd still need a snapshot for > query execution. The set of queries that could possibly never need > a snapshot at all is probably not large enough to be interesting.
I would think that the vast majority of bind variables in customer queries would involve built-in data types whose input functions are immutable. As far as the "view of the database the executed query will see" goes, either the database itself changes, which should trigger catcache invalidations, that can be accounted for, or the "view" changes as in searchpath which can be tested for. I would think the optimization would be applicable most of the time. It isn't the number of obscure user created non-immutable input functions that exist. It is the frequency with which immutable input functions are used in real app's. This is a 50% performance improvement in point lookups on larger boxes.
