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 ?
