SV: [firebird-support] index use when using order by
Hi. I couldn't agree more, and for two reasons: 1. Is it obvious to all that this is required to avoid a severe performance penalty? It wasn't to me, thats for sure. 2. Adding indices to tables (especially those heavy on writing records), is also a performance concern. So it's a bit like choosing between two evils. Best regards Mikkel Andersen Fra: firebird-support@yahoogroups.com Sendt: 18. september 2019 08:04 Til: firebird-support@yahoogroups.com Emne: Re: [firebird-support] index use when using order by On 2019-09-18 06:46, liviuslivius liviusliv...@poczta.onet.pl [firebird-support] wrote: > Hi > > your problem is that you have ascending index, which is not usable in > descending queries. You need to create descending index and it then > can be used in e.g. max queries. Ascending index is used in min > queries and order by xx asc. This is in my opinion an annoying limitation of Firebird. The indexes are theoretically bidirectional, but aren't used that way because of how index pages are written. The write strategy makes it possible for a reverse read to miss index pages when the index is modified (page split) while it is read. I think there must be a strategy that can be used to address that. In other database an 'ascending' index can also be used for descending reads, there ascending and descending are applied to individual columns to create different key orderings in the index. Mark
RE: [firebird-support] index use when using order by
Thanks everyone, I have added a descending index to the tables required, it makes a big difference. Andrew Zenz From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Wednesday, 18 September 2019 4:04 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] index use when using order by On 2019-09-18 06:46, liviuslivius liviusliv...@poczta.onet.pl [firebird-support] wrote: > Hi > > your problem is that you have ascending index, which is not usable in > descending queries. You need to create descending index and it then > can be used in e.g. max queries. Ascending index is used in min > queries and order by xx asc. This is in my opinion an annoying limitation of Firebird. The indexes are theoretically bidirectional, but aren't used that way because of how index pages are written. The write strategy makes it possible for a reverse read to miss index pages when the index is modified (page split) while it is read. I think there must be a strategy that can be used to address that. In other database an 'ascending' index can also be used for descending reads, there ascending and descending are applied to individual columns to create different key orderings in the index. Mark
Re: [firebird-support] index use when using order by
On 2019-09-18 06:46, liviuslivius liviusliv...@poczta.onet.pl [firebird-support] wrote: > Hi > > your problem is that you have ascending index, which is not usable in > descending queries. You need to create descending index and it then > can be used in e.g. max queries. Ascending index is used in min > queries and order by xx asc. This is in my opinion an annoying limitation of Firebird. The indexes are theoretically bidirectional, but aren't used that way because of how index pages are written. The write strategy makes it possible for a reverse read to miss index pages when the index is modified (page split) while it is read. I think there must be a strategy that can be used to address that. In other database an 'ascending' index can also be used for descending reads, there ascending and descending are applied to individual columns to create different key orderings in the index. Mark
Re: [firebird-support] index use when using order by
On 2019-09-18 02:11, 'Andrew Zenz' and...@aimsoftware.com.au [firebird-support] wrote: > Firebird 2.5.7 (WI-2.5.7.27050) > > Just realised I 'should' be using 2.5.8, but on a server running > WI-V2.5.8.27089 it still uses NATURAL. Be aware, the latest version of Firebird 2.5 is 2.5.9, not 2.5.8. Mark
RE: [firebird-support] index use when using order by
Thanks. Excuse me while I find a brick wall to bash my head against. Andrew Zenz From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Wednesday, 18 September 2019 2:47 PM To: firebird-support@yahoogroups.com Subject: RE: [firebird-support] index use when using order by Hi your problem is that you have ascending index, which is not usable in descending queries. You need to create descending index and it then can be used in e.g. max queries. Ascending index is used in min queries and order by xx asc. Regards, Karol Bieniaszewski
RE: [firebird-support] index use when using order by
Hiyour problem is that you have ascending index, which is not usable in descending queries. You need to create descending index and it then can be used in e.g. max queries. Ascending index is used in min queries and order by xx asc.Regards,Karol Bieniaszewski null
RE: [firebird-support] index use when using order by
and further, when I run the query in Flamerobin as it was in the
trace substituting 9 for the ?:
SELECT
"ID","USERNAME","USERNO","LOGDATE","LOGTIME","LOGFILE","RECORDKEY","RECO
RDACTION","RECORDBEFORE","RECORDAFTER" FROM "AIMLOG" WHERE ("ID" <=
99 ) ORDER BY "ID" DESC
The following plan is used, still taking almost 15 seconds.
Starting transaction...
Preparing query: SELECT
"ID","USERNAME","USERNO","LOGDATE","LOGTIME","LOGFILE","RECORDKEY","RECO
RDACTION","RECORDBEFORE","RECORDAFTER" FROM "AIMLOG" WHERE ("ID" <=
99 ) ORDER BY "ID" DESC
Prepare time: 0.031s
Field #01: AIMLOG.ID Alias:ID Type:INTEGER
Field #02: AIMLOG.USERNAME Alias:USERNAME Type:STRING(12)
Field #03: AIMLOG.USERNO Alias:USERNO Type:INTEGER
Field #04: AIMLOG.LOGDATE Alias:LOGDATE Type:INTEGER
Field #05: AIMLOG.LOGTIME Alias:LOGTIME Type:INTEGER
Field #06: AIMLOG.LOGFILE Alias:LOGFILE Type:STRING(30)
Field #07: AIMLOG.RECORDKEY Alias:RECORDKEY Type:STRING(30)
Field #08: AIMLOG.RECORDACTION Alias:RECORDACTION Type:STRING(10)
Field #09: AIMLOG.RECORDBEFORE Alias:RECORDBEFORE Type:STRING(1000)
Field #10: AIMLOG.RECORDAFTER Alias:RECORDAFTER Type:STRING(1000)
PLAN SORT ((AIMLOG INDEX (AIMLOG_PK)))
Executing...
Done.
260094 fetches, 0 marks, 3914 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 129996 index, 0 seq.
Delta memory: 9549504 bytes.
Total execution time: 14.391s
Script execution finished.
I am still confused/bemused about the delay. Are my expectations, that
such a query should be almost instant, unreasonable? Especially when a
query such as this is almost instant:
SELECT a.ID, a.USERNAME, a.USERNO, a.LOGDATE, a.LOGTIME, a."LOGFILE",
a.RECORDKEY, a.RECORDACTION, a.RECORDBEFORE, a.RECORDAFTER
FROM AIMLOG a
where a.id = 123456
Starting transaction...
Preparing query: SELECT a.ID, a.USERNAME, a.USERNO, a.LOGDATE,
a.LOGTIME, a."LOGFILE", a.RECORDKEY, a.RECORDACTION, a.RECORDBEFORE,
a.RECORDAFTER
FROM AIMLOG a
where a.id = 123456
Prepare time: 0.016s
Field #01: AIMLOG.ID Alias:ID Type:INTEGER
Field #02: AIMLOG.USERNAME Alias:USERNAME Type:STRING(12)
Field #03: AIMLOG.USERNO Alias:USERNO Type:INTEGER
Field #04: AIMLOG.LOGDATE Alias:LOGDATE Type:INTEGER
Field #05: AIMLOG.LOGTIME Alias:LOGTIME Type:INTEGER
Field #06: AIMLOG.LOGFILE Alias:LOGFILE Type:STRING(30)
Field #07: AIMLOG.RECORDKEY Alias:RECORDKEY Type:STRING(30)
Field #08: AIMLOG.RECORDACTION Alias:RECORDACTION Type:STRING(10)
Field #09: AIMLOG.RECORDBEFORE Alias:RECORDBEFORE Type:STRING(1000)
Field #10: AIMLOG.RECORDAFTER Alias:RECORDAFTER Type:STRING(1000)
PLAN (A INDEX (AIMLOG_PK))
Executing...
Done.
18 fetches, 0 marks, 2 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 4 index, 0 seq.
Delta memory: 20548 bytes.
Total execution time: 0.031s
Script execution finished.
Cheers,
Andrew Zenz
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]
Sent: Wednesday, 18 September 2019 11:02 AM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] index use when using order by
I performed a trace and this is the result. I suspected it was doing an
order by:
Preparing Statement 5694738 : SELECT
"ID","USERNAME","USERNO","LOGDATE","LOGTIME","LOGTABLE","RECORDKEY","REC
ORDACTION","RECORDBEFORE","RECORDAFTER" FROM "AIMLOG" WHERE ("ID" <= ? )
ORDER BY "ID" DESC Time Taken:0.00 secs
Setting number of rows to fetch to 1 for Statement 5694738 Time
Taken:0.00 secs
Binding Column 1 to C type SLONG for Statement 5694738 Time Taken:0.00
secs
Binding Column 2 to C type CHAR for Statement 5694738 Time Taken:0.00
secs
Binding Column 3 to C type SSHORT for Statement 5694738 Time Taken:0.00
secs
Binding Column 4 to C type SLONG for Statement 5694738 Time Taken:0.00
secs
Binding Column 5 to C type SLONG for Statement 5694738 Time Taken:0.00
secs
Binding Column 6 to C type CHAR for Statement 5694738 Time Taken:0.00
secs
Binding Column 7 to C type CHAR for Statement 5694738 Time Taken:0.00
secs
Binding Column 8 to C type CHAR for Statement 5694738 Time Taken:0.00
secs
Binding Column 9 to C type CHAR for Statement 5694738 Time Taken:0.00
secs
Binding Column 10 to C type CHAR for Statement 5694738 Time Taken:0.00
secs
Binding ? 1 with C type SLONG for Statement 5694738 Time Taken:0.00 secs
Executing prepared Statement 5694738 Time Taken:15.73 secs
Fetching Row from Statement 5694738 Return Code: 0 Time Taken:0.00 secs
HTH
Andrew Zenz
RE: [firebird-support] index use when using order by
I performed a trace and this is the result. I suspected it was doing an
order by:
Preparing Statement 5694738 : SELECT
"ID","USERNAME","USERNO","LOGDATE","LOGTIME","LOGTABLE","RECORDKEY","REC
ORDACTION","RECORDBEFORE","RECORDAFTER" FROM "AIMLOG" WHERE ("ID" <= ? )
ORDER BY "ID" DESC Time Taken:0.00 secs
Setting number of rows to fetch to 1 for Statement 5694738 Time
Taken:0.00 secs
Binding Column 1 to C type SLONG for Statement 5694738 Time Taken:0.00
secs
Binding Column 2 to C type CHAR for Statement 5694738 Time Taken:0.00
secs
Binding Column 3 to C type SSHORT for Statement 5694738 Time Taken:0.00
secs
Binding Column 4 to C type SLONG for Statement 5694738 Time Taken:0.00
secs
Binding Column 5 to C type SLONG for Statement 5694738 Time Taken:0.00
secs
Binding Column 6 to C type CHAR for Statement 5694738 Time Taken:0.00
secs
Binding Column 7 to C type CHAR for Statement 5694738 Time Taken:0.00
secs
Binding Column 8 to C type CHAR for Statement 5694738 Time Taken:0.00
secs
Binding Column 9 to C type CHAR for Statement 5694738 Time Taken:0.00
secs
Binding Column 10 to C type CHAR for Statement 5694738 Time Taken:0.00
secs
Binding ? 1 with C type SLONG for Statement 5694738 Time Taken:0.00 secs
Executing prepared Statement 5694738 Time Taken:15.73 secs
Fetching Row from Statement 5694738 Return Code: 0 Time Taken:0.00 secs
HTH
Andrew Zenz
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]
Sent: Wednesday, 18 September 2019 10:11 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] index use when using order by
Hi everyone.
I am experiencing an unusual situation.
I have a log table for tracking user changes to records. Table is as
follows:
CREATE TABLE AIMLOG
(
ID Integer NOT NULL,
USERNAME Char(12) COLLATE EN_UK,
USERNO Integer,
LOGDATE Integer,
LOGTIME Integer,
LOGTABLE Char(30) COLLATE EN_UK,
RECORDKEY Char(30) COLLATE EN_UK,
RECORDACTION Char(10) COLLATE EN_UK,
RECORDBEFORE Varchar(1000) COLLATE EN_UK,
RECORDAFTER Varchar(1000) COLLATE EN_UK,
CONSTRAINT AIMLOG_PK PRIMARY KEY (ID)
);
Using Flamerobin, if I want to find the last record I run the following
query:
SELECT a.ID, a.USERNAME, a.USERNO, a.LOGDATE, a.LOGTIME, a.LOGTABLE,
a.RECORDKEY, a.RECORDACTION, a.RECORDBEFORE, a.RECORDAFTER
FROM AIMLOG a
order by a.id descending
The query takes about 15 seconds because it doesn't want to use the
primary key, AIMLOG_PK. Plan below:
Starting transaction...
Preparing query: SELECT a.ID, a.USERNAME, a.USERNO, a.LOGDATE,
a.LOGTIME, a.LOGTABLE, a.RECORDKEY, a.RECORDACTION, a.RECORDBEFORE,
a.RECORDAFTER
FROM AIMLOG a
order by a.id descending
Prepare time: 0.015s
Field #01: AIMLOG.ID Alias:ID Type:INTEGER
Field #02: AIMLOG.USERNAME Alias:USERNAME Type:STRING(12)
Field #03: AIMLOG.USERNO Alias:USERNO Type:INTEGER
Field #04: AIMLOG.LOGDATE Alias:LOGDATE Type:INTEGER
Field #05: AIMLOG.LOGTIME Alias:LOGTIME Type:INTEGER
Field #06: AIMLOG.LOGTABLE Alias:LOGTABLE Type:STRING(30)
Field #07: AIMLOG.RECORDKEY Alias:RECORDKEY Type:STRING(30)
Field #08: AIMLOG.RECORDACTION Alias:RECORDACTION Type:STRING(10)
Field #09: AIMLOG.RECORDBEFORE Alias:RECORDBEFORE Type:STRING(1000)
Field #10: AIMLOG.RECORDAFTER Alias:RECORDAFTER Type:STRING(1000)
PLAN SORT ((A NATURAL))
Executing...
Done.
267615 fetches, 0 marks, 3818 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 0 index, 129986 seq.
Delta memory: 9460264 bytes.
Total execution time: 14.734s
Script execution finished.
Another simple query that should use the index (I would assume) but
doesn't is:
select max(a.id) from aimlog a
which uses the plan:
Starting transaction...
Preparing query: select max(a.id) from aimlog a
Prepare time: 0.015s
Field #01: .MAX Alias:MAX Type:INTEGER
PLAN (A NATURAL)
Executing...
Done.
267615 fetches, 0 marks, 3821 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 0 index, 129986 seq.
Delta memory: 9724 bytes.
Total execution time: 0.172s
Script execution finished.
The application we develop in, Clarion, uses a mechanism of SET the
records in a key order (usually placing the cursor at the bottom or top
of the order, in this case top), then PREVIOUS to find the highest
record, then adding 1 to get the next ID. (Don't question the method,
it works in the IDE we have never had an issue), however when the
application does it's SET/PREVIOUS it takes 10 to 15 seconds to
complete. I can only gather that it is doing a NATURAL read as above
instead of using the index.
We don't seem to have the issue (as far as I can tell) with other tables
that use the SET/PREVIOUS mechanism.
Is it a property of the 'order by' to not use the index, is it a bug, is
it a fault
[firebird-support] index use when using order by
Hi everyone. I am experiencing an unusual situation. I have a log table for tracking user changes to records. Table is as follows: CREATE TABLE AIMLOG ( ID Integer NOT NULL, USERNAME Char(12) COLLATE EN_UK, USERNO Integer, LOGDATE Integer, LOGTIME Integer, LOGTABLE Char(30) COLLATE EN_UK, RECORDKEY Char(30) COLLATE EN_UK, RECORDACTION Char(10) COLLATE EN_UK, RECORDBEFORE Varchar(1000) COLLATE EN_UK, RECORDAFTER Varchar(1000) COLLATE EN_UK, CONSTRAINT AIMLOG_PK PRIMARY KEY (ID) ); Using Flamerobin, if I want to find the last record I run the following query: SELECT a.ID, a.USERNAME, a.USERNO, a.LOGDATE, a.LOGTIME, a.LOGTABLE, a.RECORDKEY, a.RECORDACTION, a.RECORDBEFORE, a.RECORDAFTER FROM AIMLOG a order by a.id descending The query takes about 15 seconds because it doesn't want to use the primary key, AIMLOG_PK. Plan below: Starting transaction... Preparing query: SELECT a.ID, a.USERNAME, a.USERNO, a.LOGDATE, a.LOGTIME, a.LOGTABLE, a.RECORDKEY, a.RECORDACTION, a.RECORDBEFORE, a.RECORDAFTER FROM AIMLOG a order by a.id descending Prepare time: 0.015s Field #01: AIMLOG.ID Alias:ID Type:INTEGER Field #02: AIMLOG.USERNAME Alias:USERNAME Type:STRING(12) Field #03: AIMLOG.USERNO Alias:USERNO Type:INTEGER Field #04: AIMLOG.LOGDATE Alias:LOGDATE Type:INTEGER Field #05: AIMLOG.LOGTIME Alias:LOGTIME Type:INTEGER Field #06: AIMLOG.LOGTABLE Alias:LOGTABLE Type:STRING(30) Field #07: AIMLOG.RECORDKEY Alias:RECORDKEY Type:STRING(30) Field #08: AIMLOG.RECORDACTION Alias:RECORDACTION Type:STRING(10) Field #09: AIMLOG.RECORDBEFORE Alias:RECORDBEFORE Type:STRING(1000) Field #10: AIMLOG.RECORDAFTER Alias:RECORDAFTER Type:STRING(1000) PLAN SORT ((A NATURAL)) Executing... Done. 267615 fetches, 0 marks, 3818 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 0 index, 129986 seq. Delta memory: 9460264 bytes. Total execution time: 14.734s Script execution finished. Another simple query that should use the index (I would assume) but doesn't is: select max(a.id) from aimlog a which uses the plan: Starting transaction... Preparing query: select max(a.id) from aimlog a Prepare time: 0.015s Field #01: .MAX Alias:MAX Type:INTEGER PLAN (A NATURAL) Executing... Done. 267615 fetches, 0 marks, 3821 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 0 index, 129986 seq. Delta memory: 9724 bytes. Total execution time: 0.172s Script execution finished. The application we develop in, Clarion, uses a mechanism of SET the records in a key order (usually placing the cursor at the bottom or top of the order, in this case top), then PREVIOUS to find the highest record, then adding 1 to get the next ID. (Don't question the method, it works in the IDE we have never had an issue), however when the application does it's SET/PREVIOUS it takes 10 to 15 seconds to complete. I can only gather that it is doing a NATURAL read as above instead of using the index. We don't seem to have the issue (as far as I can tell) with other tables that use the SET/PREVIOUS mechanism. Is it a property of the 'order by' to not use the index, is it a bug, is it a fault in my table definition or am I missing the point? Firebird 2.5.7 (WI-2.5.7.27050) Just realised I 'should' be using 2.5.8, but on a server running WI-V2.5.8.27089 it still uses NATURAL. Any pointers or ideas? Thanks. Andrew Zenz
