Question about join

[Matthew Foster]

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

[Kevin Grittner]

Post by Matthew Foster
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

[Matthew Foster]

On Fri, Sep 14, 2012 at 12:12 PM, Kevin Grittner <

Post by Kevin Grittner

Post by Matthew Foster
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,
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

[Kevin Grittner]

Post by Matthew Foster
Should I post the additional info here or take it to the performance list?

The performance list would be better.

-Kevin