> > Firstly, David was not joking when he implied that select count(*)
> > based queries is a possible weakness in any MGA database.
>
> Thank you and David for enlightening me.
>
> It is now clear to me why all SELECT COUNT(*) will be expensive,
> even the ones that I thought would do an index scan.
>
> I am afraid that I didn't make it clear enough that with a
> 'simple select' I meant a statement like
> SELECT NAME FROM PARENT WHERE ID = 42
> These queries also perform badly, when running them together
> with the COUNT(*) queries 4 times as badly as on other DBMS.
>
> However, when I run only these 'simple selects', the performance
> is a mere 50% worse than that of the worst competitor.
> I guess that the extra load the COUNT(*)-statements imply will
> explain the factor 4, and the 50% may be accounted for by the
> record versioning overhead, right?
>
> > Firebird will not even read the index
> > if there is not a where clause to knock out some records, because
> > the index read would just be just another I/O read for no gain.
> >
> > So you don't get the impression it is a weird Firebird thing, it
> > is a problem for Postgres as well.
> >
> > (http://archives.postgresql.org/pgsql-hackers/2005-01/msg00247.php)
>
> This is interesting.
>
> Firstly, in what I call the 'index scan' queries, I DO have a where
> clause involving an indexed column, and at least sometimes the
> optimizer should figure that using the index could save time.
>
> Secondly, one of the other databases I tested IS Postgres, and
> a) all selects, even the COUNT(*) ones, perform reasonably well, and
> b) the results of the 'index scan' queries was much better than for
> the 'full table scan' queries.
>
> This should not be if Postgres worked like Forebird, right?

Mind you, PostgreSQLs implementation of MGA might be different.
More "in memory" perhaps - that would make it faster. I don't know the
exact details, it might be a larger memory buffer, it might be something
else, who can tell?

One thing is for sure -- Firebird (v2) will perform better in normal
usage compared to so called benchmark applications. Why? Because
there's lies, more lies and benchmarks.



With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com