Hi!
I use such a select statement:
SELECT id, node_path, name, comment, host_identity,
last_connection_time, policy_counter, full_status, oid_id, value
FROM domain_tree LEFT JOIN (SELECT domain_id, status.oid_id, value
FROM status INNER JOIN cached_oids ON cached_oids.oid_id =
status.oid_id) ON id = domain_id
WHERE parent_path IS NOT NULL
UNION
SELECT CURRVAL ('domain_tree_revision') AS id, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL
UNION
SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CAST (COUNT(*)
AS INT) AS oid_id, NULL
FROM domain_tree
WHERE parent_path IS NULL
ORDER BY node_path ASC
Running h2 147 build i had execution time 3 times less than on h2
build 153. In both cases db schema and content are identical. I looked
into EXPLAIN ANALYZE and was surprised.
According to the PLAN on 153 build db 'total' processed rows - 152448,
but on 147 - 41204. Also I can't explain why build 153 db reads extra
data not relevant for the select. I can see that issue is not
reproduced if i use only first part of the select without any 'union'
clauses. Schema or db can be provided if needed. Could you comment
that issue?
PLAN for 147 build looks like :
((SELECT DISTINCT ID, NODE_PATH, NAME, COMMENT, HOST_IDENTITY,
LAST_CONNECTION_TIME, POLICY_COUNTER, FULL_STATUS, OID_ID, VALUE
FROM PUBLIC.DOMAIN_TREE /* PUBLIC.DOMAIN_TREE.tableScan */ /* WHERE
PARENT_PATH IS NOT NULL */ /* scanCount: 6627 */
LEFT OUTER JOIN (SELECT DOMAIN_ID, STATUS.OID_ID, VALUE
FROM PUBLIC.CACHED_OIDS /* PUBLIC.CACHED_OIDS.tableScan */
INNER JOIN PUBLIC.STATUS /* PUBLIC.CONSTRAINT_INDEX_9: OID_ID =
CACHED_OIDS.OID_ID */ ON 1=1
WHERE CACHED_OIDS.OID_ID = STATUS.OID_ID) _0 /* SELECT DOMAIN_ID,
STATUS.OID_ID, VALUE
FROM PUBLIC.STATUS /++ PUBLIC.PRIMARY_KEY_9: DOMAIN_ID IS ?1 ++/ /++
WHERE DOMAIN_ID IS ?1 ++/
INNER JOIN PUBLIC.CACHED_OIDS /++ PUBLIC.PRIMARY_KEY_B: OID_ID =
STATUS.OID_ID ++/ ON 1=1
WHERE (DOMAIN_ID IS ?1) AND (CACHED_OIDS.OID_ID = STATUS.OID_ID):
DOMAIN_ID = ID */ ON ID = DOMAIN_ID /* scanCount: 283776 */
WHERE PARENT_PATH IS NOT NULL) UNION (SELECT DISTINCT
CURRVAL('domain_tree_revision') AS ID, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL
FROM SYSTEM_RANGE(1, 1) /* PUBLIC.RANGE_INDEX */ /* scanCount: 2 */))
UNION (SELECT DISTINCT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
CAST(COUNT(*) AS INTEGER) AS OID_ID, NULL
FROM PUBLIC.DOMAIN_TREE /* PUBLIC.DOMAIN_TREE_PARENT_PATH_INDEX:
PARENT_PATH IS NULL */ /* scanCount: 4 */
WHERE PARENT_PATH IS NULL) ORDER BY 2
/*
total: 41204
DOMAIN_TREE.DOMAIN_TREE_DATA read: 412 (0%)
DOMAIN_TREE.DOMAIN_TREE_PARENT_PATH_INDEX read: 3 (0%)
STATUS.PRIMARY_KEY_9 read: 10322 (25%)
STATUS.STATUS_DATA read: 30456 (73%)
overflow read: 11 (0%)
*/
(1 row, 9159 ms)
PLAN for the 1.3.153 is:
((SELECT DISTINCT
ID,
NODE_PATH,
NAME,
COMMENT,
HOST_IDENTITY,
LAST_CONNECTION_TIME,
POLICY_COUNTER,
FULL_STATUS,
OID_ID,
VALUE
FROM PUBLIC.DOMAIN_TREE
/* PUBLIC.DOMAIN_TREE.tableScan */
/* WHERE PARENT_PATH IS NOT NULL
*/
/* scanCount: 6627 */
LEFT OUTER JOIN (
SELECT
DOMAIN_ID,
STATUS.OID_ID,
VALUE
FROM PUBLIC.CACHED_OIDS
/* PUBLIC.CACHED_OIDS.tableScan */
INNER JOIN PUBLIC.STATUS
/* PUBLIC.CONSTRAINT_INDEX_9: OID_ID = CACHED_OIDS.OID_ID */
ON 1=1
WHERE CACHED_OIDS.OID_ID = STATUS.OID_ID
) _1
/* SELECT
DOMAIN_ID,
STATUS.OID_ID,
VALUE
FROM PUBLIC.STATUS
/++ PUBLIC.PRIMARY_KEY_9: DOMAIN_ID IS ?1 ++/
/++ WHERE DOMAIN_ID IS ?1
++/
INNER JOIN PUBLIC.CACHED_OIDS
/++ PUBLIC.PRIMARY_KEY_B: OID_ID = STATUS.OID_ID ++/
ON 1=1
WHERE (DOMAIN_ID IS ?1)
AND (CACHED_OIDS.OID_ID = STATUS.OID_ID): DOMAIN_ID = ID
*/
ON ID = DOMAIN_ID
/* scanCount: 283776 */
WHERE PARENT_PATH IS NOT NULL)
UNION
(SELECT DISTINCT
CURRVAL('domain_tree_revision') AS ID,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
FROM SYSTEM_RANGE(1, 1)
/* PUBLIC.RANGE_INDEX */
/* scanCount: 2 */))
UNION
(SELECT DISTINCT
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
CAST(COUNT(*) AS INTEGER) AS OID_ID,
NULL
FROM PUBLIC.DOMAIN_TREE
/* PUBLIC.DOMAIN_TREE_PARENT_PATH_INDEX: PARENT_PATH IS NULL */
/* scanCount: 4 */
WHERE PARENT_PATH IS NULL)
ORDER BY 2
/*
total: 152448
ALERTS.ALERTS_DATA read: 1 (0%)
ALERTS.ALERTS_DOMAIN_TIME_INDEX read: 1 (0%)
ALERTS.CONSTRAINT_INDEX_7 read: 1 (0%)
ALERTS.CONSTRAINT_INDEX_73 read: 1 (0%)
ALERTS.CONSTRAINT_INDEX_733 read: 1 (0%)
ALERT_MESSAGES.ALERT_MESSAGES_DATA read: 1 (0%)
ALERT_MESSAGES.PRIMARY_KEY_2 read: 1 (0%)
APPLICATIONS.APPLICATIONS_DATA read: 1 (0%)
APPLICATIONS.PRIMARY_KEY_C1 read: 1 (0%)
AUTHORITIES.AUTHORITIES_DATA read: 1 (0%)
AUTHORITIES.PRIMARY_KEY_A read: 1 (0%)
CACHED_OIDS.CACHED_OIDS_DATA read: 1 (0%)
DELETED_DOMAINS.DELETED_DOMAINS_DATA read: 1 (0%)
DELETED_DOMAINS.DELETED_DOMAINS_REVISION_INDEX read: 1 (0%)
DOMAIN_TREE.CONSTRAINT_INDEX_8 read: 1 (0%)
DOMAIN_TREE.DOMAIN_TREE_DATA read: 458 (0%)
DOMAIN_TREE.DOMAIN_TREE_LAST_CONNECTION_TIME_INDEX read: 1 (0%)
DOMAIN_TREE.DOMAIN_TREE_NODE_PATH_INDEX read: 1 (0%)
DOMAIN_TREE.DOMAIN_TREE_PARENT_PATH_INDEX read: 4 (0%)
DOMAIN_TREE.DOMAIN_TREE_REVISION_INDEX read: 1 (0%)
IMPORT_RULES.CONSTRAINT_INDEX_5 read: 1 (0%)
IMPORT_RULES.IMPORT_RULES_DATA read: 1 (0%)
IMPORT_RULE_PATTERNS.IMPORT_RULE_PATTERNS_DATA read: 1 (0%)
IMPORT_RULE_PATTERNS.PRIMARY_KEY_47 read: 1 (0%)
INFECTION_ALERTS.INFECTION_ALERTS_DATA read: 1 (0%)
INSTALLATION_OPERATIONS.CONSTRAINT_INDEX_3C read: 1 (0%)
INSTALLATION_OPERATIONS.CONSTRAINT_INDEX_3C7 read: 1 (0%)
INSTALLATION_OPERATIONS.CONSTRAINT_INDEX_3C73 read: 1 (0%)
INSTALLATION_OPERATIONS.INSTALLATION_OPERATIONS_DATA read: 1 (0%)
LOBS.LOBS_DATA read: 1 (0%)
LOB_DATA.LOB_DATA_DATA read: 1 (0%)
LOB_MAP.INDEX_LOB_MAP_DATA_LOB read: 1 (0%)
LOB_MAP.LOB_MAP_DATA read: 1 (0%)
LOB_MAP.PRIMARY_KEY_3 read: 1 (0%)
MANAGEMENT_KEYS.MANAGEMENT_KEYS_DATA read: 1 (0%)
MANAGEMENT_KEYS.PRIMARY_KEY_6 read: 1 (0%)
MIBS.MIBS_DATA read: 1 (0%)
OID_DICTIONARY.CONSTRAINT_INDEX_3 read: 1 (0%)
OID_DICTIONARY.OID_DICTIONARY_DATA read: 1 (0%)
OPERATIONS_STATUS.CONSTRAINT_INDEX_E read: 1 (0%)
OPERATIONS_STATUS.CONSTRAINT_INDEX_EF read: 1 (0%)
OPERATIONS_STATUS.OPERATIONS_STATUS_DATA read: 1 (0%)
OPERATIONS_STATUS.PRIMARY_KEY_E read: 1 (0%)
PACKAGES.CONSTRAINT_INDEX_5B read: 1 (0%)
PACKAGES.CONSTRAINT_INDEX_5B6 read: 1 (0%)
PACKAGES.CONSTRAINT_INDEX_5B67 read: 1 (0%)
PACKAGES.PACKAGES_DATA read: 1 (0%)
PACKAGE_CONFIGS.CONSTRAINT_INDEX_90 read: 1 (0%)
PACKAGE_CONFIGS.PACKAGE_CONFIGS_DATA read: 1 (0%)
PACKAGE_ENTRIES.CONSTRAINT_INDEX_F read: 1 (0%)
PACKAGE_ENTRIES.PACKAGE_ENTRIES_DATA read: 1 (0%)
PACKAGE_ENTRIES.PRIMARY_KEY_F8 read: 1 (0%)
PACKAGE_MSI_TEMPLATES.CONSTRAINT_INDEX_B read: 1 (0%)
PACKAGE_MSI_TEMPLATES.PACKAGE_MSI_TEMPLATES_DATA read: 1 (0%)
PACKAGE_RIWS_ARCHIVES.CONSTRAINT_INDEX_71 read: 1 (0%)
PACKAGE_RIWS_ARCHIVES.PACKAGE_RIWS_ARCHIVES_DATA read: 1 (0%)
PAGE_INDEX.PAGE_INDEX_DATA read: 8 (0%)
PMS_PROPERTIES.PMS_PROPERTIES_DATA read: 1 (0%)
PMS_PROPERTIES.PRIMARY_KEY_F read: 1 (0%)
POLICY_RESTRICTIONS.CONSTRAINT_INDEX_61 read: 1 (0%)
POLICY_RESTRICTIONS.CONSTRAINT_INDEX_61E read: 1 (0%)
POLICY_RESTRICTIONS.POLICY_RESTRICTIONS_DATA read: 1 (0%)
POLICY_RESTRICTIONS.PRIMARY_KEY_61 read: 1 (0%)
POLICY_SETTINGS.CONSTRAINT_INDEX_37 read: 1 (0%)
POLICY_SETTINGS.CONSTRAINT_INDEX_378 read: 1 (0%)
POLICY_SETTINGS.POLICY_SETTINGS_DATA read: 1 (0%)
POLICY_SETTINGS.PRIMARY_KEY_37 read: 1 (0%)
POLICY_TABLE_ROWS.CONSTRAINT_INDEX_56 read: 1 (0%)
POLICY_TABLE_ROWS.CONSTRAINT_INDEX_566 read: 1 (0%)
POLICY_TABLE_ROWS.CONSTRAINT_INDEX_5667 read: 1 (0%)
POLICY_TABLE_ROWS.POLICY_TABLE_ROWS_DATA read: 1 (0%)
QUARANTINE.PRIMARY_KEY_4 read: 1 (0%)
QUARANTINE.QUARANTINE_DATA read: 1 (0%)
REPORTS.CONSTRAINT_INDEX_6 read: 1 (0%)
REPORTS.CONSTRAINT_INDEX_6C read: 1 (0%)
REPORTS.REPORTS_DATA read: 1 (0%)
REPORTS.REPORTS_DOMAIN_TIME_INDEX read: 1 (0%)
STATUS.CONSTRAINT_INDEX_9 read: 1 (0%)
STATUS.PRIMARY_KEY_9 read: 10278 (6%)
STATUS.STATUS_DATA read: 31919 (20%)
SYS.SYS_DATA read: 1 (0%)
TEMP_RESULT_SET_199.TEMP_RESULT_SET_199 read: 1 (0%)
TEMP_RESULT_SET_199.TEMP_RESULT_SET_199_DATA read: 1 (0%)
TEMP_RESULT_SET_201.TEMP_RESULT_SET_201 read: 8123 (5%)
TEMP_RESULT_SET_201.TEMP_RESULT_SET_201_DATA read: 19658 (12%)
TEMP_RESULT_SET_203.TEMP_RESULT_SET_203 read: 8126 (5%)
TEMP_RESULT_SET_203.TEMP_RESULT_SET_203_DATA read: 19658 (12%)
TEMP_RESULT_SET_205.TEMP_RESULT_SET_205 read: 16102 (10%)
TEMP_RESULT_SET_205.TEMP_RESULT_SET_205_DATA read: 37979 (24%)
USERS.CONSTRAINT_INDEX_4 read: 1 (0%)
USERS.USERS_DATA read: 1 (0%)
overflow read: 55 (0%)
*/
(1 row, 37141 ms)
Thanks in advance,
Konstantin Chernov
--
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.
