current_schema will not use an text index ?

From: Marcos Pegoraro <marcos(at)f10(dot)com(dot)br>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: current_schema will not use an text index ?
Date: 2022-01-23 14:00:12
Message-ID: CAB-JLwYQYxuyzT63rqE13nQ5LS5EM0CoftuGQy3+xUt7+6PUaw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 ?

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Pavel Stehule 2022-01-23 14:33:33 Re: Schema variables - new implementation for Postgres 15
Previous Message Yura Sokolov 2022-01-23 11:56:46 Re: Fix BUG #17335: Duplicate result rows in Gather node