Неправильно выбирается индекс

Wicked · 17 Апр 2008

Неправильно выбирается индекс

... что приводит к увеличению времени выполнения запроса в ~50000 раз

Есть таблица на 5гб с постами из высосанных фидов. Мы из нее хотим показать оные примерно так, как они показываются в гугл ридере. Т.е. where feed_id in (...) and pub_date between $a and $b.

Таблица:

Код:

db=# \d+ ext_feeder_item
                                             Table "public.ext_feeder_item"
     Column     |           Type           |                          Modifiers                           | Description
----------------+--------------------------+--------------------------------------------------------------+-------------
 id             | bigint                   | not null default nextval('ext_feeder_item_id_seq'::regclass) |
 feed_id        | bigint                   | not null                                                     |
 title          | text                     | not null                                                     |
 description    | text                     |                                                              |
 pub_date       | timestamp with time zone |                                                              |
Indexes:
    "ext_feeder_item_pkey" PRIMARY KEY, btree (id)
    "ext_feeder_item_feed_id_pub_date_idx" btree (feed_id, pub_date)
    "ext_feeder_item_pub_date_idx" btree (pub_date)

Статистика:
Всего постов в таблице - 3.3млн.
Всего постов для инетерсующих нас фидах:

Код:

SELECT count(*) FROM  ext_feeder_item AS i WHERE i.feed_id IN (93912, 93922, 93930, 50621, 93946, 93955, 93964, 93971, 93976, 93981, 93988, 93991);
= 52.

Всего постов из всех фидов, попадающих в интересующий нас диапазон дат

Код:

SELECT count(*) FROM  ext_feeder_item AS i WHERE '2008-01-16 00:23:04+00' <= pub_date AND pub_date <= '2008-03-16 18:17:10+00';
= 2.9млн

При пересечении условий (через AND) у нас тоже получается 52 записи, т.е. диапазоном дат не отсекается ни одной.

Ок... теперь сам запрос:

Код:

db=# EXPLAIN ANALYZE SELECT  id FROM  ext_feeder_item AS i WHERE  i.feed_id IN (93912, 93922, 93930, 50621, 93946, 93955, 93964, 93971, 93976, 93981, 93988, 93991) \
AND '2008-01-16 00:23:04+00' <= pub_date AND pub_date <= '2008-03-16 18:17:10+00' ORDER BY  i.pub_date ASC  LIMIT 11  OFFSET 41;
                                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1359.01..1723.62 rows=11 width=16) (actual time=66178.925..78270.865 rows=11 loops=1)
   ->  Index Scan using ext_feeder_item_pub_date_idx on ext_feeder_item i  (cost=0.00..780335.03 rows=23542 width=16) \
                             (actual time=44.502..78270.762 rows=52 l
         Index Cond: (('2008-01-16 06:23:04+06'::timestamp with time zone <= pub_date) \
                          AND (pub_date <= '2008-03-17 00:17:10+06'::timestamp with time zone))
         Filter: (feed_id = ANY ('{93912,93922,93930,50621,93946,93955,93964,93971,93976,93981,93988,93991}'::bigint[]))
 Total runtime: 78270.946 ms

Меня это не устраивает. Для теста стартую транзакцию и дропаю индекс, который зачем-то используется этим запросом: db=# drop index "ext_feeder_item_pub_date_idx";
Перезапускаем запрос:

Код:

                                                                                                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=76288.36..76288.39 rows=11 width=16) (actual time=45.915..45.946 rows=11 loops=1)
   ->  Sort  (cost=76288.26..76347.11 rows=23542 width=16) (actual time=45.834..45.885 rows=52 loops=1)
         Sort Key: pub_date
         Sort Method:  quicksort  Memory: 19kB
         ->  Bitmap Heap Scan on ext_feeder_item i  (cost=687.70..75499.55 rows=23542 width=16) (actual time=39.338..45.698 rows=52 loops=1)
               Recheck Cond: ((feed_id = ANY ('{93912,93922,93930,50621,93946,93955,93964,93971,93976,93981,93988,93991}'::bigint[])) \
                            AND ('2008-01-16 06:23:04+06'::timestamp with time zone <= pub_date) AND (pub_date <= '2008-03-17 00:17:10+06'::timestamp with time zone))
               ->  Bitmap Index Scan on ext_feeder_item_feed_id_pub_date_idx  (cost=0.00..681.82 rows=23542 width=0) (actual time=39.288..39.288 rows=52 loops=1)
                     Index Cond: ((feed_id = ANY ('{93912,93922,93930,50621,93946,93955,93964,93971,93976,93981,93988,93991}'::bigint[])) \
                            AND ('2008-01-16 06:23:04+06'::timestamp with time zone <= pub_date) AND (pub_date <= '2008-03-17 00:17:10+06'::timestamp with time zone))
 Total runtime: 46.025 ms // без explain analyze: Time: 1.404 ms

Собственно, мне, как обычно, интересны 2 вещи: кто виноват и что делать (кроме как дропать вредный индекс)?

Sad Spirit · 17 Апр 2008

Я бы предложил для начала поиграться с

Код:

ALTER TABLE ... SET STATISTICS ...

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

Wicked · 17 Апр 2008

set statistics увеличивает кол-во срезов в гистограмме?

ибо видно, что кол-во выбираемых записей оценивается неправильно

можешь ткнуть носом в...?

Я эти анализы пока не очень понимаю.

Sad Spirit · 17 Апр 2008

Вроде того...

Вот здесь, например, оценка

Код:

(cost=76288.26..76347.11 rows=23542 width=16) (actual time=45.834..45.885 rows=52 loops=1)

расходится с реальностью почти на 3 порядка.

Wicked · 17 Апр 2008

Sad Spirit
Спасибо... Вроде разобрался

За set statistics тоже спасибо.

В итоге получилось так:
Если убиваю сосавной индекс (feed_id, pub_date)

Код:

 Limit  (cost=33771.86..42832.61 rows=11 width=16) (actual time=75181.014..86913.602 rows=11 loops=1)
   ->  Index Scan using ext_feeder_item_pub_date_idx on ext_feeder_item i  (cost=0.00..807229.89 rows=980 width=16) (actual time=0.156..86913.505 rows=52 loops=1)
         Index Cond: (('2008-01-16 06:23:04+06'::timestamp with time zone <= pub_date) AND (pub_date <= '2008-03-17 00:17:10+06'::timestamp with time zone))
         Filter: (feed_id = ANY ('{93912,93922,93930,50621,93946,93955,93964,93971,93976,93981,93988,93991}'::bigint[]))
 Total runtime: 86913.688 ms

Если же индекс оставляю (роллбэчу транзакцию с дропом индекса), то так:

Код:

 Limit  (cost=3890.15..3890.17 rows=11 width=16) (actual time=0.541..0.572 rows=11 loops=1)
   ->  Sort  (cost=3890.04..3892.49 rows=980 width=16) (actual time=0.460..0.512 rows=52 loops=1)
         Sort Key: pub_date
         Sort Method:  quicksort  Memory: 19kB
         ->  Bitmap Heap Scan on ext_feeder_item i  (cost=64.06..3857.21 rows=980 width=16) (actual time=0.158..0.340 rows=52 loops=1)
               Recheck Cond: ((feed_id = ANY ('{93912,93922,93930,50621,93946,93955,93964,93971,93976,93981,93988,93991}'::bigint[])) AND ('2008-01-16 06:23:04+06'::timestamp with time zone <= pub_date) AND (pub_date <= '2008-03-17 00:17:10+06'::timestamp with time zone))
               ->  Bitmap Index Scan on ext_feeder_item_feed_id_pub_date_idx  (cost=0.00..63.82 rows=980 width=0) (actual time=0.138..0.138 rows=52 loops=1)
                     Index Cond: ((feed_id = ANY ('{93912,93922,93930,50621,93946,93955,93964,93971,93976,93981,93988,93991}'::bigint[])) AND ('2008-01-16 06:23:04+06'::timestamp with time zone <= pub_date) AND (pub_date <= '2008-03-17 00:17:10+06'::timestamp with time zone))
 Total runtime: 0.642 ms

Неправильно выбирается индекс

Wicked

Новичок

Sad Spirit

мизантроп (Старожил PHPClub)

Wicked

Новичок

Sad Spirit

мизантроп (Старожил PHPClub)

Wicked

Новичок