Issue Details (XML | Word | Printable)

Key: CORE-3014
Type: Improvement Improvement
Status: Open Open
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Pavel Zotov
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

GROUP BY f1, f3, f2 whereas index key is (f1, f2, f3): plan become inefficient (NATRAL instead of ORDER)

Created: 24/May/10 08:31 PM   Updated: 10/Jun/10 09:35 AM
Component/s: Engine
Affects Version/s: 2.5 RC2
Fix Version/s: None

Environment: all of FB versions


 Description  « Hide
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 ?

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
There are no comments yet on this issue.