Re: Why is a hash join being used?

On Wed, Jun 20, 2012 at 1:34 AM, Tim Jacobs <tjacobs2(at)email(dot)unc(dot)edu> wrote:
> The nested loop join performs very quickly, whereas the hash join is incredibly slow. If I disable the hash join temporarily then a nested loop join is used in the second case and is the query runs much more quickly. How can I change my configuration to favor the nested join in this case? Is this a bad idea?

First do ANALYZE the tables and try the tests again.

If it helped check your autovacuum configuration. Look at
http://www.postgresql.org/docs/9.1/static/routine-vacuuming.html#AUTOVACUUM
and the pg_stat_user_tables table (last_* and *_count fields).

If it still produces wrong plan then try to increase statistics
entries by ALTER TABLE SET STATISTICS (do not forget to ANALYZE after
doing it) or by the default_statistics_target configuration parameter.
Read more about it here
http://www.postgresql.org/docs/9.1/static/planner-stats.html.

> Alternatively, since I will be doing selections like this many times, what indexes can be put in place to expedite the query without mucking with the query optimizer? I've already created an index on the struct_id field of residue_atom_coords (each unique struct_id should only have a small number of rows for the residue_atom_coords table).

As I can see everything is okay with indexes.

>
> Thanks in advance,
> Tim
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

--
Sergey Konoplev

a database architect, software developer at PostgreSQL-Consulting.com
http://www.postgresql-consulting.com

Jabber: gray(dot)ru(at)gmail(dot)com Skype: gray-hemp Phone: +79160686204