binarycat0 commented on PR #2465:
URL: https://github.com/apache/polaris/pull/2465#issuecomment-3242450416
## Test ENV
Rows in total = 899040.
Expected rows result = 200005.
```
CREATE INDEX IF NOT EXISTS idx_entities_lookup ON entities (realm_id,
catalog_id, parent_id, type_code, sub_type_code);
```
## Explain query
```
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);
```
## Results
### With Index
```
Bitmap Heap Scan on entities (cost=956.71..19256.08 rows=44861 width=50)
(actual time=7.143..32.498 rows=200005 loops=1)
Recheck 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 Blocks: exact=3848
-> Bitmap Index Scan on idx_entities_lookup (cost=0.00..945.49
rows=44861 width=0) (actual time=6.378..6.378 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))
Planning Time: 0.111 ms
Execution Time: 37.933 ms
```
### With no Index
```
Bitmap Heap Scan on entities (cost=3960.68..22260.05 rows=44861 width=50)
(actual time=16.567..45.052 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=16.043..16.043 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.150 ms
Execution Time: 51.119 ms
```
## Conclusion
Index helps reduce query cost to 75% .. 13% and reduce query execution time
up to 25%;
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: issues-unsubscr...@polaris.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
