Как оптимизировать этот запрос PostgresSQL?

ProgramBox

Как оптимизировать этот запрос PostgresSQL?

Post author:admin
Запись опубликована:15 января, 2022
Post category:Вопросы по программированию

#python #postgresql

Вопрос:

В настоящее время работает:

 SELECT
    ll."BeaconId" ,
    a."Name" ,
    ll."Timestamp" ,
    r."Mean",
    r."AbilityId" 
FROM "LastLocations" AS ll
LEFT JOIN "Readings" AS r ON
    r."BeaconId" = ll."BeaconId" 
LEFT JOIN "BeaconAssignments" AS ba ON
    ba."BeaconId" = ll."BeaconId"
LEFT JOIN "Assets" AS a ON
    a."Id" = ba."AssetId"
WHERE
    ll."MapId" = 200 and r."AbilityId" = 3 OR r."AbilityId" = 4
FETCH FIRST 300000 ROWS ONLY

Пытался:

 SELECT
    COUNT(ll."BeaconId") as "BeaconId",
    a."Name" ,
    DATE_TRUNC('hour', ll."Timestamp") as "Timestamp",
    AVG(r."Mean") as "Mean",
    r."AbilityId"
FROM "LastLocations" AS ll
LEFT JOIN "Readings" AS r ON
    r."BeaconId" = ll."BeaconId" 
LEFT JOIN "BeaconAssignments" AS ba ON
    ba."BeaconId" = ll."BeaconId"
LEFT JOIN "Assets" AS a ON
    a."Id" = ba."AssetId"
WHERE
    ll."MapId" = 200 AND r."AbilityId" = 3 OR r."AbilityId" = 4
GROUP BY ll."Timestamp", r."AbilityId", a."Name"

Я новичок в SQL в целом, и у меня есть таблица длиной 740 миллионов строк. Первый запрос успешно загружает таблицу во фрейм данных pandas для меня. Однако в настоящее время я ограничиваю количество извлеченных строк до 300 000, так как в противном случае это займет слишком много времени.

То, что я пытаюсь сделать во втором запросе, — это по существу сгруппировать данные по часам. Это значительно сократит количество строк в SQL перед отправкой пандам. Мое намерение состоит в том, чтобы не потерять данные, ограничив количество строк, как я делаю в первом запросе.

Любопытно, есть ли у кого-нибудь идеи о том, как сделать это более эффективным, или можно использовать другой подход?

ОБЪЯСНИТЕ(ПРОАНАЛИЗИРУЙТЕ,БУФЕРЫ) вывод. Я использовал улучшенный запрос Laurenz Albe, но ограничился 300 000 строками для получения реального результата:

 Limit  (cost=21204954.90..21305131.45 rows=300000 width=42) (actual time=400750.745..423438.464 rows=472 loops=1)
  Buffers: shared hit=1300529 read=9401083, temp read=2048429 written=2050673
  ->  Finalize GroupAggregate  (cost=21204954.90..24020556.97 rows=8431920 width=42) (actual time=400750.744..423438.203 rows=472 loops=1)
        Group Key: (date_trunc('hour'::text, ll."Timestamp")), r."AbilityId", a."Name"
        Buffers: shared hit=1300529 read=9401083, temp read=2048429 written=2050673
        ->  Gather Merge  (cost=21204954.90..23683280.17 rows=16863840 width=66) (actual time=400724.591..423436.392 rows=1411 loops=1)
              Workers Planned: 2
              Workers Launched: 2
              Buffers: shared hit=1300529 read=9401083, temp read=2048429 written=2050673
              ->  Partial GroupAggregate  (cost=21203954.88..21735774.58 rows=8431920 width=66) (actual time=385879.606..405937.192 rows=470 loops=3)
                    Group Key: (date_trunc('hour'::text, ll."Timestamp")), r."AbilityId", a."Name"
                    Buffers: shared hit=1300529 read=9401083, temp read=2048429 written=2050673
                    ->  Sort  (cost=21203954.88..21275024.99 rows=28428047 width=38) (actual time=385869.894..397691.644 rows=28912575 loops=3)
                          Sort Key: (date_trunc('hour'::text, ll."Timestamp")), r."AbilityId", a."Name"
                          Sort Method: external merge  Disk: 1446864kB
                          Worker 0:  Sort Method: external merge  Disk: 1465512kB
                          Worker 1:  Sort Method: external merge  Disk: 1416352kB
                          Buffers: shared hit=1300529 read=9401083, temp read=2048429 written=2050673
                          ->  Hash Join  (cost=2005.96..15352449.79 rows=28428047 width=38) (actual time=426.777..231374.850 rows=28912575 loops=3)
                                Hash Cond: (r."BeaconId" = ll."BeaconId")
                                Buffers: shared hit=1300485 read=9401083
                                ->  Parallel Seq Scan on "Readings" r  (cost=0.00..14601996.04 rows=104786066 width=16) (actual time=0.020..173893.042 rows=84998306 loops=3)
                                      Filter: ("AbilityId" = ANY ('{3,4}'::integer[]))
                                      Rows Removed by Filter: 164491074
                                      Buffers: shared hit=1299285 read=9401083
                                ->  Hash  (cost=1996.38..1996.38 rows=766 width=26) (actual time=4.287..4.292 rows=867 loops=3)
                                      Buckets: 1024  Batches: 1  Memory Usage: 59kB
                                      Buffers: shared hit=1184
                                      ->  Hash Right Join  (cost=1870.44..1996.38 rows=766 width=26) (actual time=2.603..4.027 rows=867 loops=3)
                                            Hash Cond: (a."Id" = ba."AssetId")
                                            Buffers: shared hit=1184
                                            ->  Seq Scan on "Assets" a  (cost=0.00..82.14 rows=3614 width=18) (actual time=0.007..0.533 rows=3633 loops=3)
                                                  Buffers: shared hit=138
                                            ->  Hash  (cost=1860.87..1860.87 rows=766 width=16) (actual time=2.570..2.573 rows=867 loops=3)
                                                  Buckets: 1024  Batches: 1  Memory Usage: 52kB
                                                  Buffers: shared hit=1046
                                                  ->  Hash Right Join  (cost=1792.95..1860.87 rows=766 width=16) (actual time=1.454..2.332 rows=867 loops=3)
                                                        Hash Cond: (ba."BeaconId" = ll."BeaconId")
                                                        Buffers: shared hit=1046
                                                        ->  Seq Scan on "BeaconAssignments" ba  (cost=0.00..58.78 rows=3478 width=8) (actual time=0.011..0.469 rows=3396 loops=3)
                                                              Buffers: shared hit=72
                                                        ->  Hash  (cost=1783.76..1783.76 rows=735 width=12) (actual time=0.955..0.956 rows=735 loops=3)
                                                              Buckets: 1024  Batches: 1  Memory Usage: 43kB
                                                              Buffers: shared hit=974
                                                              ->  Bitmap Heap Scan on "LastLocations" ll  (cost=293.98..1783.76 rows=735 width=12) (actual time=0.160..0.739 rows=735 loops=3)
                                                                    Recheck Cond: ("MapId" = 200)
                                                                    Heap Blocks: exact=315
                                                                    Buffers: shared hit=974
                                                                    ->  Bitmap Index Scan on "IX_LastLocations_MapId"  (cost=0.00..293.79 rows=735 width=0) (actual time=0.112..0.112 rows=735 loops=3)
                                                                          Index Cond: ("MapId" = 200)
                                                                          Buffers: shared hit=29
Planning Time: 0.761 ms
Execution Time: 423664.670 ms

1. Примечание: ... and r."AbilityId" = 3 OR r."AbilityId" = 4 уменьшит ваше ЛЕВОЕ соединение до простого соединения. (здесь также существует возможная И/ИЛИ проблема приоритета)

2. Какую обработку вы собираетесь выполнять с данными? Вообще говоря, часто более эффективно выполнять работу внутри базы данных, чем перебирать сотни миллионов строк через интерфейс приложения-БД.

3. Как я мог бы исправить возможную и/или проблему приоритета, круглые скобки? Что касается обработки, я использую данные в графическом тире. Таким образом, пользователь может в основном выбрать из списка уникальные значения столбца «Имя» в выпадающем списке, а затем я отфильтровываю только строки, имеющие значение «Имя», с помощью Pandas.

4. Я надеялся сгруппировать столбец «Отметка времени» по дням/часам в SQL заранее, а затем отправить его Пандам. Фильтрация по дням значительно уменьшит размер, так как в настоящее время данные собираются в минуту для многих объектов в течение нескольких месяцев. Это для моей работы, но в настоящее время проект кажется невозможным из-за огромного размера этой таблицы.

Ответ №1:

Все в SELECT списке, что не входит в агрегатную функцию, также должно быть в GROUP BY списке. Кроме того, вы не можете использовать псевдонимы в GROUP BY предложении.
Вам следует избегать OR этого для повышения производительности.
У вас есть какой-нибудь указатель "LastLocations"."MapId" ? В зависимости от того, как выполняется ваш запрос, "Readings"."AbilityId" также может помочь индекс on.
Если вы можете, используйте внутренние соединения, а не внешние.

Это попытка исправить ваш запрос:

 SELECT
    COUNT(ll."BeaconId") as "BeaconId",
    a."Name" ,
    DATE_TRUNC('hour', ll."Timestamp") as "Timestamp",
    AVG(r."Mean") as "Mean",
    r."AbilityId"
FROM "LastLocations" AS ll
JOIN "Readings" AS r ON
    r."BeaconId" = ll."BeaconId" 
LEFT JOIN "BeaconAssignments" AS ba ON
    ba."BeaconId" = ll."BeaconId"
LEFT JOIN "Assets" AS a ON
    a."Id" = ba."AssetId"
WHERE
    ll."MapId" = 200 AND r."AbilityId" IN (3, 4)
GROUP BY DATE_TRUNC('hour', ll."Timestamp"),
         r."AbilityId",
         a."Name";

Прочитав свой план выполнения, вы могли бы опробовать индекс на "Readings"."BeaconId" . Кроме этого, вы можете только увеличить work_mem (чтобы ускорить сортировку) или ускорить ввод-вывод.

Хранение данных в предварительно агрегированной форме очень помогло бы.

1. Добавлены ОБЪЯСНЕНИЯ(АНАЛИЗ, БУФЕРЫ) результатов вашего улучшенного запроса.

2. Кроме того, в настоящее время я не добавил никаких индексов. Очень новичок в SQL, не уверен, как они помогут, но немного почитаю.

3. Вы могли бы примерить индекс "Readings"."BeaconId" . Кроме этого, вы могли бы только увеличить work_mem (для ускорения сортировки) или ускорить ввод-вывод. Хранение данных в предварительно агрегированной форме очень помогло бы.

4. Понял, спасибо за помощь здесь! Я буду работать над индексацией, и если это не сработает, просто создам новую таблицу, которая предварительно агрегирована.

5. Это не (только) индексация. Это моделирование ваших данных. Создайте первичные ключи. Определите внешние ключи. И добавьте индексы к этим внешним ключам.

Метки: Как оптимизировать этот запрос PostgresSQL?