Re: Extract numeric filed in JSONB more effectively - Mailing list pgsql-hackers

From Andy Fan
Subject Re: Extract numeric filed in JSONB more effectively
Date
Msg-id 87ttk0lgcx.fsf@163.com
Whole thread Raw
In response to Re: Extract numeric filed in JSONB more effectively  (Andy Fan <zhihuifan1213@163.com>)
List pgsql-hackers
Andy Fan <zhihuifan1213@163.com> writes:

> Here is latest version, nothing changed besides the rebase to the latest
> master. The most recent 3 questions should be addressed.
>
> - The error message compatible issue [1] and the Peter's answer at [2].
> - Peter's new question at [2] and my answer at [3].
>
> Any effrot to move this patch ahead is welcome and thanks all the people
> who provided high quaility feedback so far, especially chapman!
>
> [1] https://www.postgresql.org/message-id/87r0hmvuvr.fsf@163.com
> [2]
> https://www.postgresql.org/message-id/8102ff5b-b156-409e-a48f-e53e63a39b36%40eisentraut.org
> [3] https://www.postgresql.org/message-id/8734t6c5rh.fsf%40163.com

rebase to the latest master again.

commit bc990b983136ef658cd3be03cdb07f2eadc4cd5c (HEAD -> jsonb_numeric)
Author: yizhi.fzh <yizhi.fzh@alibaba-inc.com>
Date:   Mon Apr 1 09:36:08 2024 +0800

    Improve the performance of Jsonb numeric/bool extraction.
    
    JSONB object uses a binary compatible numeric format with the numeric
    data type in SQL. However in the past, extracting a numeric value from a
    JSONB field still needs to find the corresponding JsonbValue first,
    then convert the JsonbValue to Jsonb, and finally use the cast system to
    convert the Jsonb to a Numeric data type. This approach was very
    inefficient in terms of performance.
    
    In the current patch, It is handled that the JsonbValue is converted to
    numeric data type directly.  This is done by the planner support
    function which detects the above case and simplify the expression.
    Because the boolean type and numeric type share certain similarities in
    their attributes, we have implemented the same optimization approach for
    both.  In the ideal test case, the performance can be 2x than before.
    
    The optimized functions and operators includes:
    1. jsonb_object_field / ->
    2. jsonb_array_element / ->
    3. jsonb_extract_path / #>
    4. jsonb_path_query
    5. jsonb_path_query_first

-- 
Best Regards
Andy Fan


Attachment

pgsql-hackers by date:

Previous
From: Thomas Munro
Date:
Subject: Solaris tar issues, or other reason why margay fails 010_pg_basebackup?
Next
From: Michael Paquier
Date:
Subject: Re: ALTER TABLE SET ACCESS METHOD on partitioned tables