27.11.2019, 15:42, "Andrei Zhidenkov" <andrei.zhidenkov@n26.com>:
> At this point I disagree. It’s faster to fetch one row using seq scan that using index scan as well as fetching
numberof consecutive rows is faster via seq scan. Index scan is not always faster.
>
Yes, you are right in common: Index scan is not always faster.
But in my current case I have table with ~8k tuples (309 relpages) and to find 1 row in the worst case (when this row
inthe last page) we need 309 heap fetches.
For the same table to find one unique tuple in index we need about 4 (not sure about this number) index fetches and 1
heapfetch. That's why I decided that index scan is faster.
When I was thinking about your point I looked at pg_class table to determine relpages for both index and table. Index
hadmore than 700 pages... Index bloat?
So, I dropped and recreated index. Now it takes only 33 pages. And yes, my plan is now using index only scan
Ok, last question here - even with bloated index overall number of index fetches must be much lower than 309. Am I
wrong?
