From: | Stefan Andreatta <s(dot)andreatta(at)synedra(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Simple join doesn't use index |
Date: | 2013-01-04 04:33:09 |
Message-ID: | 50E65B85.9000001@synedra.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-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
From | Date | Subject | |
---|---|---|---|
Next Message | Stefan Andreatta | 2013-01-04 05:14:34 | Re: serious under-estimation of n_distinct for clustered distributions |
Previous Message | nobody nowhere | 2013-01-03 23:45:08 | SMP on a heavy loaded database |