Блог

Главная - Вопросы по программированию - Медленный запрос postgresql, если предложение where не соответствует строкам

Медленный запрос postgresql, если предложение where не соответствует строкам

#postgresql

#postgresql

Вопрос:

У меня есть простая таблица в базе данных PostgreSQL 9.0.3, которая содержит данные, полученные от контроллера ветряной турбины. Каждая строка представляет значение конкретного датчика в определенный момент времени. В настоящее время в таблице содержится около 90 миллионов строк:

 wtdata=> d integer_data
          Table "public.integer_data"
 Column |           Type           | Modifiers 
-------- -------------------------- -----------
 date   | timestamp with time zone | not null
 name   | character varying(64)    | not null
 value  | integer                  | not null
Indexes:
    "integer_data_pkey" PRIMARY KEY, btree (date, name)
    "integer_data_date_idx" btree (date)
    "integer_data_name_idx" btree (name)

Один запрос, который мне нужен, — это найти время последнего обновления переменной:

 select max(date) from integer_data where name = '<name of variable>';

Этот запрос отлично работает при поиске переменной, которая существует в таблице:

 wtdata=> select max(date) from integer_data where name = 'STATUS_OF_OUTPUTS_UINT16';
          max           
------------------------
 2011-04-11 02:01:40-05
(1 row)

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

 select max(date) from integer_data where name = 'Message';

Я позволяю запросу выполняться часами, а иногда и днями, и конца этому не видно. В таблице нет строк с именем = ‘Message’:

 wtdata=> select count(*) from integer_data where name = 'Message';
 count 
-------
     0
(1 row)

Я не понимаю, почему один запрос выполняется быстро, а другой занимает вечность. По какой-то причине запрос каким-то образом вынужден сканировать всю таблицу целиком?

 wtdata=> explain select max(date) from integer_data where name = 'Message';
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Result  (cost=13.67..13.68 rows=1 width=0)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..13.67 rows=1 width=8)
           ->  Index Scan Backward using integer_data_pkey on integer_data  (cost=0.00..6362849.53 rows=465452 width=8)
                 Index Cond: ((date IS NOT NULL) AND ((name)::text = 'Message'::text))
(5 rows)

Вот план запроса для быстрого запроса:

 wtdata=> explain select max(date) from integer_data where name = 'STATUS_OF_OUTPUTS_UINT16';
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Result  (cost=4.64..4.65 rows=1 width=0)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..4.64 rows=1 width=8)
           ->  Index Scan Backward using integer_data_pkey on integer_data  (cost=0.00..16988170.38 rows=3659570 width=8)
                 Index Cond: ((date IS NOT NULL) AND ((name)::text = 'STATUS_OF_OUTPUTS_UINT16'::text))
(5 rows)

Комментарии:

1. Вы запускали analyze для этой таблицы?

2. Да, я запустил analyze вручную, плюс запущен autovacuum.

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

4. Вы получите немного больше информации, если будете использовать EXPLAIN ANALYZE вместо EXPLAIN . EXPLAIN просто возвращает план запроса. EXPLAIN ANALYZE фактически выполняет запрос, а затем возвращает план запроса с информацией о времени в реальном мире.

5. Ведет ли это себя как-то по-другому?: SELECT date FROM from integer_data where name = 'Message' ORDER BY DATE DESC LIMIT 1;

Ответ №1:

Измените первичный ключ на (имя, дата).