Re: Index Only Scan vs Cache

On 7/14/2015 1:19 PM, Marc Mamin wrote:
>
>> On 7/9/2015 12:41 PM, Tom Lane wrote:
>>> Andy Colson <andy(at)squeakycode(dot)net> writes:
>>>> My question is: Will PG cache only the index (assuming it can
>>>> always do an Index Only Scan), or will it cache the table as
>>>> well?
>
> I'm not sure that indexes on tiny tables are useful. They raise the
> options to consider by the query planner, which has its small cost
> too. I'd be interested on other opinions on this. Any rule of the
> thumb with which number of pages per relation it is worth to start
> indexing ?
>
> And still another question: I've have tiny static tables too, that
> never got analyzed. Can this fool the query planner in a negative way
> ?
>
> regards,
>
> Marc Mamin
>

They can be. A unique constraint to ensue correctness for example. In
my case some of my "tiny" tables can be different sizes from 100 to 500
rows. I want indexes on those in case they get bigger and start to get
slow. PG can figure out when to use and not to use the index. I'd
rather have the safety net.

> And still another question: I've have tiny static tables too, that never got analyzed.
> Can this fool the query planner in a negative way ?

I would say yes. A tiny table is quickest when it is table scanned, but
its only going to be a few milliseconds more if it uses the index (also
depending on how much the table and index are cached). For a small
table I can't imagine the speed difference would even be noticeable.

In my testing, with tables of 100 rows the speed was almost the same
with an index, a covering index, and no index.

-Andy