Re: [PERFORM] Number of characters in column preventing index usage

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: [PERFORM] Number of characters in column preventing index usage
Дата
Msg-id CAKFQuwaDX7vJSrX2Rkz9njXseFVRe8eAStOvCQzqd4pc_vhcNg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [PERFORM] Number of characters in column preventing index usage  (Tomas Vondra <tomas.vondra@2ndquadrant.com>)
Ответы Re: [PERFORM] Number of characters in column preventing index usage  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
Дерево обсуждения
On Fri, Feb 17, 2017 at 3:49 PM, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote: 
 That may seem a bit strange, but I'd bet it finds the short value in some statistic (MCV, histogram) ans so can provide very accurate estimate.

​​ ->  Index Only Scan using tab_idx1 on tab  (cost=0.27..8.29 rows=1
width=0) (actual time=0.043..0.043 rows=0 loops=1)

​I'm not seeing how any of the statistic columns would capture a value that doesn't actually appear in the table...(actual ... row=0)​

Unless there is some prefix matching going on here since the short value is a substring(1, n) of the longer one which does appear 5 times.

​I guess maybe because the value doesn't appear it uses the index (via IOS) to confirm absence (or near absence, i.e., 1) while, knowing the larger value appears 5 times out of 223, it decides a quick table scan is faster than any form of double-lookup (whether on the visibility map or the heap).

​https://www.postgresql.org/docs/9.6/static/indexes-index-only-scans.html​

​David J.​

В списке pgsql-performance по дате отправления:

Предыдущее
От: Hustler DBA
Дата:
Сообщение: Re: [PERFORM] Number of characters in column preventing index usage
Следующее
От: Tom Lane
Дата:
Сообщение: Re: [PERFORM] Number of characters in column preventing index usage