-
Notifications
You must be signed in to change notification settings - Fork 1.1k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
[YSQL] Index cost estimates should account for index and scan type #4494
Comments
m-iancu
added a commit
that referenced
this issue
Jun 1, 2020
Summary: Improve index cost estimates by considering the following when computing the costs: 1. Index uniqueness -- For instance fully-specified key guarantee single-row results for unique indexes, but not for non-unique indexes, so it now has lower costs for such cases. 2. Included columns (i.e. `Index Scan` vs `Index Only Scan`) -- Index scans will require an additional read from the main table to retrieve the needed columns (these are batched so costs are somewhat amortized). So index-only scans are preferred (have lower cost). 3. Scan direction -- in DocDB backwards (reverse) scans are (somewhat) slower than forward scans, so we prefer forward scans if all else is equal. 4. Partial indexes -- the partial index predicate will mean the index size (number of rows) will be less than the table (depending on how restrictive the predicate is). The cost estimate now account for the predicate filter in addition to the index condition filter. Additionally, disable merge joins for cases where they are not supported because the `ammarkpos` and `amrestrpos` are not yet implemented by the `ybc` access method. Choosing the unsupported index would have lead to a runtime error during execution (#4496). Example: For a pathological case with an inefficient (low-cardinality) index: ``` CREATE TABLE test(device_id int primary key, device_name text UNIQUE, supplier_id int); CREATE INDEX ON test(supplier_id); INSERT INTO test (SELECT generate_series, generate_series::text, 1 FROM generate_series(1, 100000)); EXPLAIN ANALYZE SELECT * FROM test WHERE device_name = '1' AND supplier_id = 1; ``` ``` QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Index Scan using test_device_name_key on test (cost=0.00..4.13 rows=1 width=40) (actual time=1.271..1.273 rows=1 loops=1) Index Cond: (device_name = '1'::text) Filter: (supplier_id = 1) Planning Time: 0.072 ms Execution Time: 1.302 ms (5 rows) ``` Query now takes `1.302 ms` (down from `~850ms`). Test Plan: TestPgRegressPlanner (yb_planner_indexes, yb_planner_joins) Reviewers: neil, alex, neha Reviewed By: neil Subscribers: dmitry, yql Differential Revision: https://phabricator.dev.yugabyte.com/D8445
Fixed by 6a0dd26. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Among other things, when evaluating the per-row and index cost we should consider:
Index Scan
vsIndex Only Scan
). Index scans will require an additional read from the main table to retrieve the needed columns (these are batched so costs are somewhat amortized).Example:
Query takes
~850ms
, even though it could be executed as a single-key read if using the right index (ondevice_name
, notsupplier_id
).The text was updated successfully, but these errors were encountered: