psycopg2: будет ли PostgreSQL хранить копию таблицы на диске, если ей не хватило памяти

Я выполняю следующий запрос для 489 миллионов строк (102 ГБ) на компьютере с 2 ГБ памяти:

select * from table order by x, y, z, h, j, l;

Я использую psycopg2 с серверным курсором ("cursor_unique_name") и извлекаю 30000 строк за раз.

Очевидно, что результат запроса не может оставаться в памяти, но мой вопрос заключается в том, будет ли следующий набор запросов таким же быстрым:

select * into temp_table from table order by x, y, z, h, j, l;
select * from temp_table

Это означает, что я бы использовал temp_table для хранения упорядоченного результата и вместо этого извлекал данные из этой таблицы.

Причина, по которой задается этот вопрос, заключается в том, что выполнение запроса занимает всего 36 минут, если он выполняется вручную с использованием psql, но для получения первых 30000 строк потребовалось более 8 часов (никогда не завершалось), когда запрос выполнялся с использованием psycopg2.


psycopg2 postgresql
person Rohita Khatiwada    schedule 29.03.2011    source источник
comment
Замедление почти наверняка связано с pyscopg2 (я предполагаю, что ваш курсор — pyscopg2.cursor). Разница в порядке, о которой вы говорите, может улучшить 36-минутную часть, но не 8-часовую! pyscopg2 не имеет ничего общего с порядком, он просто отправляет сообщения на сервер и получает результаты. Я бы поиграл с уменьшением числа 30 000 и посмотрел, когда вы начнете получать медленные результаты. Может быть, вам не хватает памяти, и набор результатов Python будет заменен на диск.   -  person nate c    schedule 29.03.2011
comment
Ваш вопрос довольно расплывчатый, поэтому трудно сказать, почему у вас не сработали psycopg2 и серверные курсоры. Но нет, вставка их всех во временную таблицу и последующее сканирование этой таблицы только замедлит работу. Томецки прав - создайте индекс для столбцов сортировки.   -  person intgr    schedule 30.03.2011

Ответы (1)


arrow_upward
3
arrow_downward

  1. Если вы хотите получить эту таблицу по частям и отсортировать, вам нужно создать индекс. Каждая выборка должна будет отсортировать всю эту таблицу, если такого индекса не будет. Ваш курсор, вероятно, сортировал эту таблицу один раз для каждой извлеченной строки, ожидая красного гигантского солнца, вероятно, закончится раньше
    create index tablename_order_idx on tablename (x, y, z, h, j, l);

  2. Если данные вашей таблицы относительно стабильны, вам следует cluster выполнить этот индекс. Таким образом, данные таблицы будут извлекаться без лишнего поиска на диске.
    cluster tablename using tablename_order_idx;

  3. Если вы хотите получить данные порциями, вам не следует использовать курсор, так как он всегда будет работать по одной строке за раз. Вы должны использовать limit и offset:
    select * from tablename order by x, y, z, h, j, l
    limit 30000 offset 44*30000

person Tometzky    schedule 29.03.2011
comment
-1 вам не «нужно создавать индекс», потому что запрос «занимает всего 36 минут, если его запускать вручную с помощью psql». аналогично для «кластера». Использование 'limit' и 'offset' просто неправильно - даже если во время вашего довольно длинного запроса вообще не происходит никаких обновлений, кто может сказать, что в порядке не будет ничьей? В случае ничьей на позиции 30000, можете ли вы быть уверены, что postgres разорвет ничью одинаково для вашего первого и второго «куска». Нет. - person ; 30.03.2011
comment
@JackPDouglas: Не очень конструктивная критика — пожалуйста, объясните получше. Подход с ограничением и смещением требует, чтобы первичный ключ был последним столбцом в предложении порядка. Обновления в других транзакциях не представляют проблемы, если для этой серии запросов выбран уровень сериализуемых транзакций. - person Tometzky; 30.03.2011
comment
Подход с ограничением и смещением требует, чтобы первичный ключ был последним столбцом в предложении порядка — not true - вы имеете в виду какую-то другую базу данных, кроме postgres? - person ; 30.03.2011
comment
так или иначе не уверен в сериализуемом уровне изоляции - по крайней мере, вы, вероятно, должны упомянуть об этом в своем ответе - но это достаточно? - person ; 30.03.2011
comment
@JackPDouglas: я имею в виду, что для получения значимых результатов от ограничения/смещения вы должны строго упорядочивать свои данные, а это означает, что вам нужен, например, первичный ключ в качестве последнего столбца в предложении порядка. Это не соблюдается, но иначе не работает. Сериализуемой изоляции достаточно для чтения согласованных моментальных снимков данных в транзакции только для чтения. В противном случае было бы невозможно делать согласованные резервные копии. Может быть, я должен был упомянуть все это в своем ответе, но эй — нам всем не хватает времени. - person Tometzky; 30.03.2011