Разница между прямым запросом таблицы и запросом функции, которая возвращает ту же таблицу

Я хотел бы иметь функцию, которая возвращает TABLE. Я знаю, что пользователь может использовать вызов функции при выборе и объединении точно так же, как таблица. Однако сможет ли select/join использовать индексы исходной таблицы, возвращенные функцией TABLE?

Например: будет ли "select id from permitted_resources() where id = 1" совпадать с "select id from resources where id = 5"? (Предполагая, что в столбце идентификатора таблицы ресурсов есть индекс.)

CREATE OR REPLACE FUNCTION permitted_resources()
  RETURNS TABLE (id   int, name varchar(10)) AS
$func$
BEGIN
   RETURN QUERY
   SELECT r.id, r.name from resources r; 
END
$func$  LANGUAGE plpgsql;

postgresql set-returning-functions
person user1409708    schedule 27.08.2020    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Будет ли выбранный идентификатор из allow_resources(), где id = 1, совпадать с идентификатором, выбранным из ресурсов, где id = 5?

Нет, не будет. Функция PL/pgSQL — это черный ящик для оптимизатора.

Если вы хотите добиться чего-то подобного, используйте функцию language sql:

CREATE OR REPLACE FUNCTION permitted_resources()
  RETURNS TABLE (id   int, name varchar(10)) AS
$func$
   SELECT r.id, r.name from resources r; 
$func$  
LANGUAGE sql
stable;

Мы можем проверить это со следующей настройкой:

create table test 
(
  id integer primary key, 
  some_nr integer default random() * 1000 + 1,
  some_date date default current_date,
  some_text text default md5(random()::text)
);

insert into test (id) 
select *
from generate_series(1,1e6);

Теперь создайте одну функцию PL/pgSQL:

create function get_data1()
returns setof test
as
$$
begin
 return query
   select *
   from test;
end;   
$$
language plpgsql
stable;

И функция SQL:

create function get_data2()
returns setof test
as
$$
 select *
 from test;
$$
language sql
stable;

Посмотрим, как выглядят планы выполнения:

explain (analyze)
select *
from get_data1() -- this is the PL/pgSQL function
where id = 1234;

Выдает следующий план выполнения:

Function Scan on get_data1  (cost=0.25..4.75 rows=5 width=44) (actual time=261.033..361.218 rows=1 loops=1)
  Filter: (id = 1234)
  Rows Removed by Filter: 999999
Planning Time: 0.033 ms
Execution Time: 371.302 ms

По-видимому, он сначала извлекает все строки, а затем снова их отбрасывает.

Однако,

explain (analyze)
select *
from get_data2() -- the "SQL" function
where id = 1234;

Выдает следующий план выполнения:

Index Scan using test_pkey on test  (cost=0.42..2.43 rows=1 width=45) (actual time=0.015..0.017 rows=1 loops=1)
  Index Cond: (id = 1234)
Planning Time: 0.119 ms
Execution Time: 0.031 ms

Эта функция больше не упоминается в плане. Неудивительно, что простой выбор дает тот же план:

explain (analyze)
select *
from test
where id = 1234;

Index Scan using test_pkey on test  (cost=0.42..2.43 rows=1 width=45) (actual time=0.014..0.014 rows=1 loops=1)
  Index Cond: (id = 1234)
Planning Time: 0.058 ms
Execution Time: 0.026 ms

Я не знаю, верно ли это для более сложных запросов, но простое соединение между такой функцией и другой таблицей показывает такое же поведение.

person a_horse_with_no_name    schedule 27.08.2020