Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
firebird-automations opened this issue May 24, 2010 · 1 comment

Comments

@firebird-automations
Copy link
Collaborator

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 ?

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants