sequence scan, but indexed tables

From: csernazs(at)freemail(dot)hu (Cserna Zsolt)
To: pgsql-general(at)postgresql(dot)org
Subject: sequence scan, but indexed tables
Date: 2002-07-19 08:27:33
Message-ID: e64bb1c1.0207190027.5c54f43e@posting.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi!

I have two tables (picmain, and picalbum). Both table has an unique
index filed, which is indexed (this field called "aid" and the indexes
are: picmain_aid_idx, picalbum_aid_idx). But when I execute the query
with simple join, I get this:

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).

I don't know why postgres use sequence scan, because I have two good
indexes. :)
vacuum, vacuum analyze is done.

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

Thanks.

Zsolt

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jim Morcombe 2002-07-19 08:50:03 Re: Can't link to libpq
Previous Message Stephane Bortzmeyer 2002-07-19 08:21:10 Re: Logging SQL queries?