firebird - 火鸟和虚拟条件

Question

我有两个类似的查询。在一种情况下，有额外的虚拟条件（1=1、0=0、真）：

SELECT t1.*
FROM table1 t1
JOIN table2 t2 ON t2.fk_t1 = t1.id
JOIN table3 t3 ON t3.id = t1.fk_t3
WHERE
    0 = 0 AND /* with this in 1st case, without this line in 2nd case */
    t3.field = 6
    AND EXISTS (SELECT 1 FROM table2 x WHERE x.fk2_t2 = t2.id)

所有必要的字段都被索引。

对于每种情况，Firebird（2.1 和 3.0 版本）的工作方式不同，读取统计信息如下所示：

第一种情况（0=0）：

Query Time
------------------------------------------------
Prepare       : 32,00 ms
Execute       : 1 046,00 ms
Avg fetch time: 61,53 ms

Operations
------------------------------------------------
Read   : 8 342
Writes : 1
Fetches: 1 316 042
Marks  : 0


Enhanced Info:
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|          Table Name           |  Records  |  Indexed  | Non-Indexed | Updates | Deletes | Inserts | Backouts |  Purges  | Expunges |
|                               |   Total   |   reads   |    reads    |         |         |         |          |          |          |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|TABLE2                         |         0 |      4804 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
|TABLE1                         |         0 |         0 |       96884 |       0 |       0 |       0 |        0 |        0 |        0 |
|TABLE3                         |         0 |    387553 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

在第二种情况下（没有虚拟条件）：

Query Time
------------------------------------------------
Prepare       : 16,00 ms
Execute       : 515,00 ms
Avg fetch time: 30,29 ms

Operations
------------------------------------------------
Read   : 7 570
Writes : 1
Fetches: 648 103
Marks  : 0


Enhanced Info:
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|          Table Name           |  Records  |  Indexed  | Non-Indexed | Updates | Deletes | Inserts | Backouts |  Purges  | Expunges |
|                               |   Total   |   reads   |    reads    |         |         |         |          |          |          |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|TABLE2                         |         0 |       506 |      152655 |       0 |       0 |       0 |        0 |        0 |        0 |
|TABLE1                         |         0 |       467 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
|TABLE3                         |         0 |      1885 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

查询有不同的执行计划。

PLAN JOIN (T2 NATURAL, T1 INDEX (T1_ID_IDX), T3 INDEX (T3_ID_IDX)) 

PLAN JOIN (T1 NATURAL, T3 INDEX (T3_ID_IDX1), T2 INDEX (T2_FK_T1_IDX))

这对我来说很奇怪。为什么具有相同条件意义的查询效果如此不同？FB 优化器如何工作以及如何编写快速和优化的查询？这怎么理解？

PS https://github.com/FirebirdSQL/firebird/issues/6941

标签： firebird