Message-ID: <50E65B85.9000001@synedra.com>
Date: Fri, 04 Jan 2013 05:33:09 +0100
From: Stefan Andreatta <s.andreatta@synedra.com>
Organization: synedra IT
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:17.0) Gecko/17.0 Thunderbird/17.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Simple join doesn't use index
References: 
 <CALd8TVECZDosPDZ7Cwv1gVRy1p2F4Hg+sw+240MsN36KUkt31g@mail.gmail.com>
In-Reply-To: 
 <CALd8TVECZDosPDZ7Cwv1gVRy1p2F4Hg+sw+240MsN36KUkt31g@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance