Thread: sequence scan, but indexed tables

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

csernazs@freemail.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

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

Yes, the cost is cool, if I turn off enable_hashjoin, I get
a very hight cost. But the runtime is smaller (in a bigger
query, which have around 20 row on output, and 2-3 subselect
and 3-4 join).

> > I have postgresql 7.0.3 running on Debian GNU/Linux i386.
>
> You do realize that version is quite ancient?
So, upgrade is strongly recommended? :) Debian package is
not so good, the backup script doesn't work (in an earlier
debian package). I tried to backup the database (I have
daily backup) before upgrading, but restoring from this was
too hard for me, because the postgres server didn't started
(he/she thinks I haven't got backup :).

Regards, and thank you,

Zsolt

On Mon, 2002-07-22 at 14:33, Cserna Zsolt wrote:
> So, upgrade is strongly recommended? :) Debian package is
> not so good, the backup script doesn't work (in an earlier
> debian package). I tried to backup the database (I have
> daily backup) before upgrading, but restoring from this was
> too hard for me, because the postgres server didn't started
> (he/she thinks I haven't got backup :).

If you have problems with the packaging, please tell me about them.  I
may well be able to help with the immediate problem; I may also be able
to improve the package for the benefit of other users.  But I can't do
anything about it if I never hear about it.

Use the Debian bug reporting system (install the package reportbug).

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Then Peter and the other apostles answered and said,
      We ought to obey God rather than men."
                                        Acts 5:29