On Thu, Jan 3, 2013 at 4:54 PM, Alex Vinnik <alvinni...@gmail.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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
