Re: Re: Postgres slowdown on large table joins

Dave Edmondson writes:

> Added two indexes, here's the query plan... it doesn't seem to be using the
> indexes at all. I'm sure I'm doing something wrong here...
>
> NOTICE: QUERY PLAN:
>
> Sort (cost=6707.62..6707.62 rows=10596 width=170)
> -> Merge Join (cost=1.34..5492.29 rows=10596 width=170)
> -> Nested Loop (cost=0.00..4943.38 rows=36493 width=154)
> -> Index Scan using config_pkey on config c (cost=0.00..2.01 rows=1 width=113)
> -> Seq Scan on data d (cost=0.00..3116.72 rows=145972 width=41)
> -> Sort (cost=1.34..1.34 rows=12 width=16)
> -> Seq Scan on prefs p (cost=0.00..1.12 rows=12 width=16)

I don't see anything blatantly wrong with this. The Nested Loop can
obviously(?) not use two index scans, and the Seq Scan under the Sort is
okay since it only retrieves 12 rows. (Unless those 12 rows are a wild
misguess.)

> I actually didn't know anything about indexes before now. Know of anywhere
> with a good explanation of them? The Postgres user manual wasn't too
> helpful, it just explained the syntax of the CREATE USER command.

There's this:
http://www.postgresql.org/users-lounge/docs/7.0/postgres/indices.htm

but it could probably need to some work.

--
Peter Eisentraut peter_e(at)gmx(dot)net http://yi.org/peter-e/