How to create the most optimal query with a search for the same field in different PostgreSQL tables and sort by it in the final result?

Question

PostgreSQL

PostgreSQL 9+ DB
There are several tables of different entities, they have several identical fields (name, price, description,...).
They are combined into one list through a linking table for display.
This table looks like this:

CREATE TABLE section_items(
  id serial not null primary key,  
  section_id int4, -- ссылка на секцию, к которой относится привязка
  uni_id int4,     -- универсальное ID, которое ссылается на разные таблицы, в зависимости от type_item
  type_item int4,  -- хранит тип сущности, который необходимо выводить, по ней определяется к какой таблице надо обращаться за данными
  sort_num int4
);

Suppose there is table A (name, price, description, test_id, last_date_actual,...) , table B (name, price, description, questions_id, location_position, ...) , table C (name, price, description, webinar_url , webinar_time_start,...) , etc. The number of such entities is planned to be increased in the future.
All of them are displayed in a similar +- structure on the page, with their own tile template for different structures. There are no problems with this.
Now the crux of the matter : The user enters a phrase in the search bar on the site, by which it is necessary to find all the entities involved in the section, taking into account this phrase, and also sort them by name.
those. the final request script should be formed in the following form:

select section_items.*, (/* тут предполагаю какая-нибудь функция выборки полей */) as name 
from section_items 
/*тут длинный запрос объединения и получения name из разных сущностей*/ 
order by name 
limit 12 offset 0;

An example of the result of executing this query :
the user entered "training" in the first section and a table was formed:

id | section_id | uni_id | type_item | name
------------------------------------------------
2 | 1 | 24 | 3 | Guitar Learning Webinar
15 | 1 | 117 | 1 | Chess course
25 | 1 | 14 | 1 | Mental Mathematics Course
116 | 1 | 24 | 2 | Programming Learning Test
....

The whole thing is still divided into pages, i.e. this must be taken into account.
Now I solve this problem with several requests, and by joining the data already with a script on the server side.
But I'm afraid that this is not the most optimal approach to solving such a problem, besides, to sort by name, you have to get all the entities, and paginate this array, getting the IDs by which another request is made, for the correct pagination of the engine.
In my opinion, a database faster and with less resource consumption could collect this table, but I'm not strong in such complex SQL scripts, so I wanted to know if it was possible to compose the select query I needed, which would be executed in 1 call / transaction , and if possible, how and with what functions, how could it look like?

Answer 1

I recommend taking a closer look at the materialized view, as already advised earlier.
The peculiarity is that using the name search field in the materialized view, you can build a gist/gin index and use a quick search by %like% (see https ://postgrespro.ru/docs/postgrespro/9.5/pgtrgm...
note that the materialized view must be updated manually by periodically executing the command

REFRESH MATERIALIZED VIEW mymatview;

Answer 2

Store required search fields in the same adjacent table or views https://www.postgresql.org/docs/12/rules-materiali... https://www.postgresql.org/docs/12/rules-views .html