Re: CTE Materialization

On Thu, 9 Dec 2021 at 10:29, Paul van der Linden <paul(dot)doskabouter(at)gmail(dot)com>
wrote:

> This one quite nicely explains it:
> https://stackoverflow.com/questions/14897816/how-can-i-prevent-postgres-from-inlining-a-subquery
>

Given indexes applicable to multiple expressions in a WHERE condition, how
does postgres decide which index is most beneficial to use?

The author of that SO post tried to adjust the default statistics target,
presumably to convince postgres to use the faster primary key index,
instead of the slower gist index on the hstore values, but this didn't work.

thanks,
richard

> On Wed, Dec 8, 2021 at 3:14 AM David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
>> On Tue, Dec 7, 2021 at 6:40 PM Дмитрий Иванов <firstdismay(at)gmail(dot)com>
>> wrote:
>>
>>> I beg your pardon.
>>> The problem is more or less clear to me, but the solution is not. What
>>> does the "hack is to add an "offset 0" to the query" suggest? Thank you.
>>>
>>>
>> A subquery with a LIMIT clause cannot have where clause expressions in
>> upper parts of the query tree pushed down it without changing the overall
>> query result - something the planner is not allowed to do. For the hack,
>> since adding an actual LIMIT clause doesn't make sense you omit it, but
>> still add the related OFFSET clause so the planner still treats the
>> subquery as a LIMIT subquery. And since you don't want to skip any rows
>> you specify 0 for the offset.
>>
>> David J.
>>
>>