Re: Question about join

Matthew Foster <matthew.foster@noaa.gov> wrote:

> I have two tables that represent a one-to-many relationship in our
> database.  One table is relatively small (~2000 rows) and the
> other is relatively large (~65M rows).  When I try to join these
> tables, Postgres does a sequential scan an the large table.  This,
> combined with the append and hash join is taking about 3 minutes
> to complete.
>
> I'm guessing that I am doing something wrong in my query to make
> it take so long.  Is there a good reference somewhere on how to do
> joins intelligently?

Are your returning the whole 65,000,000 set of rows?  If so, three
minutes isn't out of line, and a table scan probably is much faster
than the alternatives.

If that is not the exact issue, you might want to post the two table
layouts, including indexes (the output from psql \d should do) the
actual query with its search conditions, the EXPLAIN ANALYZE output,
and other details as suggested on this page:

http://wiki.postgresql.org/wiki/SlowQueryQuestions

There is a good chance that you need to tune some configuration
settings, but there could be any number of other issues that people
can only guess at without more detail.

-Kevin