[FB 4.0.4 + 5.0] Big table, still lots of reads while using compound index

[Pieter Bas Hofstede]

I've got a big table (about 120 million rows) and want to get some min and max values.

CREATE TABLE TEST (
    ID        UUID /* UUID = CHAR(16) */,
    RELATIONID        UUID /* UUID = CHAR(16) */,
    PARENTID          UUID /* UUID = CHAR(16) */,
    DATUM             D_DATE NOT NULL /* D_DATE = DATE */,
    SOORTMETING       D_SMALLINT NOT NULL /* D_SMALLINT = SMALLINT */)

Indices:

ALTER TABLE TEST ADD CONSTRAINT UNQ1_TEST UNIQUE (DIERID, DATUM, SOORTMETING)
USING DESCENDING INDEX UNQ1_TEST;

ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (ID);
CREATE DESCENDING INDEX TEST_IDX1 ON TEST (RELATIONID, DATUM, SOORTMETING);
CREATE DESCENDING INDEX TEST_IDX2 ON TEST (PARENTID, SOORTMETING, DATUM);
CREATE UNIQUE INDEX TEST_IDX3 ON TEST (DIERID, DATUM, SOORTMETING);
CREATE INDEX TEST_IDX4 ON TEST (RELATIONID, DATUM, SOORTMETING);

SQL 1: fast and correct:

select max(dm.DATUM)
from TEST dm
where dm.RELATIONID = char_to_uuid('9c295585-a307-49be-9014-43545ef79c2a')
and dm.SOORTMETING = 2

Uses PLAN (DM ORDER TEST_IDX1) good speed and has a few reads.

SQL 2: change max to min:

select min(dm.DATUM)
from TEST dm
where dm.RELATIONID = char_to_uuid('9c295585-a307-49be-9014-43545ef79c2a')
and dm.SOORTMETING = 2

Uses correctly plan PLAN (DM ORDER TEST_IDX4). But needs 540.489 fetches

SQL 3: (copy of SQL 1, but with SOORTMETING=3)

select max(dm.DATUM)
from TEST dm
where dm.RELATIONID = char_to_uuid('9c295585-a307-49be-9014-43545ef79c2a')
and dm.SOORTMETING = 3

Uses correctly plan PLAN (DM ORDER TEST_IDX1), but has 2.391.684 fetches

Regardless of empty resultset.

Above occurs in both FB4.0.4.3010 and FB5.0.0.1306.

Question is, it this expected behaviour from your point of view? I can send database. Compressed fbk is 4.3GB.

Please note:

When under FB 5 I rewrite indexes to several conditional/partial index (on soortmeting=2 and 3), all is good! Good speed and expected number of fetches.

[Dimitry Sibiryakov]

Pieter Bas Hofstede wrote 17.06.2024 10:12:
> *Question *is, it this expected behaviour from your point of view?

Yes. Your queries has no condition for DATUM so only first segment on indexes
is used, the rest is useless.

I suggest to read http://www.ibase.ru/dataaccesspaths/

--
WBR, SD.

[Dimitry Sibiryakov]

'Dimitry Sibiryakov' via firebird-support wrote 17.06.2024 11:05:
>   Yes. Your queries has no condition for DATUM so only first segment on indexes
> is used

Sorry, actually two segments are used. You can see it in explained plan. To
get better your indexes must be built on (RELATIONID, SOORTMETING, DATUM). Order
of fields does matter in this case.

--
WBR, SD.

[Pieter Bas Hofstede]

Thank you for your quick response.

I dont get it completely I think.

I already have an index "CREATE INDEX TEST_IDX4 ON TEST (RELATIONID, DATUM, SOORTMETING);" like you suggest.

Explaining plan says: Index "TEST_IDX4" Range Scan (partial match: 1/3)

1) This range scan with only match for field 1 also applies to the fast SQL1. I still dont get it why SQL 1 is fast (I think the first match in the range scan is the correct record?). But why doens't that work for the ascending index in the min-function for SQL2?

2) In FB5 with the following partial index "CREATE INDEX TEST_IDX_SM2ASC ON TEST (RELATIONID, DATUM, SOORTMETING) where SOORTMETING = 2" the range scan is still 1/3 but needs to fetch only 1 record. Why does this work fast compared to the same SQL but without the partial index (but including above TEST_IDX4) ?

Op maandag 17 juni 2024 om 11:11:27 UTC+2 schreef sd:

[Dimitry Sibiryakov]

Pieter Bas Hofstede wrote 17.06.2024 12:05:
> I already have an index "CREATE INDEX TEST_IDX4 ON TEST (RELATIONID, DATUM,
> SOORTMETING);" like you suggest.
> Explaining plan says: Index "TEST_IDX4" Range Scan (partial match: 1/3)

Read letter by letter. I suggested (RELATIONID, SOORTMETING, DATUM), not
(RELATIONID, DATUM, SOORTMETING). Order DOES matter.

--
WBR, SD.

[Pieter Bas Hofstede]

Yes! Thank you Dimitry!

So putting the date-column as last column in the compound index solves this one because index has now a partial match of 2/3 (RELATIONID + SOORTMETING) and can use index sorting to quickly determine the result within the range.

Is this correct understood?

Op maandag 17 juni 2024 om 12:07:06 UTC+2 schreef sd:

[Dimitry Sibiryakov]

Pieter Bas Hofstede wrote 17.06.2024 12:38:
> So putting the date-column as last column in the compound index solves this one
> because index has now a partial match of 2/3 (RELATIONID + SOORTMETING) and can
> use index sorting to quickly determine the result within the range.
> Is this correct understood?

Not quite: the plan is "ORDER" in every case, but using 2 of 3 index segments
Firebird reduces number of records it skips before reaching the needed one.
Once again I suggest you to carefully study
http://www.ibase.ru/dataaccesspaths/ - the best description of this machinery ever.

--
WBR, SD.