current_schema will not use an text index ?

Поиск
Список
Период
Сортировка
От Marcos Pegoraro
Тема current_schema will not use an text index ?
Дата
Msg-id CAB-JLwYQYxuyzT63rqE13nQ5LS5EM0CoftuGQy3+xUt7+6PUaw@mail.gmail.com
обсуждение исходный текст
Ответы Re: current_schema will not use an text index ?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-hackers
On a multi tenant server, with hundreds of schemas with same structure, I have an audit table shared with all of them. When any record is deleted I add on this table tenant, table and PK values, just that. Something like this:

drop table if exists audit;
create table audit(id serial primary key,
customer_schema text, --here is the problem, a text column.
table_name text,
ins_datetime timestamp default current_timestamp,
pk integer);

--An index for searching
drop index if exists public.audit_customer_table_datetime;
create index audit_customer_table_datetime on audit(customer_schema,table_name,ins_datetime);

--A trigger to insert when a customer deletes a record
create function table_delete() returns trigger language plpgsql as $$ begin
insert into audit(customer_schema, table_name, pk)
select tg_table_schema, tg_table_name, (row_to_json(OLD.*)->>(tg_argv[0]))::bigint; return old; end;

--And now I insert some records for testing. My table has some millions, but for now I´m inserting 100.000 only.
insert into audit(customer_schema,table_name,ins_datetime,pk)
select customer_schema, table_name, current_timestamp + (rn||'seconds')::interval, random()*50000 from generate_series(1,5) as g(g) inner join (select row_number() over () * random() rn, relname, relnamespace::regnamespace::text
from pg_class where relkind = 'r' and relnamespace::regnamespace::text !~ 'pg_|information_schema') x(rn, customer_schema, table_name) on true;

Until version 11 my select was using that index correctly. Then I´ve upgraded to 14.1, then ...

--Application sets search_path to a schema.
set search_path to cust_0298, public;

explain analyze select customer_schema, pk from audit where customer_schema = current_schema and table_name = any('{this_table,that_table,other_table,just_table,more_one_table,last_table}'::text[]) and ins_datetime > '2022/01/22 10:00';
QUERY PLAN
Gather  (cost=1000.00..4167.30 rows=14 width=4) (actual time=24.178..27.117 rows=0 loops=1)
  Workers Planned: 1
  Workers Launched: 1
  ->  Parallel Seq Scan on audit  (cost=0.00..3165.90 rows=8 width=4) (actual time=21.909..21.909 rows=0 loops=2)
        Filter: ((ins_datetime > '2022-01-22 10:00:00'::timestamp without time zone) AND (customer_schema = CURRENT_SCHEMA) AND (table_name = ANY ('{this_table,that_table,other_table,just_table,more_one_table,last_table}'::text[])))
        Rows Removed by Filter: 66262
Planning Time: 0.105 ms
Execution Time: 27.135 ms

hmm, did not use that index. Tried casting current_schema or trying any function which returns text but has no effect.
where customer_schema = Current_Schema::text 
where customer_schema = substring(current_schema from 1 for 50)
where customer_schema = Left(current_schema,50)

The only way I have success to use that index was when I tried
where customer_schema = split_part(current_setting('search_path'),',',1)
QUERY PLAN
Bitmap Heap Scan on audit  (cost=26.68..78.56 rows=14 width=4) (actual time=0.043..0.043 rows=0 loops=1)
  Recheck Cond: ((customer_schema = split_part(current_setting('search_path'::text), ','::text, 1)) AND (table_name = ANY ('{this_table,that_table,other_table,just_table,more_one_table,last_table}'::text[])) AND (ins_datetime > '2022-01-22 10:00:00'::timestamp without time zone))
  ->  Bitmap Index Scan on audit_customer_table_datetime (cost=0.00..26.67 rows=14 width=0) (actual time=0.041..0.041 rows=0 loops=1)
        Index Cond: ((customer_schema = split_part(current_setting('search_path'::text), ','::text, 1)) AND (table_name = ANY ('{this_table,that_table,other_table,just_table,more_one_table,last_table}'::text[])) AND (ins_datetime > '2022-01-22 10:00:00'::timestamp without time zone))
Planning Time: 0.111 ms
Execution Time: 0.065 ms

So, not using Current_Schema but getting it with current_setting function.

And as last test, yes, if I change type of that column, then index is used with my initial query
alter table audit alter customer_schema type name;

So, what was changed with current_schema ?

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Yura Sokolov
Дата:
Сообщение: Re: Fix BUG #17335: Duplicate result rows in Gather node
Следующее
От: Pavel Stehule
Дата:
Сообщение: Re: Schema variables - new implementation for Postgres 15