Middle+
Дана таблица: create table bank_users ( id bigint generated by default as identity primary key, status int, employee_number varchar(8) unique, first_name varchar(100) not null, last_name varchar(100) not null, middle_name varchar(100) default null, email varchar(100) not null unique, created_at_date_time timestamptz ); create index ix_created_at_date_time_status on bank_users (created_at_date_time, status); Есть запрос: explain (costs off, verbose) select employee_number, first_name, last_name from bank_users where status = 2 order by created_at_date_time; Возвращает: Sort Output: employee_number, first_name, last_name, created_at_date_time Sort Key: bank_users.created_at_date_time -> Seq Scan on bank_users Output: employee_number, first_name, last_name, created_at_date_time Filter: (bank_users.status = 2) Что можно сказать об оптимальности плана запроса?
sobes.tech AI
Answer from AI
The query plan shows that a sequential scan (Seq Scan) is used on the bank_users table with a filter on status = 2, followed by sorting by created_at_date_time.
Although there is an index ix_created_at_date_time_status on (created_at_date_time, status), it is not used. The reason lies in the order of columns in the index and the query conditions:
- The index is built on (created_at_date_time, status), meaning it sorts first by created_at_date_time, then by status.
- The WHERE clause filters by status, which is the second column in the index, making it inefficient for searching.
To optimize, you should create an index where status is the first column, for example:
CREATE INDEX ix_status_created_at ON bank_users (status, created_at_date_time);
This way, the planner can use the index for quick lookup by status and immediately retrieve data sorted by created_at_date_time, avoiding sorting and sequential scan.
In summary: the current plan is not optimal; creating a better index can speed up the query.