slow reading of index for select count(*) ?

[Raimund Jacob]

hello list,

when our applications start, we usually check for the existence of some
tables. we usually do something like 'select count(*) from table where
1<>1' - this is easy and works with all DBs.

however, when doing this for a largish table (900 000 rows) this takes a
rather long time: the EXPLAIN statement shows that only an index scan is
performed on an unique index on two columns (which are not the PK).

while the select is running, everything is waiting on I/O. but disk
activity shows only 500kb/s on a disk that is able to do >25MB/s. this
happens when the DB has just been restarted and nothing is cached, yet.
subsequent selects are fast.

it seems that reading the index from disk is very slow because of
suboptimal access patterns. this is 7.5.0.26 on Linux, all Volumes on
ReiserFS.

is this known? can there be done anything about it? what would be a
cheap way to check for table-existence (portable, if possible)?

thanks for any hint,
Raimund
--
Die Lösung zum effizienten eCRM und eMail-Marketing, mehr unter
Pinuts Universal Messenger http://www.pinuts.de/universalmessenger/

Pinuts media+science GmbH http://www.pinuts.de
Dipl.-Inform. Raimund Jacob ***@pinuts.de
Krausenstr. 9-10 voice : [phone]
10117 Berlin fax : [phone]
Germany

[Becker, Holger]

Post by Raimund Jacob
hello list,
when our applications start, we usually check for the
existence of some
tables. we usually do something like 'select count(*) from table where
1<>1' - this is easy and works with all DBs.
however, when doing this for a largish table (900 000 rows)
this takes a
rather long time: the EXPLAIN statement shows that only an
index scan is
performed on an unique index on two columns (which are not the PK).
while the select is running, everything is waiting on I/O. but disk
activity shows only 500kb/s on a disk that is able to do >25MB/s. this
happens when the DB has just been restarted and nothing is
cached, yet.
subsequent selects are fast.
it seems that reading the index from disk is very slow because of
suboptimal access patterns. this is 7.5.0.26 on Linux, all Volumes on
ReiserFS.
is this known? can there be done anything about it? what would be a
cheap way to check for table-existence (portable, if possible)?
thanks for any hint,
Raimund

Hi,

this behaviour is comprehensible for MaxDB because the optimizer doesn't

detect that the predicate always ecaluate to false.
So the whole table or like in your case the index have to be scaned and
for every row the predicate have to be checked.
We are already working on this kind of optimization and it will be part
of one of the next 7.6 release.
Until then you could use the rowno predicate to check for table
existence
but I think it's not portable.

Example:
select 1 from table where rowno <= 1

Kind regards
Holger