> However, I'm skeptical that any problem actually remains in
> real-world use cases.
Hello Tom,
We also had some issues with planning and get_actual_variable_range(). We
actually found some interesting behaviour that probably requires an eye with
better expertise in how the planner works.
For
Hello everyone,
> Apparently, the planner isn't reusing the data boundaries across alternative
> plans. It would be nicer if the planner remembered each column boundaries
> for later reuse (within the same planner execution).
I've written a very naive (and crappy) patch to show how adding
> On 30-06-2021, at 16:56, Manuel Weitzman wrote:
>
> One way in which I see possible to share this kind of information (of
> extremal values) across RestrictInfos is to store the known variable
> ranges in PlannerInfo (or within a member of such struct), which seems
> to be
> On 29-06-2021, at 15:43, Tom Lane wrote:
>
> Manuel Weitzman writes:
>>> On 20-06-2021, at 17:06, Tom Lane wrote:
>>> So ... the reason why there's not caching of get_actual_variable_range
>>> results already is that I'd supposed it wouldn't be necessary
> On 20-06-2021, at 17:06, Tom Lane wrote:
>
> So ... the reason why there's not caching of get_actual_variable_range
> results already is that I'd supposed it wouldn't be necessary given
> the caching of selectivity estimates that happens at the RestrictInfo
> level. I don't have any
> On 08-07-2021, at 17:13, Manuel Weitzman wrote:
>
> Option A: Use a common table expression to "force" the usage of
> test_json_data_idx
>
>WITH json_matching_rows AS (
>SELECT t.*
>FROM test ti
>WHERE t.json_data @> '
book title and tweak the
query to use it.
This can also be a composite index (to have the values sorted by id
already) and partial (to only include rows where "existe" is true)
CREATE INDEX test_json_data_composite_idx
ON test
USING BTREE ((json_data->'book'->>'title'), id DESC)
WHERE (existe);
SELECT t.*
FROM test t
WHERE t."existe" IS true
and t.json_data->'book'->>'title' = 'In Search of Lost Time'
ORDER BY t."id" DESC
LIMIT 100 OFFSET 0;
Be aware that partial indexes don't support HOT updates.
I hope this reply helps you.
Best regards,
Manuel Weitzman
> On 08-07-2021, at 17:13, Manuel Weitzman wrote:
>
> I'd recommend you using cursors for pagination in general (I know it
> may not be possible for you, just wanted to explain as it could be
> useful).
By the way, I mean cursor pagination as the general concept.
I'm no
> 1. create_join_clause doesn't trouble to look for commuted
> equivalents, which perhaps is penny-wise and pound-foolish.
> The cost of re-deriving selectivity estimates could be way
> more than the cost of checking this.
Agreed.
> 2. Although these look like they ought to be equivalent to the
