У нас есть таблица с 4 миллионами записей, и мы создали разделы для этой таблицы, предполагая, что запросы выбора будут быстрее для таблиц с поддержкой разделов. Однако выбор для таблиц с включенными разделами выполняется в 2 раза медленнее !!
В обычном столе (24 мс)
explain analyse select * from tbl_original where device_info_id = 5;
В таблице с включенными разделами (49 мс)
explain analyse select * from tbl_partitioned where device_info_id = 5;
Ниже приводится результат выполнения команды EXPLAIN ANALYZE
для tbl_original
:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_original (cost=61.19..9515.02 rows=2679 width=379) (actual time=0.297..13.008 rows=3369 loops=1)
Recheck Cond: (device_info_id = 5)
Heap Blocks: exact=554
-> Bitmap Index Scan on idx_tbl_original (cost=0.00..60.52 rows=2679 width=0) (actual time=0.232..0.232 rows=3369 loops=1)
Index Cond: (device_info_id = 5)
Planning time: 0.087 ms
Execution time: 24.890 ms
Ниже приводится результат выполнения команды EXPLAIN ANALYZE
для tbl_partitioned
.
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..6251.14 rows=3697 width=404) (actual time=0.034..36.635 rows=3369 loops=1)
-> Seq Scan on tbl_partitioned (cost=0.00..0.00 rows=1 width=1069) (actual time=0.006..0.006 rows=0 loops=1)
Filter: (device_info_id = 5)
-> Index Scan using idx_tbl_partitioned_p1 on tbl_partitioned_p1 (cost=0.42..6251.14 rows=3696 width=404) (actual time=0.017..12.922 rows=3369 loops=1)
Index Cond: (device_info_id = 5)
Planning time: 0.184 ms
Execution time: 49.129 ms
Похоже, что самая затратная операция в многораздельном запросе - это сканирование индекса с использованием 6251,14 единиц. Однако, учитывая размер таблицы разделов по сравнению с исходной таблицей, сканирование индекса должно было быть очень быстрым. Не уверен, что мы упускаем здесь что-нибудь очевидное!
Приветствуется любая помощь в оптимизации таблицы запросов / секционированной таблицы.
Примечание: многораздельная таблица была создана с использованием следующего:
CREATE TABLE tbl_partitioned (LIKE tbl_original);
CREATE TABLE tbl_partitioned_p1 (
CONSTRAINT pk_tbl_partitioned_p1 PRIMARY KEY (id),
CONSTRAINT ck_tbl_partitioned_p1 CHECK ( device_info_id < 10 )
) INHERITS (tbl_partitioned);
CREATE INDEX idx_tbl_partitioned_p1 ON tbl_partitioned_p1 (device_info_id);
CREATE INDEX idx_tbl_partitioned ON tbl_partitioned (device_info_id);
INSERT INTO tbl_partitioned_p1 SELECT * from tbl_original where device_info_id < 10;
Размеры столов:
select count(*) from tbl_partitioned; -- 413696 rows
select count(*) from tbl_original; -- 4417025 rows
select count(*) from tbl_original where device_info_id = 5; -- 3369 rows
constraint_exclusion
установлен на partition
select version()
скажет вам). Не могли бы вы также добавить план выполнения, созданный с использованиемexplain (analyze, buffers)
(особенно для случая с разделами) - person a_horse_with_no_name   schedule 03.12.2018device_info_id
, для которого исходная таблица занимает ~ 8 секунд:explain analyse select * from tbl_original where device_info_id = 72; -- Execution time: 8780.177 ms
- person Khushbu   schedule 03.12.2018