sequence scan, but indexed tables - Mailing list pgsql-general

From csernazs@freemail.hu (Cserna Zsolt)
Subject sequence scan, but indexed tables
Date
Msg-id e64bb1c1.0207190027.5c54f43e@posting.google.com
Whole thread Raw
Responses Re: sequence scan, but indexed tables
List 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

pgsql-general by date:

Previous
From: Wim
Date:
Subject: pg_dumpall failed!
Next
From: Janning Vygen
Date:
Subject: Re: select a ranking