On 7/14/2015 1:19 PM, Marc Mamin wrote:
On 7/9/2015 12:41 PM, Tom Lane wrote:
Andy Colson <andy@xxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
