Re: sequence scan, but indexed tables

csernazs(at)freemail(dot)hu (Cserna Zsolt) writes:
> explain SELECT picmain.aid from picmain, picalbum where
> picmain.aid=picalbum.aid;
> NOTICE: QUERY PLAN:

> Hash Join (cost=806.71..69631.60 rows=832629 width=8)
> -> Seq Scan on picalbum (cost=0.00..14323.29 rows=832629 width=4)
> -> Hash (cost=730.57..730.57 rows=30457 width=4)
> -> Seq Scan on picmain (cost=0.00..730.57 rows=30457
> width=4)

> Both table has "relative" many rows (picmain: around 30000, picalbum:
> around 800000).

Then I'd say the planner is making the right decision.

If you want to experiment, see what plans (and actual runtimes) you get
after turning off enable_hashjoin and/or enable_mergejoin. With both
off you will get a nestloop with inner indexscan ... and I bet you won't
like it.

> I have postgresql 7.0.3 running on Debian GNU/Linux i386.

You do realize that version is quite ancient?

regards, tom lane