Summary:
Add estimated roundtrip for index and sequential scans to EXPLAIN.
Examples with index-only, index, and sequential scans:
yugabyte=# \d+ rand_dist
Table "public.rand_dist"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------+---------------+-----------+----------+---------+----------+-------------+--------------+-------------
pk | integer | | not null | | plain | | |
norm_col | character(15) | | not null | | extended | | |
exp_col | integer | | not null | | plain | | |
uni_col | integer | | | | plain | | |
uni_corr_col | character(10) | | | | extended | | |
step_col | integer | | not null | | plain | | |
Indexes:
"rand_dist_pkey" PRIMARY KEY, lsm (pk HASH)
"exp_uni" lsm (exp_col HASH, uni_col ASC)
"norm_exp" lsm (norm_col HASH, exp_col ASC)
"norm_uni" lsm (norm_col HASH, uni_col ASC)
"step_exp" lsm (step_col HASH, exp_col ASC)
Access method: heap
yugabyte=# explain (dist on, analyze on, debug on) select count(*) from rand_dist where norm_col='00000000000933' and uni_col < 50;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=24.36..24.37 rows=1 width=8) (actual time=1.879..1.880 rows=1 loops=1)
-> Index Only Scan using norm_uni on rand_dist (cost=20.02..24.36 rows=1 width=0) (actual time=1.872..1.873 rows=0 loops=1)
Index Cond: ((norm_col = '00000000000933'::bpchar) AND (uni_col < 50))
Heap Fetches: 0
Storage Index Read Requests: 1
Storage Index Read Execution Time: 1.598 ms
Metric rocksdb_number_db_seek: 1.000
Metric rocksdb_number_db_seek_found: 1.000
Metric rocksdb_iter_bytes_read: 56.000
Metric ql_read_latency: sum: 387.000, count: 1.000
Estimated Seeks: 2
Estimated Nexts: 3
Estimated Table Roundtrips: 0
Estimated Index Roundtrips: 1
Estimated Docdb Result Width: 30
Partial Aggregate: true
Planning Time: 24.238 ms
Execution Time: 2.042 ms
Storage Read Requests: 1
Storage Read Execution Time: 1.598 ms
Storage Rows Scanned: 0
Storage Write Requests: 0
Catalog Read Requests: 6
Catalog Read Execution Time: 20.065 ms
Catalog Write Requests: 0
Storage Flush Requests: 0
Metric rocksdb_number_db_seek: 1
Metric rocksdb_number_db_seek_found: 1
Metric rocksdb_iter_bytes_read: 56
Metric ql_read_latency: sum: 387, count: 1
Storage Execution Time: 21.664 ms
Peak Memory Usage: 25 kB
(32 rows)
yugabyte=# /*+ indexscan(rand_dist) */ explain (dist on, analyze on, debug on) select count(*) from rand_dist where norm_col='00000000000933' and uni_col < 50;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=68.73..68.74 rows=1 width=8) (actual time=2.857..2.857 rows=1 loops=1)
-> Index Scan using norm_uni on rand_dist (cost=40.04..68.73 rows=1 width=0) (actual time=2.848..2.848 rows=0 loops=1)
Index Cond: ((norm_col = '00000000000933'::bpchar) AND (uni_col < 50))
Storage Index Read Requests: 1
Storage Index Read Execution Time: 2.235 ms
Metric rocksdb_number_db_seek: 1.000
Metric rocksdb_number_db_seek_found: 1.000
Metric rocksdb_iter_bytes_read: 56.000
Metric ql_read_latency: sum: 499.000, count: 1.000
Estimated Seeks: 4
Estimated Nexts: 6
Estimated Table Roundtrips: 1
Estimated Index Roundtrips: 1
Estimated Docdb Result Width: 30
Partial Aggregate: true
Planning Time: 0.524 ms
Execution Time: 3.155 ms
Storage Read Requests: 1
Storage Read Execution Time: 2.235 ms
Storage Rows Scanned: 0
Storage Write Requests: 0
Catalog Read Requests: 0
Catalog Write Requests: 0
Storage Flush Requests: 0
Metric rocksdb_number_db_seek: 1
Metric rocksdb_number_db_seek_found: 1
Metric rocksdb_iter_bytes_read: 56
Metric ql_read_latency: sum: 499, count: 1
Storage Execution Time: 2.235 ms
Peak Memory Usage: 25 kB
(30 rows)
yugabyte=# /*+ seqscan(rand_dist) */ explain (dist on, analyze on, debug on) select count(*) from rand_dist where norm_col='00000000000933' and uni_col < 50;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=7184.05..7184.06 rows=1 width=8) (actual time=1290.385..1290.386 rows=1 loops=1)
-> Seq Scan on rand_dist (cost=20.00..7184.05 rows=1 width=0) (actual time=1290.378..1290.378 rows=0 loops=1)
Storage Filter: ((uni_col < 50) AND (norm_col = '00000000000933'::bpchar))
Storage Table Read Requests: 1
Storage Table Read Execution Time: 1289.778 ms
Storage Table Rows Scanned: 100000
Metric rocksdb_number_db_seek: 2.000
Metric rocksdb_number_db_next: 200000.000
Metric rocksdb_number_db_seek_found: 2.000
Metric rocksdb_number_db_next_found: 199998.000
Metric rocksdb_iter_bytes_read: 10698356.000
Metric docdb_keys_found: 100000.000
Metric ql_read_latency: sum: 2563198.000, count: 2.000
Estimated Seeks: 1
Estimated Nexts: 99999
Estimated Table Roundtrips: 1
Estimated Index Roundtrips: 0
Estimated Docdb Result Width: 9
Partial Aggregate: true
Planning Time: 0.778 ms
Execution Time: 1290.532 ms
Storage Read Requests: 1
Storage Read Execution Time: 1289.778 ms
Storage Rows Scanned: 100000
Storage Write Requests: 0
Catalog Read Requests: 0
Catalog Write Requests: 0
Storage Flush Requests: 0
Metric rocksdb_number_db_seek: 2
Metric rocksdb_number_db_next: 200000
Metric rocksdb_number_db_seek_found: 2
Metric rocksdb_number_db_next_found: 199998
Metric rocksdb_iter_bytes_read: 10698356
Metric docdb_keys_found: 100000
Metric ql_read_latency: sum: 2563198, count: 2
Storage Execution Time: 1289.778 ms
Peak Memory Usage: 24 kB
(37 rows)
Jira: DB-14194
Test Plan: TestPgCostModelSeekNextEstimation
Reviewers: gkukreja, kramanathan
Reviewed By: gkukreja, kramanathan
Subscribers: smishra, svc_phabricator, yql, gkukreja
Differential Revision: https://phorge.dev.yugabyte.com/D40230
