Thread: Index Only Scan vs Cache

Hi All.

I have a website db that is 90% read-only.  I have 50 (or so) tiny
lookup tables, something like:

\d m_zone
  Column  |  Type   | Modifiers
---------+---------+-----------
  code    | integer | not null
  zone_id | text    |
  descr   | text    |

This one has less than 10 rows, others might hit 100, I doubt any would
make it over 500 rows.

All of them have an index on code.  I'm thinking of dropping it and
creating the index on (code, descr) so that I'd get Index Only Scans.

I host 100 counties (One database, 100 schemas) each will have these 50
tables, so 5,000 small lookup tables.

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?

There is very little difference speed wise, my purpose is to reduce
cache usage.  Right now I assume I have 5000 tables + 5000 indexes in cache.

I'd like to try to cut that down to either 5000 tables, or 5000 indexes
(not both).

Thanks for your time,

-Andy

Andy Colson <andy@squeakycode.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?

The table blocks would fall out of cache if they're never touched.

            regards, tom lane

On 7/9/2015 12:41 PM, Tom Lane wrote:
> Andy Colson <andy@squeakycode.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?
>
> The table blocks would fall out of cache if they're never touched.
>
>             regards, tom lane
>
>

Sweet!  Thanks Tom.

>On 7/9/2015 12:41 PM, Tom Lane wrote:
>> Andy Colson <andy@squeakycode.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

>> The table blocks would fall out of cache if they're never touched.
>>
>>                        regards, tom lane
>>
>>
>
>Sweet!  Thanks Tom.
>
>
>--
>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general
>

On 7/14/2015 1:19 PM, Marc Mamin wrote:
>
>> On 7/9/2015 12:41 PM, Tom Lane wrote:
>>> Andy Colson <andy@squeakycode.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