Understanding how Firebird fetches and orders records

[Michael Vilhelmsen]

Hi

This question is just to make me understand some of the "behind the scene" action, thats going on.

I have this table definition:

CREATE TABLE POINT_KVIT
(
  ID                 INTEGER         NOT NULL,
  BONNR              INTEGER         DEFAULT 0,
  TEKST              VARCHAR(   100) DEFAULT '' COLLATE NONE,
  DATO                  DATE         DEFAULT 'NOW',
 CONSTRAINT FK_POINT_KVIT PRIMARY KEY (ID)
);
CREATE ASC INDEX I_POINT_KVIT_DATO ON POINT_KVIT (DATO);
CREATE ASC INDEX POINT_KVIT_IDX1 ON POINT_KVIT (BONNR);


ID is update via trigger and a generator.
So far so good.

This table contains 216.000.000+ records.
The selectivity of the indices are:

RDB$PRIMARY240          : 0,00000000463
I_POINT_KVIT_DATO       : 0,00000005754
POINT_KVIT_IDX1         : 0,00000016429


If I do this simple SQL:

select * from Point_Kvit where BonNr=6899403

It completes in 0.1 seconds.

But if I add an order by ID like this:

select * from Point_Kvit where BonNr=6899403 order by ID

suddenly it takes 12+ seconds to complet
Whereas ordering like this:

select * from Point_Kvit where BonNr=6899403 order by bonnr, ID

is back to 0.1 seconds


Why is it, that ordering by primary key alone is so much more slower?
I have tried this in firebird 3 and 4. Same results. 

Regards Michael

[Dimitry Sibiryakov]

Michael Vilhelmsen wrote 09.06.2022 8:35:
> Why is it, that ordering by primary key alone is so much more slower?

EXPLAIN PLAN isql option can help you. And this article:
http://www.ibase.ru/dataaccesspaths/

--
WBR, SD.

[Alexey Kovyazin]

Hello,

If you would show us plans and statistics for SQLs above, we would see ... ORDER ... INDEX .. in the slow query, right?

In essence, the problem that with the wrong plan engine tries to order with index 216M of records first, and only then apply the filter for BonNr.

The typical workaround is to change column ID to expression ID+0, in order to supress the usage of index for ordering.

select * from Point_Kvit where BonNr=6899403 order by ID+0

Regards,

Alexey Kovyazin

IBSurgeon

четверг, 9 июня 2022 г. в 09:35:34 UTC+3, Michael Vilhelmsen:

[Michael Vilhelmsen]

Thank you

In essence, the problem that with the wrong plan engine tries to order with index 216M of records first, and only then apply the filter for BonNr.

The typical workaround is to change column ID to expression ID+0, in order to supress the usage of index for ordering.

Above explains why - I was just wondering why it was this way. 

mvh
Michael Kubel Vilhelmsen

--
You received this message because you are subscribed to a topic in the Google Groups "firebird-support" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/firebird-support/Qy7Js9f-BVY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/7289cd85-96b9-4cac-8bf2-3113cec07e95n%40googlegroups.com.

[Mark Rotteveel]

On 09-06-2022 08:35, Michael Vilhelmsen wrote:
> Why is it, that ordering by primary key alone is so much more slower?
> I have tried this in firebird 3 and 4. Same results.

It seems to me that the Firebird optimizer somehow thinks that sorting
using the primary key index before finding the row is somehow more
efficient, while in reality it is not.

I'm not sure if it is a bug as such, or something that could be
improved, but I would recommend reporting it on
https://github.com/FirebirdSQL/firebird/issues

Mark
--
Mark Rotteveel

[Dmitry Yemanov]

09.06.2022 14:18, Michael Vilhelmsen wrote:
>
> /In essence, the problem that with the wrong plan engine tries to order
> with index 216M of records first, and only then apply the filter for BonNr./

Not exactly. At the first stage, engine scans the index POINT_KVIT_IDX1
for matches (BonNr=6899403) and remembers the resulting rows (their
DBKEYs). Then the engine scans the primary key index until it gets the
key with DBKEY equal to one of the priorly remembered ones. In your
case, it appears that ID of record(s) with BonNr=6899403 are pretty far
from the beginning (in the ID order). Hence the slowdown.

> Above explains why - I was just wondering why it was this way.

There's no other way to use an index for ordering in your query. And
Firebird always tries to use an index-based ordering (if possible)
despite the external sort (SORT plan) could in fact be better.


Dmitry

[Dmitry Yemanov]

09.06.2022 14:23, Mark Rotteveel wrote:

> It seems to me that the Firebird optimizer somehow thinks that sorting
> using the primary key index before finding the row is somehow more
> efficient, while in reality it is not.

There's no cost-base choice between ORDER and SORT plans yet (sigh), and
heuristics is usually in favour of the ORDER plan. As simple as that.


Dmitry

[Michael Vilhelmsen]

Thank you for a deeper explanation

mvh
Michael Kubel Vilhelmsen

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/fcfe2160-ac4d-fa44-867b-aa51152a4eba%40yandex.ru.