Я работаю с набором данных HackerNews в Postgres. Около 17 миллионов строк, из них 14,5 миллионов комментариев и около 2,5 миллионов историй. Есть очень активный пользователь по имени «rbanffy», у которого 25 тысяч материалов, примерно поровну разделенных историй/комментариев. И «by», и «type» имеют отдельные индексы.
У меня есть запрос:
SELECT *
FROM "hn_items"
WHERE by = 'rbanffy'
and type = 'story'
ORDER BY id DESC
LIMIT 20 OFFSET 0
Это работает быстро (используется индекс «by»). Если я изменю тип на «комментарий», это будет очень медленно. Судя по объяснению, он не использует ни один индекс и выполняет сканирование.
Limit (cost=0.56..56948.32 rows=20 width=1937)
-> Index Scan using hn_items_pkey on hn_items (cost=0.56..45823012.32 rows=16093 width=1937)
Filter: (((by)::text = 'rbanffy'::text) AND ((type)::text = 'comment'::text))
Если я изменю запрос на type||''='comment'
, тогда он будет использовать индекс 'by' и будет выполняться быстро.
Почему это происходит? Я понимаю из https://stackoverflow.com/a/309814/214545, что такой взлом подразумевает что-то неправильно. Но я не знаю что.
РЕДАКТИРОВАТЬ:
Это объяснение для type='story'
Limit (cost=72553.07..72553.12 rows=20 width=1255)
-> Sort (cost=72553.07..72561.25 rows=3271 width=1255)
Sort Key: id DESC
-> Bitmap Heap Scan on hn_items (cost=814.59..72466.03 rows=3271 width=1255)
Recheck Cond: ((by)::text = 'rbanffy'::text)
Filter: ((type)::text = 'story'::text)
-> Bitmap Index Scan on hn_items_by_index (cost=0.00..813.77 rows=19361 width=0)
Index Cond: ((by)::text = 'rbanffy'::text)
РЕДАКТИРОВАТЬ: ОБЪЯСНИТЬ (АНАЛИЗИРОВАТЬ, БУФЕРЫ)
Limit (cost=0.56..59510.10 rows=20 width=1255) (actual time=20.856..545.282 rows=20 loops=1)
Buffers: shared hit=21597 read=2658 dirtied=32
-> Index Scan using hn_items_pkey on hn_items (cost=0.56..47780210.70 rows=16058 width=1255) (actual time=20.855..545.271 rows=20 loops=1)
Filter: (((by)::text = 'rbanffy'::text) AND ((type)::text = 'comment'::text))
Rows Removed by Filter: 46798
Buffers: shared hit=21597 read=2658 dirtied=32
Planning time: 0.173 ms
Execution time: 545.318 ms
РЕДАКТИРОВАТЬ: ОБЪЯСНИТЬ (АНАЛИЗИРОВАТЬ, БУФЕРЫ) type='story'
Limit (cost=72553.07..72553.12 rows=20 width=1255) (actual time=44.121..44.127 rows=20 loops=1)
Buffers: shared hit=20137
-> Sort (cost=72553.07..72561.25 rows=3271 width=1255) (actual time=44.120..44.123 rows=20 loops=1)
Sort Key: id DESC
Sort Method: top-N heapsort Memory: 42kB
Buffers: shared hit=20137
-> Bitmap Heap Scan on hn_items (cost=814.59..72466.03 rows=3271 width=1255) (actual time=6.778..37.774 rows=11630 loops=1)
Recheck Cond: ((by)::text = 'rbanffy'::text)
Filter: ((type)::text = 'story'::text)
Rows Removed by Filter: 12587
Heap Blocks: exact=19985
Buffers: shared hit=20137
-> Bitmap Index Scan on hn_items_by_index (cost=0.00..813.77 rows=19361 width=0) (actual time=3.812..3.812 rows=24387 loops=1)
Index Cond: ((by)::text = 'rbanffy'::text)
Buffers: shared hit=152
Planning time: 0.156 ms
Execution time: 44.422 ms
РЕДАКТИРОВАТЬ: последние результаты теста. Я играл с запросом type='comment'
и заметил, что если изменить предел на более высокое число, например 100, он использует индекс by
. Я играл со значениями, пока не нашел критическое число «47». Если у меня было ограничение 47, то использовался индекс by
, если у меня было ограничение 46, то это было полное сканирование. Я предполагаю, что это число не волшебное, просто это порог для моего набора данных или какой-то другой переменной, которую я не знаю. Я не знаю, поможет ли это.
EXPLAIN (ANALYZE, BUFFERS)
вывод? - person Laurenz Albe   schedule 29.05.2018hn_items_by_index
. Можете ли вы показать результатexplain (analyze, buffers)
для запроса fast? - person a_horse_with_no_name   schedule 29.05.2018