Index Only Scan vs Cache

From: Andy Colson <andy(at)squeakycode(dot)net>
To: pgsql <pgsql-general(at)postgresql(dot)org>
Subject: Index Only Scan vs Cache
Date: 2015-07-09 16:46:50
Message-ID: 559EA57A.9080504@squeakycode.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andy Colson 2015-07-09 16:50:44 Re: regexp_matches for digit
Previous Message Jimit Amin 2015-07-09 16:46:29 Re: regexp_matches for digit