Subject RE: [firebird-support] Natural Plan when grouping by 5 indexed columns?
Author Leyne, Sean

> > The engine MUST READ read ALL records, since the index structures
> > contain data values which apply to all active transactions (Ann
> > forgive
> > me if I'm describing this incorrectly). As such, each record must
> > read to confirm whether the data values (visible for this
> > meet the query criteria.
> >
> > You have made an assumption regarding the format/content of the FB
> > indexes and how they are processed.
> Well, I think I get it now. Thanks for being patient.
> What would happen if I made the database read-only?

Nothing different, the engine does not distinguish between database
modes in processing queries.

Support for read-only databases was added late in the product life, so I
don't think anyone ever thought about doing something different for
queries that applied to read-only databases.

To be honest, when you are trying to add to support for SQL standard
functions for general database operations, working on special read-only
database logic is very low on the rank of priorities.

> so it looks like it does the same thing. Ann said something a while
> back about PG having a large RAM cache. I bumped the FB cache up to
> 20480 pages, which is almost large enough to hold the entire database
> (4K pages, 97Mb file) but maybe "almost" is not good enough?

It depends on what version of the engine you are running (I don't recall
you mentioning).

> > Firebird like all databases has some very good points, the
> > benchmarks I
> > have seen shows that it outperforms Postgres for some operations,
> > it's bad points, you have found 10 out of 1000 (1% 'failure' rate
> > pretty
> > good as I see it, it would be better if it were .01%).
> I'm not calling it a failure rate, I'm really just wondering whether
> I am doing something wrong/suboptimal (that is why my OP asked about
> compound indecies). I find these outliers by comparing the same test
> across various engines which unfortunately results in comparisons.

I was using the term failures in a broad context, perhaps I should have
used another term...

> > I don't think anyone took offense to your question, but perhaps to
> > your
> > pre-conceived notion of how FB works and the fact that you didn't
> > really
> > want to listen to the explanations we were providing.
> I'm sorry if you felt I wasn't listening, but honestly I didn't think
> I had asked the question very well.

No problem let's move on...