Re: [firebird-support] Why index is not used in this query?
SELECT * FROM TABLE_2 T2 INNER JOIN TABLE_1 T1 ON T2.TABLE_1_ID = T1.ID After executing this query I am getting such plan: PLAN JOIN (T1 NATURAL, T2 INDEX (FK_TABLE_2)) Why in case of TABLE_1 optimizer did not chose index PK_TABLE_1? And why I am getting error when i try to enforce it with: PLAN JOIN (T1 INDEX (PK_TABLE_1), T2 INDEX (FK_TABLE_2)) Admittedly, my way of plan thinking is far from the way things are implemented, but still - it normally helps me understand why the optimizer behaves as it does. A simple query like yours I typically consider from the first table to the last in the plan. Hence, I'd start with T1. SELECT * FROM TABLE_1 T1 Here, there's no limiting JOIN, WHERE or anything similar that can be used to limit the number of rows to return (T2 isn't involved yet, hence any clause referring to that tuple cannot be considered). So, the quickest and only sensible option (at least unless you want to slow things down) is to go NATURAL on T1. Then, we add T2 INNER JOIN TABLE_2 T2 ON T2.TABLE_1_ID = T1.ID Here, you already have T1 available, and using an index for T2.TABLE_1_ID will help reducing the potential number of records to look up considerably. Is there no way to utilize the index for T1.ID? Sure there is, you can start with T2 rather than T1. However, then you would start with SELECT * FROM TABLE_2 T2 which cannot use an index. Adding INNER JOIN TABLE_1 T1 ON T2.TABLE_1_ID = T1.ID would, however, benefit from using the index for T1.ID. Is there really no way to use an index for both fields? No, not unless you add a WHERE (or ON) clause that limits the result set through including one or more constants, e.g. AND T1.ID between 2 and 4 Having said that, it is only on rather big tables that it is bad to see NATURAL on the first tuple in the plan (well, in more complex queries - e.g. involving subselects - it may not be the first tuple). And I think you would normally use a proper WHERE clause on big tables, although it is of course thinkable to e.g. want to have a query that should find all persons being a citizen of both London and Paris (millions of persons in both tables, but only a very small subset being part of both). Such rare queries are normally slowish, sometimes even after optimization. HTH, Set
Re: [firebird-support] Why index is not used in this query?
How about something along the lines of: A table that small is going to fit into a single disk page. So a table scan involves reading one disk page. Using the index would involve reading the index as well, which is a second disk page, so twice as slow. ? (Other RDBMS which have a covering index concept can, for appropriate queries, use the index without reading the table at all, and might come to a different conclusion in such cases.) On 15/05/2015 14:02, brucedickin...@wp.pl [firebird-support] wrote: Hello, here is full example: CREATE TABLE TABLE_1 ( ID INTEGER NOT NULL, NAME VARCHAR(32), CONSTRAINT PK_TABLE_1 PRIMARY KEY (ID) ); GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TABLE_1 TO SYSDBA WITH GRANT OPTION; CREATE TABLE TABLE_2 ( ID INTEGER NOT NULL, TABLE_1_ID INTEGER ); ALTER TABLE TABLE_2 ADD CONSTRAINT FK_TABLE_2 FOREIGN KEY (TABLE_1_ID) REFERENCES TABLE_1 (ID); GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TABLE_2 TO SYSDBA WITH GRANT OPTION; INSERT INTO TABLE_1 VALUES(1, 'V_1'); INSERT INTO TABLE_1 VALUES(2, 'V_2'); INSERT INTO TABLE_1 VALUES(3, 'V_3'); INSERT INTO TABLE_1 VALUES(4, 'V_4'); INSERT INTO TABLE_1 VALUES(5, 'V_5'); INSERT INTO TABLE_2 VALUES(1, 1); INSERT INTO TABLE_2 VALUES(2, 1); INSERT INTO TABLE_2 VALUES(3, 2); INSERT INTO TABLE_2 VALUES(4, 2); INSERT INTO TABLE_2 VALUES(5, 3); SELECT * FROM TABLE_2 T2 INNER JOIN TABLE_1 T1 ON T2.TABLE_1_ID = T1.ID After executing this query I am getting such plan: PLAN JOIN (T1 NATURAL, T2 INDEX (FK_TABLE_2)) Why in case of TABLE_1 optimizer did not chose index PK_TABLE_1? And why I am getting error when i try to enforce it with: PLAN JOIN (T1 INDEX (PK_TABLE_1), T2 INDEX (FK_TABLE_2)) Thanks for any clues. -- Tim Ward
Re: [firebird-support] Why index is not used in this query?
On May 15, 2015, at 9:02 AM, brucedickin...@wp.pl [firebird-support] firebird-support@yahoogroups.com wrote: SELECT * FROM TABLE_2 T2 INNER JOIN TABLE_1 T1 ON T2.TABLE_1_ID = T1.ID After executing this query I am getting such plan: PLAN JOIN (T1 NATURAL, T2 INDEX (FK_TABLE_2)) Why in case of TABLE_1 optimizer did not chose index PK_TABLE_1? And why I am getting error when i try to enforce it with: PLAN JOIN (T1 INDEX (PK_TABLE_1), T2 INDEX (FK_TABLE_2)) There's nothing to look up in the primary key index of T1 - indexes are used to find records withh specific values in the key fields. If you added another condition like T1.ID between 5 and 10, you'd get the plan you expect. Good luck, Ann here is full example: CREATE TABLE TABLE_1 ( ID INTEGER NOT NULL, NAME VARCHAR(32), CONSTRAINT PK_TABLE_1 PRIMARY KEY (ID) ); GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TABLE_1 TO SYSDBA WITH GRANT OPTION; CREATE TABLE TABLE_2 ( ID INTEGER NOT NULL, TABLE_1_ID INTEGER ); ALTER TABLE TABLE_2 ADD CONSTRAINT FK_TABLE_2 FOREIGN KEY (TABLE_1_ID) REFERENCES TABLE_1 (ID); GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON TABLE_2 TO SYSDBA WITH GRANT OPTION; INSERT INTO TABLE_1 VALUES(1, 'V_1'); INSERT INTO TABLE_1 VALUES(2, 'V_2'); INSERT INTO TABLE_1 VALUES(3, 'V_3'); INSERT INTO TABLE_1 VALUES(4, 'V_4'); INSERT INTO TABLE_1 VALUES(5, 'V_5'); INSERT INTO TABLE_2 VALUES(1, 1); INSERT INTO TABLE_2 VALUES(2, 1); INSERT INTO TABLE_2 VALUES(3, 2); INSERT INTO TABLE_2 VALUES(4, 2); INSERT INTO TABLE_2 VALUES(5, 3); SELECT * FROM TABLE_2 T2 INNER JOIN TABLE_1 T1 ON T2.TABLE_1_ID = T1.ID After executing this query I am getting such plan: PLAN JOIN (T1 NATURAL, T2 INDEX (FK_TABLE_2)) Why in case of TABLE_1 optimizer did not chose index PK_TABLE_1? And why I am getting error when i try to enforce it with: PLAN JOIN (T1 INDEX (PK_TABLE_1), T2 INDEX (FK_TABLE_2)) Thanks for any clues.
RE: [firebird-support] Why index is not used in this query?
Bruce, SELECT * FROM TABLE_2 T2 INNER JOIN TABLE_1 T1 ON T2.TABLE_1_ID = T1.ID After executing this query I am getting such plan: PLAN JOIN (T1 NATURAL, T2 INDEX (FK_TABLE_2)) Why in case of TABLE_1 optimizer did not chose index PK_TABLE_1? Given that there is no ORDER BY clause it is much faster to walk Table_1 in natural order, then it is to use any index (even primary key). Using an index always requires that the rows related to the index value be read to confirm that the indexed value is still valid (indexes contain all of the index values which a row could have had -- so the index may have a value which is no longer valid). So, reading by index would generate a huge amount of random IO, whereas walking the table is much more lightweight. Sean
