Created
March 28, 2023 06:40
-
-
Save mche/485fa4a7180fb414f1ef4c85be4a84a3 to your computer and use it in GitHub Desktop.
Как сделать сортировку по изменяемому параметру?
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TEMP TABLE users ( | |
id SERIAL PRIMARY KEY, | |
name TEXT not null | |
); | |
CREATE INDEX ON users (name); -- сортировка | |
CREATE TEMP TABLE history ( | |
id SERIAL PRIMARY KEY, | |
ts TIMESTAMP NOT NULL default now()+(random()::numeric::text || ' days')::interval, | |
user_id INT NOT NULL, | |
data text, | |
FOREIGN KEY(user_id) REFERENCES users(id) | |
); | |
CREATE INDEX ON history (user_id); | |
INSERT INTO users (name) VALUES | |
('Вася'), | |
('Петя'), | |
('Маша'), | |
('Яна') | |
RETURNING *; | |
INSERT INTO history (user_id, data) | |
SELECT ceil(random() * 4)::int, substring('0123456789abcdefghijklmnopqrstuvwxyz', round(random() * 36)::integer, 10) | |
FROM generate_series(1,10000000); | |
--- обычная сортировка - быстро | |
select * | |
from users u join history h on u.id=h.user_id | |
where u.id=1 | |
order by u.name | |
limit 10; | |
-- сортировка по выражению - долгая, нет индекса, параметр сортировки меняется в каждом запросе | |
select h.* | |
from users u | |
join history h on u.id=h.user_id | |
order by case when u.id = any('{1,2}'::int[]) then u.name else 'employee' end | |
limit 10; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment