Re: Index Only Scan vs Cache

On Tue, Jul 14, 2015 at 2:19 PM, Marc Mamin <M(dot)Mamin(at)intershop(dot)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>*

On Tue, Jul 14, 2015 at 2:19 PM, Marc Mamin <M(dot)Mamin(at)intershop(dot)de> 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
>
> >> 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(at)postgresql(dot)org)
> >To make changes to your subscription:
> >http://www.postgresql.org/mailpref/pgsql-general
> >
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>