THE DDL of the 3 sample table :

CREATE TABLE DESC_VARCHAR (
IDOBJ VARCHAR(20),
COMMENT VARCHAR(10000)
);
CREATE INDEX DESC_VARCHAR_IDX ON DESC_VARCHAR(IDOBJ);

********

CREATE TABLE DESC_BLOB (
IDOBJ VARCHAR(20)
COMMENT BLOB
);
CREATE INDEX DESC_BLOB_IDX ON DESC_BLOB(IDOBJ);

********

CREATE TABLE DESC_EMPTY (
IDOBJ VARCHAR(20)
);
CREATE INDEX DESC_EMPTY_IDX ON DESC_EMPTY(IDOBJ);


tables are newly created and filled with the exact same reccords (40 millions rows)

the same query on the 3 tables :

select IDObj From desc_varchar where IDObj='NOT_EXIST';
PLAN (DESC_VARCHAR INDEX (DESC_VARCHAR_IDX))
=> around 430 ms to return

select IDObj From desc_blob where IDObj='NOT_EXIST'
PLAN (DESC_BLOB INDEX (DESC_BLOB_IDX))
=> around 350 ms to return

select IDObj From desc_xxxx where IDObj='NOT_EXIST'
PLAN (DESC_EMPTY INDEX (DESC_EMPTY_IDX))
=> around 1.5 ms to return


if you understand something ..... moving the page size from 8 to 16 reduce by 10 the speed of the 2 first query, but still 10x more slower than the last variante

--- In firebird-support@yahoogroups.com, Mark Rotteveel <mark@...> wrote:
>
> On Wed, 07 Mar 2012 09:23:11 -0000, "nathanelrick" <nathanelrick@...>
> wrote:
> > Thanks Svein,
> >
> >> Is your query,
> >>
> >> select IDObj From DESCRIPTION where ID='ID_HAS_NEVER_EXISTED', or
> >> select IDObj From DESCRIPTION where
> >> ID='ID_THAT_IS_RECENTLY_DELETED_UPDATED_OR_ADDED'?
> >
> > First case ID='ID_HAS_NEVER_EXISTED', so unfortunatly it's not this ..
> > also i do the test on a fresh backup/restored database, even a newly
> > created and filled table to be sure about versioning ... not look like
> this
>
> What is the plan for the query?
>