query partitioned table is very slow
От | Vladimir Yavoskiy |
---|---|
Тема | query partitioned table is very slow |
Дата | |
Msg-id | 5621740E.3010002@krevedko.su обсуждение исходный текст |
Ответы |
Re: query partitioned table is very slow
|
Список | pgsql-performance |
I have about 900 partitioned tables with 67 millons rows. And I found that my query takes too much time! ------------------------------------------------------------------------------ explain ( ANALYZE,VERBOSE,BUFFERS ) select report_id from cars."all" WHERE report_datetime = '2015-10-14 00:02:02+03'::timestamptz AND report_uuid = 'f6b08f92-0d5d-28b0-81c3-0c20c4ca3038'::uuid ------------------------------------------------------------------------------ Append (cost=0.00..4.43 rows=2 width=4) (actual time=0.023..0.023 rows=1 loops=1) Buffers: shared hit=4 -> Seq Scan on cars.all (cost=0.00..0.00 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1) Output: all.report_id Filter: ((all.report_datetime = '2015-10-14 00:02:02+03'::timestamp with time zone) AND (all.report_uuid = 'f6b08f92-0d5d-28b0-81c3-0c20c4ca3038'::uuid)) -> Index Scan using day_151014_uuid_idx on cars.day_151014 (cost=0.42..4.43 rows=1 width=4) (actual time=0.020..0.020 rows=1 loops=1) Output: day_151014.report_id Index Cond: (day_151014.report_uuid = 'f6b08f92-0d5d-28b0-81c3-0c20c4ca3038'::uuid) Filter: (day_151014.report_datetime = '2015-10-14 00:02:02+03'::timestamp with time zone) Buffers: shared hit=4 Total runtime: 0.096 ms ------------------------------------------------------------------------------ This query takes about 500ms. But query from only part-table takes 12ms: select report_id from cars.day_151014 WHERE report_datetime = '2015-10-14 00:02:02+03'::timestamptz AND report_uuid = 'f6b08f92-0d5d-28b0-81c3-0c20c4ca3038'::uuid ------------------------------------------------------------------------------ explain ( ANALYZE,VERBOSE,BUFFERS ) select report_id from cars.day_151014 WHERE report_datetime = '2015-10-14 00:02:02+03'::timestamptz AND report_uuid = 'f6b08f92-0d5d-28b0-81c3-0c20c4ca3038'::uuid ------------------------------------------------------------------------------ Index Scan using day_151014_uuid_idx on cars.day_151014 (cost=0.42..4.43 rows=1 width=4) (actual time=0.022..0.023 rows=1 loops=1) Output: report_id Index Cond: (day_151014.report_uuid = 'f6b08f92-0d5d-28b0-81c3-0c20c4ca3038'::uuid) Filter: (day_151014.report_datetime = '2015-10-14 00:02:02+03'::timestamp with time zone) Buffers: shared hit=4 Total runtime: 0.045 ms ------------------------------------------------------------------------------ Query plans seems fine, but why actual query is so slow? p.s. PostgreSQL 9.3.9 x86_64 on Oracle Linux Server release 6.6 (3.8.13-68.3.2.el6uek.x86_64)
В списке pgsql-performance по дате отправления: