Thread: Question about join
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?
Matt
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
On Fri, Sep 14, 2012 at 12:12 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
Are your returning the whole 65,000,000 set of rows? If so, threeMatthew 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
Matthew Foster <matthew.foster@noaa.gov> wrote: > Should I post the additional info here or take it to the performance list? The performance list would be better. -Kevin