Optimizations for INSERT

[steve-chavez]

While working on #2672, I noticed some optimizations we can do for INSERTs.

Our current query on POST /complex_items Prefer: return=representation is:

WITH pgrst_source AS (
  WITH
  pgrst_payload AS (SELECT '[{"id": 4, "name": "Vier"}, {"id": 5, "name": "Funf", "arr_data": null}, {"id": 6, "name": "Sechs", "arr_data": [1, 2, 3], "field-with_sep": 6}]'::json AS json_data),
  pgrst_body AS ( SELECT CASE WHEN json_typeof(json_data) = 'array' THEN json_data ELSE json_build_array(json_data) END AS val FROM pgrst_payload)
  INSERT INTO "test"."complex_items"("arr_data", "field-with_sep", "id", "name")
  SELECT "arr_data", "field-with_sep", "id", "name"
  FROM json_to_recordset ((SELECT val FROM pgrst_body)) AS _ ("arr_data" integer[], "field-with_sep" integer, "id" bigint, "name" text)
  RETURNING "test"."complex_items".*
)
SELECT
  '' AS total_result_set,
  pg_catalog.count(_postgrest_t) AS page_total,
  array[]::text[] AS header,
  coalesce(json_agg(_postgrest_t), '[]')::character varying AS body,
  nullif(current_setting('response.headers', true), '') AS response_headers,
  nullif(current_setting('response.status', true), '') AS response_status
FROM (SELECT "complex_items".* FROM "pgrst_source" AS "complex_items") _postgrest_t;

Optimizations

• pg_catalog.count(_postgrest_t) AS page_total is always included, but if remove it, this grants us an increase in 12% TPS. AFAICT this is only required for:

  • PUT
  • When using application/vnd.pgrst.object+json
  • When using Prefer: count=exact
  • Cannot be done. See below.

• Change the CTEs(pgrst_payload, pgrst_body) to LATERAL instead. This increases TPS by 6%. I got the hint of this optimization from limits don't seem to apply directly to the underlying query, causing poor performance #1652 (comment).

  • Done on perf: use LATERAL instead of CTE for json body #2677

• Use json_to_recordset or json_to_record according to Content type header and inline the json payload as a paremeter. This grants us 31% increase in TPS.

  • Basically removes the internal CTEs(pgrst_payload, pgrst_body) and does FROM json_to_recordset ('[{"id": 4)..') directly.
  • It would require adding an application/vnd.pgrst.array+json media type.
  • It sounds worth it because it's a large increase in TPS.

[steve-chavez]

pg_catalog.count(_postgrest_t) AS page_total is always included, but if remove it, this grants us an increase in 12% TPS. AFAICT this is only required for:

The above cannot be done. We rely on counting the page total even when not using any header. This test proves it:

postgrest/test/spec/Feature/Query/UpdateSpec.hs

Lines 61 to 70 in 2c94455

	context "in a nonempty table" $ do
	it "can update a single item" $ do
	patch "/items?id=eq.2"
	[json| { "id":42 } |]
	`shouldRespondWith`
	""
	{ matchStatus = 204
	, matchHeaders = [ matchHeaderAbsent hContentType
	, "Content-Range" <:> "0-0/*" ]
	}

See 0-0. Sharing my attempt on steve-chavez@60a641c, which got 24 test failures.

[steve-chavez]

Use json_to_recordset or json_to_record according to Content type header and inline the json payload as a paremeter. This grants us 31% increase in TPS.

Since the query change on #2677, the above optimization no longer grants such increase. It's only about 10% in TPS now.