Dear all,
we stumbled upon the following problem: we have a large table
(roughly 500k rows) with three columns.The selectivity of all 3
columns is 1 whereas the correct value is 4 for one of them. It is
correctly computed as 4 when we set the SAMPLE_SIZE to 0 (scan all
rows). With these incorrect selectivity values the optimizer picks the
wrong index and the performance hit is huge: 4675 pages read instead
of 7 in the correct case. Is there a way to force H2 to use an index
instead of another one (hint?)?
How exactly is selectivity computed? In the H2 console, if we display
the first 10000 rows of this table, we obtain 139 distinct values
yielding a selectivity of 1 we presume; Is that correct?
thank you,
Manuel.
PS: please find below the numbers:
-- statistics on our table (endf_sections)
SELECT COUNT(DISTINCT MAT_KEY), COUNT(DISTINCT MF), COUNT(DISTINCT
MT), COUNT(1) FROM endf_sections;
21322 20 409 474666
--this table has the following indexes:
PRIMARY KEY: sec_key column
ENDF_SECTIONS_1: both mf and mt columns
IDX_ENDF_SECTIONS_MAT_KEY: mat_key column
-- setting mat_key selectivity to 1
ALTER TABLE endf_seCTIONS ALTER COLUMN mat_key SELECTIVITY 1
EXPLAIN ANALYZE SELECT COUNT(1) FROM endf_sections WHERE mat_key=5159
AND mf=3 AND (mt=4 OR mt=14)
SELECT
COUNT(1)
FROM PUBLIC.ENDF_SECTIONS
/* PUBLIC.ENDF_SECTIONS_1: MT IN(4, 14)
AND MF = 3
*/
/* scanCount: 180796 */
WHERE (MT IN(4, 14))
AND ((MAT_KEY = 5159)
AND (MF = 3))
/*
total: 4675
ENDF_SECTIONS.ENDF_SECTIONS_1 read: 1460 (31%)
ENDF_SECTIONS.ENDF_SECTIONS_DATA read: 3215 (68%)
*/
-- setting mat_key selectivity to 4
ALTER TABLE endf_seCTIONS ALTER COLUMN mat_key SELECTIVITY 4
EXPLAIN ANALYZE SELECT COUNT(1) FROM endf_sections WHERE mat_key=5159
AND mf=3 AND (mt=4 OR mt=14)
SELECT
COUNT(1)
FROM PUBLIC.ENDF_SECTIONS
/* PUBLIC.IDX_ENDF_SECTIONS_MAT_KEY: MAT_KEY = 5159 */
/* scanCount: 113 */
WHERE (MT IN(4, 14))
AND ((MAT_KEY = 5159)
AND (MF = 3))
/*
total: 7
ENDF_SECTIONS.ENDF_SECTIONS_DATA read: 3 (42%)
ENDF_SECTIONS.IDX_ENDF_SECTIONS_MAT_KEY read: 4 (57%)
*/
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
