Index Only Scan vs Cache

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

--
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/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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Does PG has its own data cache? I don't think so.
I think PG just using the filesystem cache.

On Fri, Jul 10, 2015 at 2:06 AM, Andy Colson <andy@squeakycode.net> wrote:

2015-07-14 11:59 GMT+02:00 Shujie Shang <sshang@pivotal.io>:

Does PG has its own data cache? I don't think so.

PG has own data cache - see shared_buffers

Regards

Pavel

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

--
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Jul 14, 2015 at 2:19 PM, Marc Mamin <M.Mamin@intershop.de> wrote:

Any rule of the thumb with which number of pages per relation it is worth
to start indexing ?

The code for the monitoring tool check_postgres uses table size larger than
5.12kb as a rule of thumb, expecting that for tables smaller than 5.12kb
the planner may choose a sequential scan instead because the table is so
small. check_postgres was written a long time ago though, so someone who is
more familiar with the optimizer may be able to provide a better estimate.

*Will J. Dunn*
*willjdunn.com <http://willjdunn.com&gt;*

On Tue, Jul 14, 2015 at 2:19 PM, Marc Mamin <M.Mamin@intershop.de> wrote: