Subject Help in optimizing big data query
Author Iwan Cahyadi Sugeng
Post date 2019-04-23T03:57:11Z
Hi,

I'm using Firebird 2.5.8 win 64, i have 33 millions data on one table
Here is the DML :
CREATE TABLE TBAT_ITM (
  ID_TBAT_ITM ID NOT NULL,
  ID_TBAT ID,
  KODE_MESIN KODE,
  NOMOR ID,
  TANGGAL_CETAK WAKTU,
  REVISION REVISI,
  CREATED_BY ID,
  CREATED_DATE WAKTU,
  MODIFIED_BY ID,
  MODIFIED_DATE WAKTU);


ALTER TABLE TBAT_ITM ADD CONSTRAINT PK_TBAT_ITM PRIMARY KEY (ID_TBAT_ITM);

ALTER TABLE TBAT_ITM ADD CONSTRAINT TBAT_TBAT_ITM FOREIGN KEY (ID_TBAT) REFERENCES TBAT(ID_TBAT) ON UPDATE CASCADE;


CREATE INDEX IDX_TBAT_ITM_1_FK ON TBAT_ITM(ID_TBAT);

CREATE UNIQUE INDEX IDX_TBAT_ITM_3 ON TBAT_ITM(NOMOR);

CREATE UNIQUE INDEX IDX_TBAT_ITM_PK ON TBAT_ITM(ID_TBAT_ITM);

I need to do a simple query to retrieve data from that table with this query:
SELECT 
  TBAT_ITM.NOMOR,
  TBAT_ITM.TANGGAL_CETAK,
  TBAT_ITM.KODE_MESIN
FROM TBAT_ITM
WHERE TBAT_ITM.TANGGAL_CETAK IS NULL AND
  TBAT_ITM.KODE_MESIN = 'DeviceC'      
ORDER BY TBAT_ITM.ID_TBAT_ITM;

PLAN The Firebird Select : 
Plan:
PLAN (TBAT_ITM ORDER IDX_TBAT_ITM_PK)
Adapted plan:
PLAN (TBAT_ITM ORDER IDX_TBAT_ITM_PK)

and here is the performance analysis

------ QUERY PERFORMANCE ------
Prepare       : 0 ms
Execute       : 0 ms
Avg fetch time: 00:03:10

----------- MEMORY ------------
Current       : 30.32 MB
Max           : 30.62 MB
Buffers       : 2048

------ TABLE OPERATIONS -------
Table name    : TBAT_ITM
Idx reads     : 31025673
Non-idx reads : 0
Updates       : 0
Deletes       : 0
Inserts       : 0

So the problem is that event though it use index read, but i expect not to read all record because i already set the where clause which should be limit the data return. The data will go even bigger up to 300 millions record, so if firebird still need to read all 300 millions data, then it will be a problem in the near future.

I Also already do a full reindex:

RDB$INDEX_NAME RDB$STATISTICS
IDX_TBAT_ITM_3 3.22580646638926E-8

So can anyone give me a performance suggestion?

Thanks