Re: Question about join

On Fri, Sep 14, 2012 at 12:12 PM, Kevin Grittner <
Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:

> Matthew Foster <matthew(dot)foster(at)noaa(dot)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
>

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