binarycat0 commented on PR #2465:
URL: https://github.com/apache/polaris/pull/2465#issuecomment-3244547998
# Test ENV
OS: `24.5.0 Darwin Kernel Version 24.5.0: Tue Apr 22 19:53:27 PDT 2025;
root:xnu-11417.121.6~2/RELEASE_ARM64_T6041 arm64`
Storage: Docker image `postgres:17`
Rows in total = 899040.
Expected rows result = 200005.
# Results
## Main build version
```
EXPLAIN ANALYZE
(SELECT id, catalog_id, parent_id, type_code, name, entity_version,
sub_type_code, create_timestamp, drop_timestamp, purge_timestamp,
to_purge_timestamp, last_update_timestamp, properties, internal_properties,
grant_records_version, location_without_scheme
FROM POLARIS_SCHEMA.ENTITIES
WHERE catalog_id = 6911759911282783572 AND sub_type_code = 0 AND realm_id =
'POLARIS' AND parent_id = 6911759911282783572 AND type_code = 6);
```
```
Bitmap Heap Scan on entities (cost=3960.68..22260.05 rows=44861 width=140)
(actual time=16.287..43.851 rows=200005 loops=1)
Recheck Cond: ((realm_id = 'POLARIS'::text) AND (catalog_id =
'6911759911282783572'::bigint) AND (parent_id = '6911759911282783572'::bigint)
AND (type_code = 6))
Filter: (sub_type_code = 0)
Heap Blocks: exact=3848
-> Bitmap Index Scan on constraint_name (cost=0.00..3949.47 rows=44861
width=0) (actual time=15.792..15.792 rows=200005 loops=1)
Index Cond: ((realm_id = 'POLARIS'::text) AND (catalog_id =
'6911759911282783572'::bigint) AND (parent_id = '6911759911282783572'::bigint)
AND (type_code = 6))
Planning Time: 0.239 ms
Execution Time: 49.253 ms
```
## New build version
```
CREATE INDEX IF NOT EXISTS idx_entities_lookup
ON entities USING btree (realm_id, catalog_id, parent_id, type_code,
sub_type_code)
INCLUDE (id, name);
```
```
EXPLAIN ANALYZE
(SELECT id, catalog_id, parent_id, type_code, name, sub_type_code
FROM POLARIS_SCHEMA.ENTITIES
WHERE catalog_id = 6911759911282783572 AND sub_type_code = 0 AND realm_id =
'POLARIS' AND parent_id = 6911759911282783572 AND type_code = 6);
```
```
Index Only Scan using idx_entities_lookup on entities (cost=0.42..2975.89
rows=42744 width=50) (actual time=0.030..29.235 rows=200005 loops=1)
Index Cond: ((realm_id = 'POLARIS'::text) AND (catalog_id =
'6911759911282783572'::bigint) AND (parent_id = '6911759911282783572'::bigint)
AND (type_code = 6) AND (sub_type_code = 0))
Heap Fetches: 0
Planning Time: 0.089 ms
Execution Time: 35.317 ms
```
# Conclusion
Using `Index Only Scan` instead of `Bitmap Heap Scan` helps:
- reduce query execution time up to 28%;
- reducing query cost up to 87%;
cc: @dimas-b @singhpk234
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
