Re: Question about join - Mailing list pgsql-novice

From Kevin Grittner
Subject Re: Question about join
Date
Msg-id 50531F3A020000250004A3BE@gw.wicourts.gov
Whole thread Raw
In response to Question about join  (Matthew Foster <matthew.foster@noaa.gov>)
Responses Re: Question about join  (Matthew Foster <matthew.foster@noaa.gov>)
List pgsql-novice
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


pgsql-novice by date:

Previous
From: Matthew Foster
Date:
Subject: Question about join
Next
From: Matthew Foster
Date:
Subject: Re: Question about join