Re: [PROPOSAL] Use SnapshotAny in get_actual_variable_range

> 29 апр. 2017 г., в 17:34, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> написал(а):
>
> Dmitriy Sarafannikov <dsarafannikov(at)yandex(dot)ru> writes:
>>> Maybe we need another type of snapshot that would accept any
>>> non-vacuumable tuple. I really don't want SnapshotAny semantics here,
>
>> If I understood correctly, this new type of snapshot would help if
>> there are long running transactions which can see this tuples.
>> But if there are not long running transactions, it will be the same.
>> Am i right?
>
> Right. You haven't shown us much about the use-case you're concerned
> with, so it's not clear what's actually needed.

The use case is nearly the same as the way to reproduce the problem described in the first letter. It’s an OLTP database with short mostly read-only queries (~ 6k rps). Every 10 minutes new data is inserted (~5-10% of rows in polygon_table) and old is deleted (~ 5-10% or rows in polygon_table). Insert and delete are made in different transactions. Until the vacuum after delete is finished the planning time is two orders of magnitude is higher than usually. If we use prepared statements the problem doesn’t reproduce since planning is not actually done.

>
>> And what about don’t fetch actual min and max values from indexes
>> whose columns doesn’t involved in join?
>
> We don't fetch that info unless we need it.

We used to think that it’s actually not so (there was a problem in our test case), but we rechecked and it is, the planner doesn’t find min and max in unneeded for join indexes.

>
> I'm not entirely certain, but there could be cases where a single
> planning cycle ends up fetching that data more than once. (There's
> caching at the RestrictInfo level, but that might not be enough.)
> So a line of thought that might be worth looking into is adding a
> lower layer of caching to make sure it's not done more than once per
> plan. Again, whether this saves anything would depend a lot on
> specific use-cases.
>
> regards, tom lane