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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
