Add possibility to get index size on disk

[PavelTurk]

As I understand H2 currently doesn't provide the size of index on disk. There is a function disk_space_used but this function doesn't provide index size.

So I suggest to add such posssibility.

As it is more preferable to get index size separately from table size maybe it is better to add function disk_space_used_idx (index_name).

Another way is to provide table and index size (separately from each other) via information_schema. I think that using information_schema will be closer to SQL standard.

[katzyn]

Indexes are not covered by the SQL Standard at all.

Information about on-disk size isn't stored anywhere and its calculation can be slow, so it is a bad idea to include it into meta tables, but a separate function can be implemented.

[PavelTurk]

@katzyn

Indexes are not covered by the SQL Standard at all.

I think you are right as I am not expert in SQL. I meant that many RDBMS do it using their own version of information_schema. For example in PSQL (pg_catalog):

SELECT
   relname  as table_name,
   pg_size_pretty(pg_total_relation_size(relid)) As "Total Size",
   pg_size_pretty(pg_indexes_size(relid)) as "Index Size",
   pg_size_pretty(pg_relation_size(relid)) as "Actual Size"
   FROM pg_catalog.pg_statio_user_tables 
ORDER BY pg_total_relation_size(relid) DESC;

In SQL Server (sys):

SELECT i.[name] AS IndexName
	,SUM(s.[used_page_count]) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
	AND s.[index_id] = i.[index_id]
GROUP BY i.[name]
ORDER BY i.[name]

[PavelTurk]

@katzyn I am now testing DB_OBJECT_SIZE and two times I had situation when all foreign key indexes disappeared from information_schema.indexes. So when I did SELECT * FROM information_schema.indexes; I got only all PRIMARY KEY and one UNIQUE INDEX. But there was no any INDEX. Can it be somehow linked to DB_OBJECT_SIZE?

[katzyn]

Primary keys, unique keys and foreign keys are constraints.

You can read information about them from INFORMATION_SCHEMA.TABLE_CONSTRAINTS, INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS, and INFORMATION_SCHEMA.KEY_COLUMN_USAGE.

(There are also other types of constraints and other tables in INFORMATION_SCHEMA.)

Some types of constraints in H2 currently always create or use an existing compatible index (or even multiple indexes in historic versions of H2), but in some other database systems such indexes aren't required. If a constraint has an index, H2 reports its name in additional non-standard and non-portable INDEX_CATALOG / INDEX_SCHEMA / INDEX_NAME columns in already mentioned standard INFORMATION_SCHEMA.TABLE_CONSTRAINTS table. Multiple constraints may have the same index in some cases, for example, if set of columns references another set of columns and referencing columns also have a unique or a primary key constraint most likely both these constraints will try to share the same index.

Detailed information about indexes is available in non-standard INFORMATION_SCHEMA.INDEXES and INFORMATION_SCHEMA.INDEX_COLUMNS tables, but you can't get information about constraints from these tables.

[PavelTurk]

@katzyn Thank you very much for your detailed explanation. But can I use INFORMATION_SCHEMA.INDEXES to get information about indexes not about constraints?

When I say that indexes disappear I mean the following. These are my indexes when everything is OK - 33 indexes:

And these are my indexes when something happened - same database, no any changes (no alter, no insert/update/delete etc).

As you see there are only 7 indexes. Maybe something happened is about using

SELECT DB_OBJECT_SIZE('TABLE', ' schema_name', 'table_name') <-- WRONG SQL

The above SQL has an error - see space before schema name. Now I am trying to reproduce this situation. But I had it already 3 times.

[katzyn]

This function doesn't modify anything and I have no idea what happened with indexes in your database.

[PavelTurk]

@katzyn I have the same problem the 4'th time and now I saved the broken database and compared it with normal database:

               | normal | broken
--------------------------------
custom indexes | 33     | 7
custom tables  | 9      | 6
custom schemas | 4      | 3
custom rows    | 3      | 0

As you see not only indexes gone, but also tables, schemas and all rows. One schema is empty - no table inside it. But in normal DB this schema has 2 tables.

I use h2 2.2.229-SNAPSHOT with such url jdbc:h2:tcp://localhost/mydb;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;DEFAULT_NULL_ORDERING=HIGH;TRACE_LEVEL_FILE=4.

I worked with 2.2.224 and didn't have such problems (although I didn't use PSQL mode much). Any hints can help me to find a way to reproduce this problem?