[Firebird-devel] [FB-Tracker] Created: (CORE-3014) GROUP BY f1, f3, f2 whereas index key is (f1, f2, f3): plan become inefficient (NATRAL instead of ORDER)

[Pavel Z. (JIRA) <tr...@fi...>]

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

                 Key: CORE-3014
                 URL: http://tracker.firebirdsql.org/browse/CORE-3014
             Project: Firebird Core
          Issue Type: Improvement
          Components: Engine
    Affects Versions: 2.5 RC2
         Environment: all of FB versions
            Reporter: 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 ?

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira