TimeScaleDB - медленный запрос временного сегмента с индексами и различными вариантами размера блока - я сделал что-то не так?

Я недавно играл с TimeScaleDB, но я немного сбит с толку и мне нужны указатели на то, почему мой запрос выполняется медленно, или чтобы проверить, является ли это типичной производительностью запроса timescaledb.

Набор данных, который я использую, представляет собой тиковые данные рынка за одну конкретную дату, для которой я загрузил около 84 миллионов записей в свою гипертаблицу.

Вот пример типа данных из моего файла:

2018-12-03 00:00:00.000344+00:00,2181.T,2018-12-03,2179,56300,2180,59500

2018-12-03 00:00:00.000629+00:00,1570.T,2018-12-03,20470,555118,20480,483857

2018-12-03 00:00:00.000631+00:00,2002.T,2018-12-03,2403,30300,2404,30200

Моя таблица была создана так:


CREATE TABLE tt1 (time        TIMESTAMPTZ           NOT NULL,
cusip       varchar(40)           NOT NULL,     
date        DATE                NULL,  
value       DOUBLE PRECISION,
value2      DOUBLE PRECISION,
value3      DOUBLE PRECISION,
value4      DOUBLE PRECISION);

Я создал две версии гипертаблицы: tt1 с фрагментами по 1 минуте и tt30m с фрагментами по 30 минут. Обе таблицы соответствуют приведенной выше схеме. Я создал гипертаблицу так:

SELECT create_hypertable('tt1', 'time', chunk_time_interval => interval '1 minute');

Столбцы времени и cusip индексируются в обеих версиях гипертаблицы. Время по умолчанию индексируется при создании гипертаблицы, и я создал индекс cusip со следующим

  CREATE INDEX ON tt1(cusip, time DESC);

Мой запрос выглядит так:

EXPLAIN ANALYZE SELECT time_bucket('15 minutes', time) AS fifteen_min,
  cusip, COUNT(*)
  FROM tt1
  WHERE time > timestamp '2018-12-03 05:10:06.174704-05' - interval '3 hours'
  GROUP BY fifteen_min, cusip
  ORDER BY fifteen_min DESC;

Для 30-минутных фрагментов запрос занимает 25,969 секунды. вот план запроса для него:

                                                                                 QUERY PLAN                                                                        



-------------------------------------------------------------------------------------------------------------------------------------------------------------------

----------

 Finalize GroupAggregate  (cost=1679944.84..1685344.84 rows=40000 width=40) (actual time=25770.209..25873.410 rows=305849 loops=1)

   Group Key: (time_bucket('00:15:00'::interval, tt30m."time")), tt30m.cusip

   ->  Gather Merge  (cost=1679944.84..1684544.84 rows=40000 width=40) (actual time=25770.181..25885.080 rows=305849 loops=1)

         Workers Planned: 1

         Workers Launched: 1

         ->  Sort  (cost=1678944.83..1679044.83 rows=40000 width=40) (actual time=12880.868..12911.917 rows=152924 loops=2)

               Sort Key: (time_bucket('00:15:00'::interval, tt30m."time")) DESC, tt30m.cusip

               Sort Method: quicksort  Memory: 25kB

               Worker 0:  Sort Method: external merge  Disk: 10976kB

               ->  Partial HashAggregate  (cost=1675387.29..1675887.29 rows=40000 width=40) (actual time=12501.381..12536.373 rows=152924 loops=2)

                     Group Key: time_bucket('00:15:00'::interval, tt30m."time"), tt30m.cusip

                     ->  Parallel Custom Scan (ChunkAppend) on tt30m  (cost=10680.22..1416961.58 rows=34456761 width=32) (actual time=0.020..7293.929 rows=24255398

 loops=2)

                           Chunks excluded during startup: 14

                           ->  Parallel Seq Scan on _hyper_2_753_chunk  (cost=0.00..116011.42 rows=4366426 width=17) (actual time=0.037..1502.121 rows=7423073 loop

s=1)

                                 Filter: ("time" > '2018-12-03 02:10:06.174704'::timestamp without time zone)

                           ->  Parallel Seq Scan on _hyper_2_755_chunk  (cost=0.00..108809.26 rows=4095539 width=17) (actual time=0.017..1446.248 rows=6962556 loop

s=1)

                                 Filter: ("time" > '2018-12-03 02:10:06.174704'::timestamp without time zone)

                           ->  Parallel Seq Scan on _hyper_2_754_chunk  (cost=0.00..107469.27 rows=4056341 width=17) (actual time=0.015..1325.638 rows=6895917 loop

s=1)

                                 Filter: ("time" > '2018-12-03 02:10:06.174704'::timestamp without time zone)

                           ->  Parallel Seq Scan on _hyper_2_756_chunk  (cost=0.00..99037.70 rows=3730381 width=17) (actual time=0.006..1206.708 rows=6341775 loops

=1)

                                 Filter: ("time" > '2018-12-03 02:10:06.174704'::timestamp without time zone)

                           ->  Parallel Seq Scan on _hyper_2_758_chunk  (cost=0.00..90757.67 rows=3421505 width=17) (actual time=0.017..1126.757 rows=5816675 loops

Time: 25968.520 ms (00:25.969)

Для фрагментов в 1 минуту запрос занимает 25,686 секунды. Вот план запроса:

                                                                              QUERY PLAN                                                                           



-------------------------------------------------------------------------------------------------------------------------------------------------------------------

---

 Finalize GroupAggregate  (cost=1048760.27..1054160.27 rows=40000 width=25) (actual time=25306.291..25409.778 rows=305849 loops=1)

   Group Key: (time_bucket('00:15:00'::interval, tt1."time")), tt1.cusip

   ->  Gather Merge  (cost=1048760.27..1053360.27 rows=40000 width=25) (actual time=25306.282..25424.711 rows=305849 loops=1)

         Workers Planned: 1

         Workers Launched: 1

         ->  Sort  (cost=1047760.26..1047860.26 rows=40000 width=25) (actual time=12629.859..12665.190 rows=152924 loops=2)

               Sort Key: (time_bucket('00:15:00'::interval, tt1."time")) DESC, tt1.cusip

               Sort Method: quicksort  Memory: 25kB

               Worker 0:  Sort Method: external merge  Disk: 10976kB

               ->  Partial HashAggregate  (cost=1044202.72..1044702.72 rows=40000 width=25) (actual time=12276.755..12311.071 rows=152924 loops=2)

                     Group Key: time_bucket('00:15:00'::interval, tt1."time"), tt1.cusip

                     ->  Parallel Custom Scan (ChunkAppend) on tt1  (cost=0.42..830181.18 rows=28536205 width=17) (actual time=0.013..7147.401 rows=24255398 loops=

2)

                           Chunks excluded during startup: 430

                           ->  Parallel Seq Scan on _hyper_1_564_chunk  (cost=0.00..4776.72 rows=180218 width=17) (actual time=0.022..56.440 rows=306370 loops=1)

                                 Filter: ("time" > '2018-12-03 02:10:06.174704'::timestamp without time zone)

                           ->  Parallel Seq Scan on _hyper_1_571_chunk  (cost=0.00..4632.82 rows=174066 width=16) (actual time=0.006..55.440 rows=295912 loops=1)

                                 Filter: ("time" > '2018-12-03 02:10:06.174704'::timestamp without time zone)

                           ->  Parallel Seq Scan on _hyper_1_553_chunk  (cost=0.00..4598.08 rows=173526 width=17) (actual time=0.019..61.084 rows=294995 loops=1)

                                 Filter: ("time" > '2018-12-03 02:10:06.174704'::timestamp without time zone)

                           ->  Parallel Seq Scan on _hyper_1_499_chunk  (cost=0.00..4586.53 rows=172922 width=17) (actual time=0.006..64.104 rows=293968 loops=1)

                                 Filter: ("time" > '2018-12-03 02:10:06.174704'::timestamp without time zone)

                           ->  Parallel Seq Scan on _hyper_1_498_chunk  (cost=0.00..4528.29 rows=170504 width=17) (actual time=0.005..52.295 rows=289856 loops=1)

                                 Filter: ("time" > '2018-12-03 02:10:06.174704'::timestamp without time zone)

                           ->  Parallel Seq Scan on _hyper_1_502_chunk  (cost=0.00..4509.36 rows=169949 width=17) (actual time=0.005..53.786 rows=288913 loops=1)

                                 Filter: ("time" > '2018-12-03 02:10:06.174704'::timestamp without time zone)

                           ->  Parallel Seq Scan on _hyper_1_645_chunk  (cost=0.00..4469.19 rows=168735 width=17) (actual time=0.013..55.431 rows=286850 loops=1)

                                 Filter: ("time" > '2018-12-03 02:10:06.174704'::timestamp without time zone)

По сути, я ищу некоторые указатели относительно того, является ли это ожидаемой производительностью timescaledb или есть ли способ оптимизировать этот запрос?

Я уже запустил инструмент timescaledb-tune и принял все предложенные им оптимизации. Я запускаю это на Linux vm через виртуальную коробку. VM имеет 20 ГБ оперативной памяти, 250 ГБ + места на жестком диске и 2 процессора. Версия Postgres - 11.6, а версия TimeScaleDB - 1.5.0. Прикрепленный вывод dump_meta_data здесь: вывод метаданных дампа

Большое спасибо за любые отзывы :)


sql database timescaledb performance hypertable
person Lakhvir    schedule 05.12.2019    source источник

Ответы (1)


arrow_upward
1
arrow_downward

этот запрос выглядит так, как будто в любом случае потребуется сканировать все записи за 3-часовой период, и это то, что требует времени, есть несколько вариантов для ускорения такого рода вещей, один из них заключается в том, что виртуальное оборудование здесь может быть замедление, так как для этого требуется изрядное количество io, а ваш ящик довольно мал и, вероятно, немного замедляется для ввода-вывода, поэтому здесь поможет более крупный ящик. Изменение размера блока не будет иметь большого эффекта, размер блока практически не влияет на этот тип запроса, и на самом деле я бы рекомендовал блоки большего размера, поскольку 84 миллиона строк - это не так уж и много. Другой вариант - использовать непрерывные агрегаты для предварительного вычисления некоторых из этих операций для вас, если это тип запроса, который вы собираетесь выполнять в большом количестве, что может сэкономить вам время и проблемы с процессором / памятью / io.

person davidk    schedule 05.12.2019