Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.100.0.2.22\))
Subject: Re: Strange execution plan
From: Manuel Weitzman <manuelweitzman@gmail.com>
In-Reply-To: <9F9D5E5E-6C58-4815-A20A-84216B788AD1@gmail.com>
Date: Thu, 8 Jul 2021 22:00:54 -0400
Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Content-Transfer-Encoding: 7bit
Message-Id: <EE6D94D6-2F72-4B51-9ACE-750230596A54@gmail.com>
References: <17a8539f896.10fc10eb5173534.6306217358025168810@car-expresso.com>
 <9F9D5E5E-6C58-4815-A20A-84216B788AD1@gmail.com>
To: Joel Frid <joel.frid@car-expresso.com>
Archived-At: 
 <https://www.postgresql.org/message-id/EE6D94D6-2F72-4B51-9ACE-750230596A54%40gmail.com>
Precedence: bulk


> On 08-07-2021, at 17:13, Manuel Weitzman <manuelweitzman@gmail.com> 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":"In Search of Lost Time"}}'
>    )
>    SELECT t.*
>    FROM json_matching_rows t
>    WHERE  t."existe" IS true
>    ORDER BY t."id" DESC
>    LIMIT 100 OFFSET 0;
> 

The first query line should be
    WITH MATERIALIZED json_matching_rows AS (

I had forgotten that Postgres 12 removed the optimization barrier on
common table expressions.
To introduce it again the MATERIALIZED clause is needed.

Apparently I need to work on reviewing my emails properly before
sending them.


Best regards,
Manuel Weitzman