You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
recreate table tmpidx(node_id int, ref_a int, ref_b int, ref_c int, val numeric(12,2));
create unique index tmp_idx1 on tmpidx (node_id, ref_a, ref_b, ref_c);
insert into tmpidx values(101, 1301, 33, 6, 12345.67);
insert into tmpidx values(101, 1301, 33, 5, 23456.78);
insert into tmpidx values(101, 1307, 32, 4, 10001.01);
insert into tmpidx values(102, 1303, 34, 3, 22222.33);
insert into tmpidx values(102, 1303, 34, 2, 43434.22);
insert into tmpidx values(102, 1303, 34, 1, 58582.27);
insert into tmpidx values(102, 1308, 30, 8, 77728.17);
insert into tmpidx values(103, 1305, 31, 9, 19191.11);
insert into tmpidx values(103, 1305, 31, 1, 54321.09);
commit;
1) The query like this:
select t.node_id, t.ref_a, t.ref_b, count(*) c
from tmpidx t
group by t.node_id, t.ref_A, t.ref_B
has a "good plan", i.e. is DOES use index: PLAN (T ORDER TMP_IDX1)
This is because index key "STARTS" exactly the same as "argument" of GROUP BY (fields node_id, ref_a & ref_b are specified in index in the SAME MATTER as in GROUP BY).
2) But if we make quiet insignificant mistake and specifies these fields in muddled order like this:
select t.node_id, t.ref_a, t.ref_b, count(*) c
from tmpidx t
group by t.node_id, t.ref_B, t.ref_A
- we get a "bad plan": PLAN SORT ((T NATURAL)).
Note that ALL the fields in any case are from the "START" of index key expression (1st, 2nd and 3rd field; but not 4th).
The resultset in both samples is the same (from relational point of view).
Unfortunately, FB does not recognize that index can be used in second variant. Can it be improved ?
The text was updated successfully, but these errors were encountered:
Submitted by: @pavel-zotov
Given a DDL + data:
recreate table tmpidx(node_id int, ref_a int, ref_b int, ref_c int, val numeric(12,2));
create unique index tmp_idx1 on tmpidx (node_id, ref_a, ref_b, ref_c);
insert into tmpidx values(101, 1301, 33, 6, 12345.67);
insert into tmpidx values(101, 1301, 33, 5, 23456.78);
insert into tmpidx values(101, 1307, 32, 4, 10001.01);
insert into tmpidx values(102, 1303, 34, 3, 22222.33);
insert into tmpidx values(102, 1303, 34, 2, 43434.22);
insert into tmpidx values(102, 1303, 34, 1, 58582.27);
insert into tmpidx values(102, 1308, 30, 8, 77728.17);
insert into tmpidx values(103, 1305, 31, 9, 19191.11);
insert into tmpidx values(103, 1305, 31, 1, 54321.09);
commit;
1) The query like this:
select t.node_id, t.ref_a, t.ref_b, count(*) c
from tmpidx t
group by t.node_id, t.ref_A, t.ref_B
has a "good plan", i.e. is DOES use index: PLAN (T ORDER TMP_IDX1)
This is because index key "STARTS" exactly the same as "argument" of GROUP BY (fields node_id, ref_a & ref_b are specified in index in the SAME MATTER as in GROUP BY).
2) But if we make quiet insignificant mistake and specifies these fields in muddled order like this:
select t.node_id, t.ref_a, t.ref_b, count(*) c
from tmpidx t
group by t.node_id, t.ref_B, t.ref_A
- we get a "bad plan": PLAN SORT ((T NATURAL)).
Note that ALL the fields in any case are from the "START" of index key expression (1st, 2nd and 3rd field; but not 4th).
The resultset in both samples is the same (from relational point of view).
Unfortunately, FB does not recognize that index can be used in second variant. Can it be improved ?
The text was updated successfully, but these errors were encountered: