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

Question

[lil_koi]

У меня стоит задача. Есть табличка, у которой 205+-млн записей. Мне нужно обновить два столбца в ней. На данный монет они оба null.
нужно знать, что есть столбец date типа timestamp.
ЗАДАЧА

Нужно получить все записи за определённый час. Запрос выполняется примерно минуту. В одном дне 24ч, => 24 минуты за весь день. За месяц 24*30=720мин. Это очень долго, что бы обновить за 1 месяц, а у меня период 1.5 года. Всё уперается в то, что запрос на получение выборки за час выполняется очень долго. Как это можно оптимизировать? Я не нашёл, как проиндексировать дату в postgress. Прилагаю код, который написал.

import psycopg2
import requests
import datetime
import json

conn = psycopg2.connect(dbname='diplom', user='root', password='root', host='localhost')
cursor = conn.cursor()


with open('31.json', 'r') as handle:
    data = json.load(handle)
    first_object = data[0]['data']
    for i in range(len(first_object)):
      date = first_object[i]
      if(date['time'] >= 1628974800 and date['time'] <= 1630357199):
        time_start = datetime.datetime.fromtimestamp(date['time'])
        time_end = time_start + datetime.timedelta(hours=1, microseconds=-1)
        cursor.execute('''UPDATE nmea SET temperature = %s, wind_speed = %s WHERE "AIS" = 1 AND nmea.temperature IS NULL AND nmea.wind_speed IS NULL AND nmea.date >= %s AND nmea.date <= %s''', (date['temperature'], date['wind_speed'], time_start, time_end,))
        conn.commit()
        print(time_start)
    conn.commit()

Если кто-то сталкивался с обновлением таких масштабов - был бы рад услышать решение.

Comments

[Voland69]

В случае массового обновления данных commit после каждого запроса не надо делать, это медленно.
Но коммит после всех запросов тоже нельзя, потому что transaction log огромный будет.
Следует выбрать некий размер батча, который обновляется и при успехе делает коммит.

Answer 1

[ky0]

Если нужно часто выполнять какие-то bulk-операции с данными за определённый период - лучше партиционировать таблицу.

Comments

[lil_koi]

Можете подсказать, это нормально, что запрос на индексацию выполняется уже >4ч? Я не представляю, сколько может создваться индекс, где в таблице 205млн записей

[ky0]

lil_koi, если СУБД не настроена, либо ей выдано мало памяти - такое вполне возможно. В том числе поэтому я вам и предложил партишенинг.

[lil_koi]

ky0, в datagrip запрос выполнялся >6ч, сделал через консоль и проиндексировалось за 1 - 1.5 минуты.

[o5a]

lil_koi, по поводу долгого исполнения создания индекса, может быть еще какая-то особенность DataGrip. Попробуйте DBeaver (есть бесплатная Community Edition), хорошо с Postgres работает, подобных проблем не замечалось.

По поводу изначального кода еще:
1) не надо делать commit после каждой команды, сделайте в конце, после массовых изменений.
Точнее, некую середину выбрать, как предложили уже, хотя бы не чаще каждых 100 команд.
2) если есть возможность, лучше минимизировать количество таких вызовов update через код python именно, т.е. предварительно сгруппировать данные так, чтобы по каждому диапазону дат получался только один вызов update.
3) можно свою таблицу данных погоды по дням сначала загрузить в отдельную таблицу в базе, а потом уже средствами SQL сделать update одной командой, напрямую выбирая данные из самой базы. Такое как правило обрабатывается гораздо быстрее, чем повторный вызов update через курсор питона.

[Сергей Горностаев]

lil_koi, и ещё можно create index concurrently использовать, если нет возможности ограничить доступ к данным на время индексации.