PostgreSQL 9.2 10x медленнее, чем MySQL 5.5? [закрытый]

У меня на ноутбуке установлены PostgreSQL 9.2 и MySQL 5.5 (InnoDB). Оба ядра баз данных используют установку по умолчанию и заполняются из одного CSV-файла. У меня есть таблица sales_reports с ca. 700к строк.

Сценарий 1:

  • Следующий запрос:

    Выберите название, год, регион, филиал из группы sales_reports по названию, год, регион, филиал;

  • PostgreSQL 9.2: Общее время выполнения запроса: 42.14 сек, 18064 строки извлечено

  • PostgreSQL объяснить: 
    Group  (cost=165091.16..174275.61 rows=73476 width=58) (actual time=35196.959..41896.739 rows=18064 loops=1)
->  Sort  (cost=165091.16..166928.05 rows=734756 width=58) (actual time=35196.956..41704.549 rows=734756 loops=1)
    Sort Key: name, year, region, branch
    Sort Method: external merge  Disk: 49920kB
    ->  Seq Scan on sales_reports  (cost=0.00..38249.56 rows=734756 width=58) (actual time=0.048..282.331 rows=734756 loops=1)
Total runtime: 41906.628 ms
  • MySQL 5.5: Общее время выполнения запроса: 4.4 сек, 18064 строки извлечены
  • MySQL объяснить: 
    +----+-------------+---------------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table         | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+----+-------------+---------------+------+---------------+------+---------+------+--------+---------------------------------+
|  1 | SIMPLE      | sales_reports | ALL  | NULL          | NULL | NULL    | NULL | 729433 | Using temporary; Using filesort |
+----+-------------+---------------+------+---------------+------+---------+------+--------+---------------------------------+
  • PostgreSQL в 10 раз медленнее

Сценарий 2:

  • следующий запрос: 
    select name, year, region, branch, sum(sale) as sale from sales_reports group by name, year, region, branch;
  • PostgreSQL 9.2: Общее время выполнения запроса: 42.51 сек, получено 18064 строки
  • PostgreSQL объяснить: 
    GroupAggregate  (cost=165091.16..176847.26 rows=73476 width=64) (actual time=35160.911..42254.060 rows=18064 loops=1)
->  Sort  (cost=165091.16..166928.05 rows=734756 width=64) (actual time=35160.489..41857.986 rows=734756 loops=1)
    Sort Key: name, year, region, branch
    Sort Method: external merge  Disk: 54760kB
    ->  Seq Scan on sales_reports  (cost=0.00..38249.56 rows=734756 width=64) (actual time=0.047..296.347 rows=734756 loops=1)
Total runtime: 42264.790 ms
  • MySQL 5.5: Общее время выполнения запроса: 8.15 сек, 18064 строки извлечено
  • MySQL объяснить: 
    +----+-------------+---------------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table         | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+----+-------------+---------------+------+---------------+------+---------+------+--------+---------------------------------+
|  1 | SIMPLE      | sales_reports | ALL  | NULL          | NULL | NULL    | NULL | 729433 | Using temporary; Using filesort |
+----+-------------+---------------+------+---------------+------+---------+------+--------+---------------------------------+
  • PostgreSQL в 5 раз медленнее

Сценарий 3:

  • следующий запрос: 
    select name, year, region, sum(sale) as sale from sales_reports group by name, year, region;
  • PostgreSQL 9.2: Общее время выполнения запроса: 1 сек, получено 18064 строки
  • PostgreSQL объяснить: 
    HashAggregate  (cost=45597.12..45655.62 rows=5850 width=37) (actual time=758.396..759.756 rows=4644 loops=1)
->  Seq Scan on sales_reports  (cost=0.00..38249.56 rows=734756 width=37) (actual time=0.061..116.541 rows=734756 loops=1)
Total runtime: 760.133 ms
  • MySQL 5.5: Общее время выполнения запроса: 5.8 сек, 18064 строки извлечены
  • MySQL объяснить: 
    +----+-------------+---------------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table         | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+----+-------------+---------------+------+---------------+------+---------+------+--------+---------------------------------+
|  1 | SIMPLE      | sales_reports | ALL  | NULL          | NULL | NULL    | NULL | 729433 | Using temporary; Using filesort |
+----+-------------+---------------+------+---------------+------+---------+------+--------+---------------------------------+
  • PostgreSQL в 5 раз быстрее

Есть идеи, почему первые два сценарии настолько медленные на PostgreSQL?

Кстати, я создал индексы для полей, которые я использую в запросе на PostgreSQL, я не создавал никаких индексов на MySQL.

Спасибо,

Марек

2 2
mysql postgresql postgresql-9.2

2 ответа:

Конфигурация postgresql по умолчанию довольно консервативна. Для начала попробуйте увеличить shared_buffers до 1 ГБ. (Помните о перезапуске сервера, чтобы изменения вступили в силу.)

Также попробуйте увеличить work_mem, пока GroupAggregate не переключится на HashAggregate в объяснении. Вы можете изменить это без перезагрузки.

Предупреждение: перед тем, как возиться с настройками в производстве, пожалуйста, прочитайте дружественное руководство, есть некоторые готы, связанные.

13

Несколько вещей:

  1. при создании индексов на столбцах varchar/text (например, name, вероятно, есть) может быть быстрее использовать индекс хэш-типа в PostgreSQL для операторов group by
  2. Если mysql использует таблицы MyISAM, это, вероятно, будет быстрее для не параллельных нагрузок, потому что MyISAM игнорирует много вещей безопасности, которые InnoDB и PgSQL имеют.
  3. Вы должны попробовать проанализировать вакуум на вашей базе данных pgsql.
  4. Вы можете создать составной индекс по имени, году, региону, филиалу, чтобы сделайте эту группу по частям быстрее.
1