Ускорить простой запрос с CTE

0

1

Использую PGSQL, есть простой запрос,

	
	EXPLAIN (ANALYZE,BUFFERS)
	WITH data_to_cte AS (
		SELECT
			unnest[1]::integer account
		,	unnest[2]::text user_id
		,	unnest[3]::integer target_id
		FROM
			(
				SELECT
					unnest('{ "{ 7267996,4,96394594 }" }'::text[])::text[]
			) f
	)
	SELECT
		"DocumentsId",
		data_to_cte.*
	FROM
		data_to_cte
	JOIN
		"Documents"
			on "Account" = account AND 
			"Target" = target_id and
			"User" = user_id  AND
			"User" IS NOT NULL

план такой

Nested Loop  (cost=0.58..8.68 rows=1 width=48) (actual time=23.352..23.353 rows=0 loops=1)
  Buffers: shared hit=16730
  ->  Result  (cost=0.00..0.04 rows=1 width=32) (actual time=0.008..0.012 rows=1 loops=1)
        ->  ProjectSet  (cost=0.00..0.02 rows=1 width=32) (actual time=0.004..0.007 rows=1 loops=1)
              ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
  ->  Index Scan using "iDocuments-AccountUser" on "Documents"  (cost=0.58..8.61 rows=1 width=28) (actual time=23.334..23.334 rows=0 loops=1)
        Index Cond: (("Account" = (((((unnest('{"{ 7267996,4,96394594 }"}'::text[])))::text[]))[1])::integer) AND ("User" = ((((unnest('{"{ 7267996,4,96394594 }"}'::text[])))::text[]))[2]))
        Filter: ((((((unnest('{"{ 7267996,4,96394594 }"}'::text[])))::text[]))[3])::integer = "Target")
        Rows Removed by Filter: 19163
        Buffers: shared hit=16730
Planning Time: 0.318 ms
Execution Time: 23.397 ms

Хочется чтобы использовался такой индекс

CREATE INDEX "iDocuments-AccountTarget"
	ON "Documents" USING btree
	("Account" NULLS LAST, "Target" NULLS LAST, "User" NULLS LAST)
	WHERE ("User" IS NOT NULL);

Что нужно сделать, возможно переписать запрос?

Например, если указать напрямую Target, то используется нужный индекс

	EXPLAIN (ANALYZE,BUFFERS)
	WITH data_to_cte AS (
		SELECT
			unnest[1]::integer account
		,	unnest[2]::text user_id
		,	unnest[3]::integer target_id
		FROM
			(
				SELECT
					unnest('{ "{ 7267996,4,96394594 }" }'::text[])::text[]
			) f
	)
	SELECT
		"DocumentsId",
		data_to_cte.*
	FROM
		data_to_cte
	JOIN
		"Documents"
			on "Account" = account AND 
			"Target" = 4 and
			"User" = user_id  AND
			"User" IS NOT NULL

Похожие темы