Horton
Menu

Re: SQL/JSON: functions

Nikita Glukhov <n.gluhov@postgrespro.ru>

View thread
From: Nikita Glukhov <n.gluhov@postgrespro.ru>
To: Oleg Bartunov <obartunov@postgrespro.ru>, PostgreSQL mailing lists <pgsql-hackers@postgresql.org>
Cc: Alexander Korotkov <a.korotkov@postgrespro.ru>, Andres Freund <andres@anarazel.de>, Michael Paquier <michael@paquier.xyz>, Simon Riggs <simon@2ndquadrant.com>, Andrew Dunstan <andrew.dunstan@2ndquadrant.com>, Pavel Stehule <pavel.stehule@gmail.com>
Date: 2018-06-28T00:18:03Z
Lists: pgsql-hackers

Attachments

On 15.03.2018 20:04, Nikita Glukhov wrote:
> Attached 13th version of the patches:
>
> * Subtransactions in PG_TRY/CATCH in ExecEvalJsonExpr() were made unconditional,
>   regardless of the volatility of expressions.
>
> * PG_TRY/CATCH in ExecEvalExprPassingCaseValue() was removed along with the
>   entire function.


Attached 15th version of the patches:
  * disabled parallel execution of SQL/JSON query functions when internal
    subtransactions are used (if ERROR ON ERROR is not specified)
  * added experimental optimization of internal subtransactions (see below)


The new patch #14 is an experimental attempt to reduce overhead of
subtransaction start/commit which can result in 2x-slowdown in the simplest
cases.  By the idea of Alexander Korotkov, subtransaction is not really
committed if it has not touched the database and its XID has not been assigned
(DB modification is not expected in type casts functions) and then can be reused
when the next subtransaction is started.  So, all rows in JsonExpr can be
executed in the single cached subtransaction.  This optimization really helps
to reduce overhead from 100% to 5-10%:

-- without subtransactions
=# EXPLAIN ANALYZE
    SELECT JSON_VALUE('true'::jsonb, '$' RETURNING boolean ERROR ON ERROR)
    FROM generate_series(1, 10000000) i;
  ...
  Execution Time: 2785.410 ms

-- cached subtransactions
=# EXPLAIN ANALYZE
    SELECT JSON_VALUE('true'::jsonb, '$' RETURNING boolean)
    FROM generate_series(1, 10000000) i;
  ...
  Execution Time: 2939.363 ms

-- ordinary subtransactions
=# EXPLAIN ANALYZE
    SELECT JSON_VALUE('true'::jsonb, '$' RETURNING boolean)
    FROM generate_series(1, 10000000) i;
  ...
  Execution Time: 5417.268 ms


But, unfortunately, I don't believe that this patch is completely correct,
mainly because the behavior of subtransaction callbacks (and their expectations
about subtransaction's lifecycle too) seems unpredictable to me.



Even with this optimization, internal subtransactions still have one major
drawback -- they disallow parallel query execution, because background
workers do not support subtransactions now.  Example:

=# CREATE TABLE test_parallel_json_value AS
    SELECT i::text::jsonb AS js FROM generate_series(1, 5000000) i;
CREATE TABLE

=# EXPLAIN ANALYZE
    SELECT sum(JSON_VALUE(js, '$' RETURNING numeric ERROR ON ERROR))
    FROM test_parallel_json_value;
                                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
  Finalize Aggregate  (cost=79723.15..79723.16 rows=1 width=32) (actual time=455.062..455.062 rows=1 loops=1)
    ->  Gather  (cost=79722.93..79723.14 rows=2 width=32) (actual time=455.052..455.055 rows=3 loops=1)
          Workers Planned: 2
          Workers Launched: 2
          ->  Partial Aggregate  (cost=78722.93..78722.94 rows=1 width=32) (actual time=446.000..446.000 rows=1 loops=3)
                ->  Parallel Seq Scan on t  (cost=0.00..52681.30 rows=2083330 width=18) (actual time=0.023..104.779 rows=1666667 loops=3)
  Planning Time: 0.044 ms
  Execution Time: 456.460 ms
(8 rows)

=# EXPLAIN ANALYZE
    SELECT sum(JSON_VALUE(js, '$' RETURNING numeric))
    FROM test_parallel_json_value;
                                                      QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
  Aggregate  (cost=144347.82..144347.83 rows=1 width=32) (actual time=1381.938..1381.938 rows=1 loops=1)
    ->  Seq Scan on t  (cost=0.00..81847.92 rows=4999992 width=18) (actual time=0.076..309.676 rows=5000000 loops=1)
  Planning Time: 0.082 ms
  Execution Time: 1384.133 ms
(4 rows)


-- 
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. JSON_TABLE: Add support for NESTED paths and columns

  2. Add basic JSON_TABLE() functionality

  3. Add SQL/JSON query functions

  4. Add soft error handling to some expression nodes

  5. Adjust populate_record_field() to handle errors softly

  6. Refactor code used by jsonpath executor to fetch variables

  7. Add more SQL/JSON constructor functions

  8. SQL/JSON: support the IS JSON predicate

  9. SQL/JSON: add standard JSON constructor functions

  10. Improve readability of SQL/JSON documentation.

  11. Documentation for SQL/JSON features

  12. RETURNING clause for JSON() and JSON_SCALAR()

  13. SQL JSON functions

  14. SQL/JSON query functions

  15. IS JSON predicate

  16. SQL/JSON constructors

  17. Common SQL/JSON clauses

  18. Doc: remove obsolete entries in table of BRIN strategy numbers.

  19. Doc: standardize markup a bit more.

  20. Make pg_regress.c unset PGDATABASE during make installcheck.

  21. Fix handling of GENERATED columns in CREATE TABLE LIKE INCLUDING DEFAULTS.