Очень медленный лексикографический порядок в PostgreSQL?

У меня есть представление vote_pairs, которое выглядит так:

CREATE VIEW vote_pairs AS
    SELECT
        v1.name as name1,
        v2.name as name2,
        ...
    FROM votes AS v1
    JOIN votes AS v2
        ON v1.topic_id = v2.topic_id;

А с ~100 тыс. строк в таблице votes выполнение запросов в этом представлении занимает около 3 секунд.

Однако, когда я добавляю дополнительный фильтр по именам:

… ON v1.topic_id = v2.topic_id AND v1.name < v2.name;

Время выполнения увеличилось в четыре раза, и на выполнение запросов через vote_pairs уходит почти 12 секунд.

Эта среда выполнения непротиворечива независимо от положения ограничения… Например, запрос одинаково медленный, если фильтр перемещен в предложение WHERE внешнего запроса:

SELECT * FROM vote_pairs WHERE name1 < name2;

В чем дело? Медленны ли лексикографические сравнения в Postgres? Это что-то другое? И как я могу улучшить скорость этого запроса?

Таблица голосования:

CREATE TABLE votes (
    topic_id INTEGER REFERENCES topics(id),
    name VARCHAR(64),
    vote VARCHAR(12)
)

CREATE INDEX votes_topic_name ON votes (topic_id, name);
CREATE INDEX votes_name ON votes (name);

Вывод EXPLAIN ANALYZE без фильтра имен:

db=# CREATE OR REPLACE VIEW vote_pairs AS
db-#     SELECT
db-#         v1.name as name1,
db-#         v2.name as name2
db-#     FROM votes AS v1
db-#     JOIN votes AS v2
db-#         ON v1.topic_id = v2.topic_id;
CREATE VIEW
db=# EXPLAIN ANALYZE SELECT * FROM vote_pairs;                                                                                                                                                                                                                           QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=3956.38..71868.56 rows=5147800 width=28) (actual time=51.810..1236.673 rows=5082750 loops=1)
   Hash Cond: (v1.topic_id = v2.topic_id)
   ->  Seq Scan on votes v1  (cost=0.00..1882.50 rows=112950 width=18) (actual time=0.019..18.358 rows=112950 loops=1)
   ->  Hash  (cost=1882.50..1882.50 rows=112950 width=18) (actual time=50.671..50.671 rows=112950 loops=1)
         ->  Seq Scan on votes v2  (cost=0.00..1882.50 rows=112950 width=18) (actual time=0.004..20.306 rows=112950 loops=1)
 Total runtime: 1495.963 ms
(6 rows)

И с фильтром:

db=# CREATE OR REPLACE VIEW vote_pairs AS
db-#     SELECT
db-#         v1.name as name1,
db-#         v2.name as name2
db-#     FROM votes AS v1
db-#     JOIN votes AS v2
db-#         ON v1.topic_id = v2.topic_id AND v1.name < v2.name;
CREATE VIEW
db=# EXPLAIN ANALYZE SELECT * FROM vote_pairs;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=3956.38..84738.06 rows=1715933 width=28) (actual time=66.688..6900.478 rows=2484900 loops=1)
   Hash Cond: (v1.topic_id = v2.topic_id)
   Join Filter: ((v1.name)::text < (v2.name)::text)
   ->  Seq Scan on votes v1  (cost=0.00..1882.50 rows=112950 width=18) (actual time=0.023..24.539 rows=112950 loops=1)
   ->  Hash  (cost=1882.50..1882.50 rows=112950 width=18) (actual time=65.603..65.603 rows=112950 loops=1)
         ->  Seq Scan on votes v2  (cost=0.00..1882.50 rows=112950 width=18) (actual time=0.004..26.756 rows=112950 loops=1)
 Total runtime: 7048.740 ms
(7 rows)

ОБЪЯСНИТЬ (АНАЛИЗИРОВАТЬ, БУФЕРЫ):

db=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM vote_pairs;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=3956.38..71345.89 rows=5152008 width=28) (actual time=56.230..1204.522 rows=5082750 loops=1)
   Hash Cond: (v1.topic_id = v2.topic_id)
   Buffers: shared hit=129 read=1377 written=2, temp read=988 written=974
   ->  Seq Scan on votes v1  (cost=0.00..1882.50 rows=112950 width=18) (actual time=0.008..20.492 rows=112950 loops=1)
         Buffers: shared hit=77 read=676
   ->  Hash  (cost=1882.50..1882.50 rows=112950 width=18) (actual time=55.742..55.742 rows=112950 loops=1)
         Buckets: 2048  Batches: 8  Memory Usage: 752kB
         Buffers: shared hit=52 read=701 written=2, temp written=480
         ->  Seq Scan on votes v2  (cost=0.00..1882.50 rows=112950 width=18) (actual time=0.004..22.954 rows=112950 loops=1)
               Buffers: shared hit=52 read=701 written=2
 Total runtime: 1499.302 ms
(11 rows)


db=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM vote_pairs WHERE name1 > name2;                                              
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=3956.38..84225.91 rows=1717336 width=28) (actual time=51.214..6422.592 rows=2484900 loops=1)
   Hash Cond: (v1.topic_id = v2.topic_id)
   Join Filter: ((v1.name)::text > (v2.name)::text)
   Rows Removed by Join Filter: 2597850
   Buffers: shared hit=32 read=1477, temp read=988 written=974
   ->  Seq Scan on votes v1  (cost=0.00..1882.50 rows=112950 width=18) (actual time=0.008..22.605 rows=112950 loops=1)
         Buffers: shared hit=27 read=726
   ->  Hash  (cost=1882.50..1882.50 rows=112950 width=18) (actual time=50.678..50.678 rows=112950 loops=1)
         Buckets: 2048  Batches: 8  Memory Usage: 752kB
         Buffers: shared hit=2 read=751, temp written=480
         ->  Seq Scan on votes v2  (cost=0.00..1882.50 rows=112950 width=18) (actual time=0.005..21.337 rows=112950 loops=1)
               Buffers: shared hit=2 read=751
 Total runtime: 6573.308 ms
(13 rows)

Разные примечания:

  • VACCUM FULL и ANALYZE votes запущены
  • И 8.4.11, и 9.2.3 ведут себя одинаково

postgresql postgresql-performance postgresql-8.4
person David Wolever    schedule 09.06.2013    source источник
comment
Можете ли вы предоставить схему для таблицы, а также результаты EXPLAIN ANALYZE <query>?   -  person Alex Gaynor    schedule 09.06.2013
comment
Ок, добавил. Кроме того, я немного соврал о времени — оказывается, часть этого времени ушла на другие запросы. Но соотношение здесь все равно примерно такое же 1,5 секунды против 7 секунд.   -  person David Wolever    schedule 09.06.2013
comment
Я не могу отделаться от мысли, что структура вашей базовой таблицы является основной причиной проблемы. Во-первых, у него нет ключа. Во-вторых, нет четкой причины, по которой может иметь смысл присоединять его к себе по тематическому идентификатору. Похоже, результатом будет просто декартово произведение имен или голосов. В чем настоящая проблема?   -  person Mike Sherrill 'Cat Recall'    schedule 10.06.2013
comment
Интересно, есть ли что-то в том, как обрабатывается соединение с фильтром, что требует больше памяти. Можешь попробовать поднять work_mem? Я знаю, что на самом деле это не решение, но если это поможет, это может быть ключом к тому, что не так.   -  person Tom Anderson    schedule 10.06.2013
comment
Под этим вы подразумеваете таблицу votes? Ну да — я мог бы определить ключ PRIMARY KEY (topic_id, name), но в данном случае это ничего бы не изменило. И да — результат представляет собой декартово произведение имен и голосов, которое я использую в качестве промежуточного шага при расчете отношения между избирателями (например, часто ли люди А и Б голосуют вместе?). Так что в этом случае да — мой реальная проблема в том, что фильтрация очень медленная.   -  person David Wolever    schedule 10.06.2013
comment
Не могли бы вы запустить VACUUM ANALYZE votes без FULL? И, пожалуйста, включите вывод EXPLAIN (ANALYZE, BUFFERS) …, он будет работать только в версиях 9.*.   -  person vyegorov    schedule 10.06.2013
comment
@TomAnderson Когда я устанавливаю working_mem = 100MB, кажется, ничего не меняется.   -  person David Wolever    schedule 10.06.2013
comment
@vyegorov хорошо, обновил вопрос, включив в него EXPLAIN (ANALYZE, BUFFERS) после запуска VACUUM ANALYZE votes.   -  person David Wolever    schedule 10.06.2013
comment
@MikeSherrill'Catcall': настоящая проблема, похоже, связана с проблемой моделирования данных. name кажется скрытым доменом (должен быть (целочисленный) FK для таблицы лиц, а голосование может быть целым, или даже логическим, или перечислимым)   -  person wildplasser    schedule 10.06.2013
comment
@wildplasser да, я мог бы CREATE TABLE persons (id SERIAL PRIMARY KEY, name VARCHAR(64))… Но это усложнило бы мой вариант использования (по разным веским причинам).   -  person David Wolever    schedule 10.06.2013
comment
Нет, это только сделает вашу оставшуюся жизнь более комфортной. И всегда есть VIEW, которые помогут вам.   -  person wildplasser    schedule 10.06.2013
comment
Стоит взглянуть на stackoverflow.com/tags/postgresql-performance/info.   -  person Craig Ringer    schedule 10.06.2013

Ответы (2)


arrow_upward
5
arrow_downward

Да, сравнение текстов иногда тормозит. Вы можете попробовать:

SELECT * FROM vote_pairs WHERE name1 > name2 collate "C";

Это должно быть несколько быстрее, потому что оно не принимает во внимание правила сравнения, специфичные для локали. Кроме того, ваш результат анализа объяснения предполагает, что ваши shared_buffers могут быть установлены слишком низко.

person maniek    schedule 09.06.2013
comment
Ву! Это сработало — добавление collate доводит скорость запроса со сравнением до той же (приблизительной) скорости, что и без запроса. Спасибо! - person David Wolever; 10.06.2013
comment
Если вы собираетесь сделать это, вам, вероятно, следует добавить ограничение CHECK для столбца name, которое ограничивает его 7-битным диапазоном символов ASCII, который является общим для (почти - ааа, Shift-JIS) кодировок. Если это неприемлемо для приложения, то и C сортировка тоже не подойдет. - person Craig Ringer; 10.06.2013
comment
Спасибо за предложение. Однако в данном случае это не полноценное приложение… Просто разовый анализ данных. Но я буду иметь это в виду на будущее. - person David Wolever; 10.06.2013

arrow_upward
1
arrow_downward

Я предполагаю, что медлительность добавляется, потому что фильтр v1.name < v2.name добавляет некоторый фиксированный набор операций для каждой строки в объединении перекрестного произведения.

Более эффективной операцией будет проверка v1.name <> v2.name, но тогда вы получите повторяющиеся результаты, такие как (A,B), (B,A). Затем мы можем добавить обратно v1.name < v2.name в предложение WHERE, которое уберет дубликаты и, как мы надеемся, уменьшит количество строк благодаря нашему упрощенному фильтру.

Попробуй это:

CREATE OR REPLACE VIEW vote_pairs AS
    SELECT
        v1.name as name1,
        v2.name as name2
    FROM votes AS v1
    JOIN votes AS v2
        ON v1.topic_id = v2.topic_id AND v1.name <> v2.name
    WHERE v1.name < v2.name;

(Редактировать: кажется, что COLLATE "C" - это путь, но я оставлю этот ответ, потому что это хороший прием для уменьшения воздействия строк на медленные операции.)

person shazow    schedule 09.06.2013
comment
Ok! Это немного помогает — сокращает время выполнения с ~7 секунд до ~4 секунд… Но это все равно намного медленнее, чем без сравнения. - person David Wolever; 10.06.2013