Re: Simple join doesn't use index

On 01/03/2013 11:54 PM, Alex Vinnik 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?

But does the query planner know the same? If you added the EXPLAIN
ANALYZE output of the query and something like:

  SELECT tablename AS table_name, attname AS column_name,
         null_frac, avg_width, n_distinct, correlation
      FROM pg_stats
      WHERE tablename in ('views', 'visits');

.. one could possibly tell a bit more.

> Postgres 9.2
> Ubuntu 12.04.1 LTS
> shared_buffers = 4GB the rest of the settings are default ones

There are more than just this one memory related value, that need to be
changed for optimal performance. E.g. effective_cache_size can have a
direct effect on use of nested loops. See:

http://www.postgresql.org/docs/9.2/static/runtime-config-query.html

Regards,
Stefan