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
indexes with low selectivity kill the select execution [CORE47] #332
Comments
Commented by: Alice F. Bird (firebirds) Date: 2006-02-28 11:21 I made further investigation of this problem and I made the Problem: Testing: Result: 1.1 - Select from a procedure SALDO 1.2 - Select from procedure SALDO executed without procedure 1.3 - Select from procedure SALDO executed without CREATE PROCEDURE SALDO (DATDO DATE) select select suspend; end |
Commented by: Alice F. Bird (firebirds) Date: 2006-02-17 15:04 Please post your database here or send it to me by email or |
Commented by: Alice F. Bird (firebirds) Date: 2006-02-17 10:39 OK, if you want to close this case now, I agree. Then the |
Commented by: Alice F. Bird (firebirds) Date: 2006-02-16 17:23 I'm afraid we cannot help in such a volatile scenario. A |
Commented by: Alice F. Bird (firebirds) Date: 2006-02-16 13:52 After further testing I still insist on the error. There is Finaly I tested which index has this bad influence. First I |
Commented by: Alice F. Bird (firebirds) Date: 2006-02-16 12:16 Yes, I did backup and restore before testing yesterday. I 1) Indexes INACTIVE 2) Indexes ACTIVE |
Commented by: Alice F. Bird (firebirds) Date: 2006-02-15 22:36 A couple of things: First, did you backup/restore the database so that the new If not, please do so and re-test. Second, please post the PLAN generated for the statement. |
Modified by: Alice F. Bird (firebirds)description: SFID: 1432475# Having some active indexes with low selectivity is Example of a SELECT statement: The indexes with low selectivity are set on Both SKLPOH and SKLPOHDETAIL tables have about 200.000 If someone is interested, I can post the database file Martin => SFID: 1432475# Having some active indexes with low selectivity is Example of a SELECT statement: The indexes with low selectivity are set on Both SKLPOH and SKLPOHDETAIL tables have about 200.000 If someone is interested, I can post the database file Martin |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: @pcisarWorkflow: jira [ 10071 ] => Firebird [ 14240 ] |
Commented by: Ján Kolár (kolar) I noticed the same thing on following select: select * from ATTESTVALUES where ATTEST_ID = :ID order by ORD To see what the problem is i measured times needed for similar select commands: Lets see definition of table ATTESTVALUES: There is also automatically created index RDB$FOREIGN9 on column ATTEST_ID which is a foreign key. "select * from ATTESTVALUES where ATTEST_ID = :ID PLAN(ATTESTVALUES INDEX(RDB$FOREIGN9)) order by ORD" i.e. I purposefully disable index ORDX on column ORD now the query takes around 0.05 seconds. If the Firebird would apply indexes in opposite order i.e. first apply index with high selectivity RDB$FOREIGN9 and then index with low selectivity ORDX I think I would not see significant differences in times. I understand that index ORDX is maybe in my example uneccessary (because to sort few tens or few hundreds of records we dont need index) but good database should handle this. |
Submitted by: centros (centros)
SFID: 1432475#
Submitted By: centros
Having some active indexes with low selectivity is
killing condition for some SELECT statements. It is a
problem of a planner, that such bad indexes are used in
a plan. SELECT execution is about 10 to 50 times
slower, compared to a situation, when such indexes are
set inactive. I am testing on FB 2.0 Beta 2, the same
problem was on FB 1.5. The same SELECT on IB 6.0 or IB
7.5 executes nice fast, even with such bad indexes. IB
planner is clever enough not to use indexes with low
selectivity in a plan.
Example of a SELECT statement:
select
sum(SKLPOHDETAILMNOZSTVI),sum(SKLPOHDETAILCELKEMCENANAKUP),sum(SKLPOHDETAILCELKEMCENAPRODEJ)
from SKLPOHDETAIL
left outer join SKLPOH on
SKLPOHDETAIL.IDSKLPOH=SKLPOH.IDSKLPOH
where SKLPOHDATZADAL >= :DATOD AND SKLPOHDATZADAL <=
:DATDO AND SKLPOHTYP=10 AND
EIDSKLADKARTA=:EIDSKLADKARTA and
SKLPOHDETAIL.recordactive='1' and SKLPOH.recordactive =
'1' and SKLPOHSTORNOVANO='0'
The indexes with low selectivity are set on
SKLPOH.recordactive and SKLPOHDETAIL.recordactive.
These items have only three possible values, 0, 1 or 2.
Most of them have value 1.
Both SKLPOH and SKLPOHDETAIL tables have about 200.000
rows each. Overal database size is about 300 MB.
If someone is interested, I can post the database file
for testing.
Martin
The text was updated successfully, but these errors were encountered: