SELECT для 80 миллионов строк занимает слишком много времени

Таблица:

                       Table "public.hugetable"
 Column  |         Type          | Modifiers | Storage  | Description 
---------+-----------------------+-----------+----------+-------------
 reqid   | character varying(15) |           | extended | 
 browser | character varying(15) |           | extended | 
 a       | smallint              |           | plain    | 
 b       | smallint              |           | plain    | 
 metarr  | smallint[]            |           | extended |

Количество строк: 80 миллионов

Индексы: Нет

Объясните:

testdb=> EXPLAIN (ANALYZE,BUFFERS) select b from hugetable;
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Seq Scan on hugetable  (cost=0.00..6514286.08 rows=80000008 width=2) (actual time=0.009..598004.456 rows=80000000 loops=1)
   Buffers: shared hit=472831 read=5241455
 Total runtime: 674134.766 ms

metarr smallint[] содержит 250 элементов.

Запрос занимает одинаковое количество времени с select b from hugetable where a=someval или select metric[199] from hugetable

Технические характеристики сервера:

db.m3.xlarge
Type:Type   Standard - Current Generation
vCPU:Number of virtual cores    4 vCPU
Memory: 15 GiB

Я никогда не работал с таким большим набором данных, поэтому не уверен, что 10 минут — это нормально для такого рода запросов.

На практике будет еще один столбец (datetime). Таблица будет содержать ~80 миллионов записей за 1 полный день, а запросы всегда будут иметь вид SELECT metarr[someindex] from hugetable where datetimecolumn > something and datetimecolumn <something.

Что я могу сделать, чтобы сделать это быстрее? Кажется, что как только я добавлю столбец даты и времени и запрошу определенный период времени, это все равно займет огромное количество времени!


postgresql performance postgresql-performance
person user1265125    schedule 09.10.2014    source источник
comment
Индексация таблицы будет иметь большое значение для сокращения времени выполнения запросов.   -  person danmullen    schedule 09.10.2014
comment
Индексация таблицы по чему? И является ли 600 секунд разумным временем без индексации?   -  person user1265125    schedule 09.10.2014
comment
Ваш запрос запрашивает все данные, вот что вы получаете. И тогда от производительности вашей системы зависит, насколько быстро это можно сделать.   -  person Frank Heikens    schedule 09.10.2014
comment
Учитывая, что $$ не является проблемой, какой конкретный атрибут мне нужно увеличить, чтобы повысить производительность для чего-то подобного? операций ввода-вывода в секунду? aws.amazon.com/rds/pricing   -  person user1265125    schedule 09.10.2014
comment
@danmullen Ерунда - самый быстрый способ получить полное содержимое таблицы почти всегда — это seqscan, поэтому приведенный здесь пример вообще не выиграет от индекса. Вы правы в том, что практическая... часть выиграет от индекса btree для datetimecolumn.   -  person Craig Ringer    schedule 09.10.2014
comment
Как написано, это все о производительности последовательного чтения. Подготовленные EBS операции ввода-вывода помогут, но будут стоить много долларов. Серьезно много. Самое большое улучшение, которое вы можете сделать для такого рода вещей, — это перейти с AWS EC2 на что-то с хорошим хранилищем. Или используйте что-то вроде хранилища экземпляра экземпляра с высоким уровнем ввода-вывода и полагайтесь на репликацию и отработку отказа для восстановления в случае сбоя и потери содержимого хранилища экземпляра.   -  person Craig Ringer    schedule 09.10.2014
comment
Кстати, что выводит SELECT pg_size_pretty(pg_relation_size('hugetable')), pg_size_pretty(pg_total_relation_size('hugetable')); ?   -  person Craig Ringer    schedule 09.10.2014
comment
Честно говоря, @Craig Ringer, я имел в виду повседневную производительность при доступе к этой таблице.   -  person danmullen    schedule 09.10.2014

Ответы (2)


arrow_upward
0
arrow_downward

Таблица на диске больше, чем ваша оперативная память. Таким образом, таблица не может быть полностью кэширована в оперативной памяти. Увеличьте размер оперативной памяти, чтобы он соответствовал вашему рабочему набору. Это удалит все IO.

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

Теперь вы можете оказаться узким местом в сети между БД и приложением.

person usr    schedule 09.10.2014

arrow_upward
0
arrow_downward

Как повысить производительность запросов к базе данных

  1. Индексировать столбец, который используется для сравнения (например, столбец даты должен быть проиндексирован в обоих примерах).

    SELECT * FROM X WHERE X.date = 2014

    or

    SELECT * FROM X INNER JOIN Y ON X.date = Y.date

  2. горизонтальное разделение (осколок) таблицы на несколько блоков (например, ежедневная ротация) и размещение каждого блока на отдельной машине.

  3. реплицируйте базу данных, продублировав раздел db на нескольких машинах. это не сильно поможет в вашем случае, если у вас нет одновременных запросов с разными критериями

  4. используйте твердотельные накопители с высокой скоростью ввода-вывода в секунду (AWS называет их выделенным вводом-выводом).

  5. для дат используйте тип datetime, он имеет лучшую производительность, чем int http://gpshumano.blogs.dri.pt/2009/07/06/mysql-datetime-vs-timestamp-vs-int-performance-and-бенчмаркинг-с-myisam/

  6. используйте memcached, redis, ..etc для кэширования результатов для более быстрого поиска (если один и тот же запрос будет выполняться более одного раза)

person Mahmoud.Mahfouz    schedule 09.10.2014