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
Import Notes
Reply to msg id not found: Pine.LNX.4.21.0207221328120.25417-100000@fmt1.fmt.vein.hu | Resolved by subject fallback
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