Download as PDF, PPTX
Uploaded byNikolay Samokhvalov
#RuPostgresLive 4: как писать и читать сложные SQL-запросы
The document provides a comprehensive guide on writing and reading complex SQL queries in PostgreSQL, including table creation, data insertion, and performance optimization techniques. It highlights various SQL functionalities such as indexing, counting methods, and lateral joins, along with references to relevant articles and presentations for further learning. Additionally, it features code snippets for executing specific SQL tasks related to data management and retrieval.
#RuPostgresLive 4: как писать и читать сложные SQL-запросы
- 1. #RuPostgresLive Как писать ичитать сложные SQL-запросы Николай Самохвалов Twitter: @postgresmen ru@postgresql.org 2017-11-23
- 2. Подготовка create table post( id bigserial primary key, person_id int8 not null, created_at timestamptz not null, something text ); insert into post(user_id, created_at, something) select (random() * 10^5)::int8 as person_id, -- ~100k users now() - interval '1 minute' * (random() * 60 * 24 * 365 * 2) as created_at, ( select string_agg(substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVW XYZ0123456789 ', (random() * 72)::integer + 1, 1), '') from generate_series(1, 100 + i % 10 + (random() * 2000)::integer) ) as something -- 100-2100 bytes of something from generate_series(1, 1000000) AS g(i) -- 1 million posts ;
- 3. Подготовка delete from post where(person_id, created_at) in ( select person_id, created_at from post group by person_id, created_at having count(*) > 1 ); create index i_post_created_at on post using btree(created_at); create unique index u_post_author_id_created_at on post(person_id, created_at); create table person as select distinct on (person_id) person_id as id, 'person_' || person_id as name from post ; alter table person add primary key (id); alter table post add constraint fk_post_person_id foreign key (person_id) references person(id); analyze post; analyze person;
- 4. Быстрый count(..). Таблицацеликом select count(*) from post; -- sloooow explain select * from post; -- see “rows= …” for an estimation
- 5. Быстрый count(..). Поискпо индексу select count(*) from post where person_id = 1; select count(1) from post where person_id = 1; Ещё (много интересного) — в статье Joe Nelson “Faster PostgreSQL Counting” https://gist.github.com/begriffs/67839ff18176d5879e77954bfcd38f1f https://www.citusdata.com/blog/2016/10/12/count-performance/
- 6. Немного про #nooffset select* from post order by id desc limit 10 offset 100000;
- 7. Немного про #nooffset select* from post order by id desc limit 10 offset 500000;
- 8. Немного про #nooffset select* from post order by id desc limit 10 offset 500000; select * from post where id < :last_id order by id desc limit 10;
- 9. Как делать OFFSETправильно? Вариант 2 select pl.* from ( select id from post order by id offset 500000 limit 1 ) as t, lateral ( select * from post where id >= t.id order by id limit 10 ) as pl; Хорошая статья про LATERAL JOIN: https://medium.com/kkempin/postgresqls-lateral-join-bfd6bd0199df
- 10. Top-N постов select person.name, p.* from person joinlateral ( select * from post where person_id = person.id order by id desc limit 3 ) as p on true where person.id between 1 and 1000 order by person_id ;
- 11.
- 12. DISTINCT — recursiveCTE with recursive t as ( -- start from least persno ID ( select person_id as _person_id from post order by 1 limit 1 ) union all select person_id as _person_id from t, lateral ( -- find the next person_id > current person_id select person_id from post where person_id > t._person_id order by 1 limit 1 ) as p_id ) select _person_id from t;
- 13. Полезное ● Очень хорошаяпрезентация о современном SQL и хороший сайт в целом – Use the Index, Luke! http://use-the-index-luke.com/blog/2015-02/modern-sql ● Про count(..) https://gist.github.com/begriffs/67839ff18176d5879e77954bfcd38f1f ● Максим Богук “Как научить слона танцевать Рок-н-ролл” https://pgday.ru/presentation/232/5964945ea4142.pdf ● Twitter: @postgresmen