On Fri, Sep 14, 2012 at 12:12 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
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?
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
Kevin,
No, in the end 75 rows are returned, but when it does the join all 65M rows are pulled.
Should I post the additional info here or take it to the performance list?
Thanks.
Matt