Re: Simple join doesn't use index

On Thu, Jan 3, 2013 at 4:54 PM, Alex Vinnik <alvinnik(dot)g(at)gmail(dot)com> wrote:
> Don't understand why PG doesn't use views_visit_id_index in that query but
> rather scans whole table. One explanation I have found that when resulting
> dataset constitutes ~15% of total number of rows in the table then seq scan
> is used. In this case resulting dataset is just 1.5% of total number of
> rows. So it must be something different. Any reason why it happens and how
> to fix it?
>
> Postgres 9.2
> Ubuntu 12.04.1 LTS
> shared_buffers = 4GB the rest of the settings are default ones
<snip>

It happens because you lied to the database...heh. In particular, the
'effective_cache_size' setting which defaults to 128mb. That probably
needs to be much, much larger. Basically postgres is figuring the
cache is much smaller than the data and starts to favor sequential
plans once you hit a certain threshold. If you had a server with only
say 256mb ram, it probably *would* be faster.

SQL server probably uses all kinds of crazy native unportable kernel
calls to avoid having to make a similar .conf setting. Or maybe it
just assumes infinite cache size...dunno.

merlin