From: | Jesper Pedersen <jesper(dot)pedersen(at)redhat(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: JSON path |
Date: | 2019-11-14 20:46:08 |
Message-ID: | 17beac8b-e4e5-d37d-3412-3cbb2052090d@redhat.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hi,
On 11/14/19 1:04 PM, Tom Lane wrote:
> As of v12, that WITH will get flattened, so that you still end up
> with three invocations of jsonb_path_query_first, as EXPLAIN VERBOSE
> will show you. You could write "WITH foo AS MATERIALIZED ..." to
> prevent that, but then you'll need to stick the WHERE clause inside
> the WITH or you'll end up running jsonb_path_query_first for every
> row of tbl.
>
> With
>
> explain verbose WITH foo AS materialized (select
> id,
> col1,
> col2,
> jsonb_path_query_first(data,'$.lvl1.lvl2.lvl3.lvl4.lvl5') as jsondata
> from tbl where id = 1 )
> select
> col1,
> col2,
> jsondata->'val1'->'text()' as val1,
> jsondata->'val2'->'text()' as val2,
> jsondata->'val3'->'text()' as val3
> from foo;
>
Thanks Tom ! This works :)
I owe you one.
Best regards,
Jesper
From | Date | Subject | |
---|---|---|---|
Next Message | Craig James | 2019-11-14 22:19:51 | Simple DELETE on modest-size table runs 100% CPU forever |
Previous Message | Tom Lane | 2019-11-14 18:04:36 | Re: JSON path |