From: Stefan Andreatta <s.andreatta@synedra.com>
To: pgsql-performance@postgresql.org
Subject: Re: Simple join doesn't use index
Date: Fri, 04 Jan 2013 05:33:09 +0100
Message-ID: <50E65B85.9000001@synedra.com> (raw)
In-Reply-To: <CALd8TVECZDosPDZ7Cwv1gVRy1p2F4Hg+sw+240MsN36KUkt31g@mail.gmail.com>
References: <CALd8TVECZDosPDZ7Cwv1gVRy1p2F4Hg+sw+240MsN36KUkt31g@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: s.andreatta@synedra.com
Subject: Re: Simple join doesn't use index
In-Reply-To: <50E65B85.9000001@synedra.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox