Access paths are a query planning structure that correspond 1:1 to iterators, in that an access path contains pretty much exactly the information needed to instantiate given iterator, plus some information that is only needed during planning, such as costs.
More...
|
| enum AccessPath::Type | type |
| |
| Safety | safe_for_rowid = SAFE |
| | Whether it is safe to get row IDs (for sorting) from this access path. More...
|
| |
| bool | count_examined_rows: 1 |
| | Whether this access path counts as one that scans a base table, and thus should be counted towards examined_rows. More...
|
| |
| bool | forced_by_dbug: 1 |
| | Whether this access path is forced preferred over all others by means of a SET DEBUG force_subplan_0x... statement. More...
|
| |
| int8_t | immediate_update_delete_table {-1} |
| | For UPDATE and DELETE statements: The node index of a table which can be updated or deleted from immediately as the rows are read from the iterator, if this path is only read from once. More...
|
| |
| int | ordering_state = 0 |
| | Which ordering the rows produced by this path follow, if any (see interesting_orders.h). More...
|
| |
| RowIterator * | iterator = nullptr |
| | If an iterator has been instantiated for this access path, points to the iterator. More...
|
| |
| double | cost {-1.0} |
| | Expected cost to read all of this access path once; -1.0 for unknown. More...
|
| |
| double | init_cost {-1.0} |
| | Expected cost to initialize this access path; ie., cost to read k out of N rows would be init_cost + (k/N) * (cost - init_cost). More...
|
| |
| double | init_once_cost {0.0} |
| | Of init_cost, how much of the initialization needs only to be done once per query block. More...
|
| |
| double | num_output_rows_before_filter {kUnknownRowCount} |
| | If no filter, identical to num_output_rows, cost, respectively. More...
|
| |
| double | cost_before_filter {-1.0} |
| |
| OverflowBitset | filter_predicates {0} |
| | Bitmap of WHERE predicates that we are including on this access path, referring to the “predicates” array internal to the join optimizer. More...
|
| |
| OverflowBitset | delayed_predicates {0} |
| | Bitmap of WHERE predicates that touch tables we have joined in, but that we could not apply yet (for instance because they reference other tables, or because because we could not push them down into the nullable side of outer joins). More...
|
| |
| hypergraph::NodeMap | parameter_tables {0} |
| | If nonzero, a bitmap of other tables whose joined-in rows must already be loaded when rows from this access path are evaluated; that is, this access path must be put on the inner side of a nested-loop join (or multiple such joins) where the outer side includes all of the given tables. More...
|
| |
| void * | secondary_engine_data {nullptr} |
| | Auxiliary data used by a secondary storage engine while processing the access path during optimization and execution. More...
|
| |
| TABLE * | table |
| |
| struct { |
| TABLE * table |
| |
| } | table_scan |
| |
| int | idx |
| |
| bool | use_order |
| |
| bool | reverse |
| |
| struct { |
| TABLE * table |
| |
| int idx |
| |
| bool use_order |
| |
| bool reverse |
| |
| } | index_scan |
| |
| Index_lookup * | ref |
| |
| struct { |
| TABLE * table |
| |
| Index_lookup * ref |
| |
| bool use_order |
| |
| bool reverse |
| |
| } | ref |
| |
| struct { |
| TABLE * table |
| |
| Index_lookup * ref |
| |
| bool use_order |
| |
| } | ref_or_null |
| |
| struct { |
| TABLE * table |
| |
| Index_lookup * ref |
| |
| } | eq_ref |
| |
| bool | is_unique |
| |
| struct { |
| TABLE * table |
| |
| Index_lookup * ref |
| |
| bool use_order |
| |
| bool is_unique |
| |
| } | pushed_join_ref |
| |
| bool | use_limit |
| |
| Item_func_match * | ft_func |
| |
| struct { |
| TABLE * table |
| |
| Index_lookup * ref |
| |
| bool use_order |
| |
| bool use_limit |
| |
| Item_func_match * ft_func |
| |
| } | full_text_search |
| |
| struct { |
| TABLE * table |
| |
| Index_lookup * ref |
| |
| } | const_table |
| |
| AccessPath * | bka_path |
| |
| int | mrr_flags |
| |
| bool | keep_current_rowid |
| |
| struct { |
| TABLE * table |
| |
| Index_lookup * ref |
| |
| AccessPath * bka_path |
| |
| int mrr_flags |
| |
| bool keep_current_rowid |
| |
| } | mrr |
| |
| struct { |
| TABLE * table |
| |
| } | follow_tail |
| |
| KEY_PART * | used_key_part |
| |
| QUICK_RANGE ** | ranges |
| |
| unsigned | num_ranges |
| |
| unsigned | mrr_flags |
| |
| unsigned | mrr_buf_size |
| |
| unsigned | index |
| |
| unsigned | num_used_key_parts |
| |
| bool | can_be_used_for_ror: 1 |
| |
| bool | need_rows_in_rowid_order: 1 |
| |
| bool | can_be_used_for_imerge: 1 |
| |
| bool | reuse_handler: 1 |
| |
| bool | geometry: 1 |
| |
| bool | using_extended_key_parts: 1 |
| |
| struct { |
| KEY_PART * used_key_part |
| |
| QUICK_RANGE ** ranges |
| |
| unsigned num_ranges |
| |
| unsigned mrr_flags |
| |
| unsigned mrr_buf_size |
| |
| unsigned index |
| |
| unsigned num_used_key_parts |
| |
| bool can_be_used_for_ror: 1 |
| |
| bool need_rows_in_rowid_order: 1 |
| |
| bool can_be_used_for_imerge: 1 |
| |
| bool reuse_handler: 1 |
| |
| bool geometry: 1 |
| |
| bool reverse: 1 |
| |
| bool using_extended_key_parts: 1 |
| |
| } | index_range_scan |
| |
| bool | forced_by_hint |
| |
| bool | allow_clustered_primary_key_scan |
| |
| Mem_root_array< AccessPath * > * | children |
| |
| struct { |
| TABLE * table |
| |
| bool forced_by_hint |
| |
| bool allow_clustered_primary_key_scan |
| |
| Mem_root_array< AccessPath * > * children |
| |
| } | index_merge |
| |
| AccessPath * | cpk_child |
| |
| bool | retrieve_full_rows |
| |
| bool | is_covering |
| |
| struct { |
| TABLE * table |
| |
| Mem_root_array< AccessPath * > * children |
| |
| AccessPath * cpk_child |
| |
| bool forced_by_hint |
| |
| bool retrieve_full_rows |
| |
| bool need_rows_in_rowid_order |
| |
| bool reuse_handler |
| |
| bool is_covering |
| |
| } | rowid_intersection |
| |
| struct { |
| TABLE * table |
| |
| Mem_root_array< AccessPath * > * children |
| |
| bool forced_by_hint |
| |
| } | rowid_union |
| |
| IndexSkipScanParameters * | param |
| |
| struct { |
| TABLE * table |
| |
| unsigned index |
| |
| unsigned num_used_key_parts |
| |
| bool forced_by_hint |
| |
| IndexSkipScanParameters * param |
| |
| } | index_skip_scan |
| |
| GroupIndexSkipScanParameters * | param |
| |
| struct { |
| TABLE * table |
| |
| unsigned index |
| |
| unsigned num_used_key_parts |
| |
| bool forced_by_hint |
| |
| GroupIndexSkipScanParameters * param |
| |
| } | group_index_skip_scan |
| |
| QEP_TAB * | qep_tab |
| |
| struct { |
| TABLE * table |
| |
| QEP_TAB * qep_tab |
| |
| } | dynamic_index_range_scan |
| |
| Table_function * | table_function |
| |
| AccessPath * | table_path |
| |
| struct { |
| TABLE * table |
| |
| Table_function * table_function |
| |
| AccessPath * table_path |
| |
| } | materialized_table_function |
| |
| struct { |
| } | unqualified_count |
| |
| struct { |
| } | table_value_constructor |
| |
| struct { |
| } | fake_single_row |
| |
| AccessPath * | child |
| |
| const char * | cause |
| |
| struct { |
| AccessPath * child |
| |
| const char * cause |
| |
| } | zero_rows |
| |
| struct { |
| const char * cause |
| |
| } | zero_rows_aggregated |
| |
| AccessPath * | outer |
| |
| AccessPath * | inner |
| |
| const JoinPredicate * | join_predicate |
| |
| bool | allow_spill_to_disk |
| |
| bool | store_rowids |
| |
| bool | rewrite_semi_to_inner |
| |
| table_map | tables_to_get_rowid_for |
| |
| struct { |
| AccessPath * outer |
| |
| AccessPath * inner |
| |
| const JoinPredicate * join_predicate |
| |
| bool allow_spill_to_disk |
| |
| bool store_rowids |
| |
| bool rewrite_semi_to_inner |
| |
| table_map tables_to_get_rowid_for |
| |
| } | hash_join |
| |
| JoinType | join_type |
| |
| unsigned | mrr_length_per_rec |
| |
| float | rec_per_key |
| |
| struct { |
| AccessPath * outer |
| |
| AccessPath * inner |
| |
| JoinType join_type |
| |
| unsigned mrr_length_per_rec |
| |
| float rec_per_key |
| |
| bool store_rowids |
| |
| table_map tables_to_get_rowid_for |
| |
| } | bka_join |
| |
| bool | pfs_batch_mode |
| |
| bool | already_expanded_predicates |
| |
| OverflowBitset | equijoin_predicates |
| |
| struct { |
| AccessPath * outer |
| |
| AccessPath * inner |
| |
| JoinType join_type |
| |
| bool pfs_batch_mode |
| |
| bool already_expanded_predicates |
| |
| const JoinPredicate * join_predicate |
| |
| OverflowBitset equijoin_predicates |
| |
| } | nested_loop_join |
| |
| const TABLE * | table |
| |
| KEY * | key |
| |
| size_t | key_len |
| |
| struct { |
| AccessPath * outer |
| |
| AccessPath * inner |
| |
| const TABLE * table |
| |
| KEY * key |
| |
| size_t key_len |
| |
| } | nested_loop_semijoin_with_duplicate_removal |
| |
| Item * | condition |
| |
| bool | materialize_subqueries |
| |
| struct { |
| AccessPath * child |
| |
| Item * condition |
| |
| bool materialize_subqueries |
| |
| } | filter |
| |
| Filesort * | filesort |
| |
| ORDER * | order |
| |
| ha_rows | limit |
| |
| bool | remove_duplicates |
| |
| bool | unwrap_rollup |
| |
| bool | force_sort_rowids |
| |
| struct { |
| AccessPath * child |
| |
| Filesort * filesort |
| |
| table_map tables_to_get_rowid_for |
| |
| ORDER * order |
| |
| ha_rows limit |
| |
| bool remove_duplicates |
| |
| bool unwrap_rollup |
| |
| bool force_sort_rowids |
| |
| } | sort |
| |
| bool | rollup |
| |
| struct { |
| AccessPath * child |
| |
| bool rollup |
| |
| } | aggregate |
| |
| AccessPath * | subquery_path |
| |
| Temp_table_param * | temp_table_param |
| |
| int | ref_slice |
| |
| struct { |
| AccessPath * subquery_path |
| |
| Temp_table_param * temp_table_param |
| |
| TABLE * table |
| |
| AccessPath * table_path |
| |
| int ref_slice |
| |
| } | temptable_aggregate |
| |
| ha_rows | offset |
| |
| bool | count_all_rows |
| |
| bool | reject_multiple_rows |
| |
| ha_rows * | send_records_override |
| |
| struct { |
| AccessPath * child |
| |
| ha_rows limit |
| |
| ha_rows offset |
| |
| bool count_all_rows |
| |
| bool reject_multiple_rows |
| |
| ha_rows * send_records_override |
| |
| } | limit_offset |
| |
| JOIN * | join |
| |
| bool | provide_rowid |
| |
| struct { |
| AccessPath * child |
| |
| JOIN * join |
| |
| Temp_table_param * temp_table_param |
| |
| TABLE * table |
| |
| bool provide_rowid |
| |
| int ref_slice |
| |
| } | stream |
| |
| MaterializePathParameters * | param |
| |
| double | subquery_cost |
| | The total cost of executing the queries that we materialize. More...
|
| |
| struct { |
| AccessPath * table_path |
| |
| MaterializePathParameters * param |
| |
| double subquery_cost |
| | The total cost of executing the queries that we materialize. More...
|
| |
| } | materialize |
| |
| Table_ref * | table_list |
| |
| struct { |
| AccessPath * table_path |
| |
| Table_ref * table_list |
| |
| Item * condition |
| |
| } | materialize_information_schema_table |
| |
| Mem_root_array< AppendPathParameters > * | children |
| |
| struct { |
| Mem_root_array< AppendPathParameters > * children |
| |
| } | append |
| |
| Window * | window |
| |
| TABLE * | temp_table |
| |
| bool | needs_buffering |
| |
| struct { |
| AccessPath * child |
| |
| Window * window |
| |
| TABLE * temp_table |
| |
| Temp_table_param * temp_table_param |
| |
| int ref_slice |
| |
| bool needs_buffering |
| |
| } | window |
| |
| SJ_TMP_TABLE * | weedout_table |
| |
| struct { |
| AccessPath * child |
| |
| SJ_TMP_TABLE * weedout_table |
| |
| table_map tables_to_get_rowid_for |
| |
| } | weedout |
| |
| Item ** | group_items |
| |
| int | group_items_size |
| |
| struct { |
| AccessPath * child |
| |
| Item ** group_items |
| |
| int group_items_size |
| |
| } | remove_duplicates |
| |
| unsigned | loosescan_key_len |
| |
| struct { |
| AccessPath * child |
| |
| TABLE * table |
| |
| KEY * key |
| |
| unsigned loosescan_key_len |
| |
| } | remove_duplicates_on_index |
| |
| AccessPath * | table_scan_path |
| |
| Index_lookup * | used_ref |
| |
| struct { |
| AccessPath * table_scan_path |
| |
| AccessPath * child |
| |
| Index_lookup * used_ref |
| |
| } | alternative |
| |
| const char * | name |
| |
| struct { |
| AccessPath * child |
| |
| const char * name |
| |
| } | cache_invalidator |
| |
| table_map | tables_to_delete_from |
| |
| table_map | immediate_tables |
| |
| struct { |
| AccessPath * child |
| |
| table_map tables_to_delete_from |
| |
| table_map immediate_tables |
| |
| } | delete_rows |
| |
| table_map | tables_to_update |
| |
| struct { |
| AccessPath * child |
| |
| table_map tables_to_update |
| |
| table_map immediate_tables |
| |
| } | update_rows |
| |
Access paths are a query planning structure that correspond 1:1 to iterators, in that an access path contains pretty much exactly the information needed to instantiate given iterator, plus some information that is only needed during planning, such as costs.
(The new join optimizer will extend this somewhat in the future. Some iterators also need the query block, ie., JOIN object, they are part of, but that is implicitly available when constructing the tree.)
AccessPath objects build on a variant, ie., they can hold an access path of any type (table scan, filter, hash join, sort, etc.), although only one at the same time. Currently, they contain 32 bytes of base information that is common to any access path (type identifier, costs, etc.), and then up to 40 bytes that is type-specific (e.g. for a table scan, the TABLE object). It would be nice if we could squeeze it down to 64 and fit a cache line exactly, but it does not seem to be easy without fairly large contortions.
We could have solved this by inheritance, but the fixed-size design makes it possible to replace an access path when a better one is found, without introducing a new allocation, which will be important when using them as a planning structure.
Bitmap of sargable join predicates that have already been applied in this access path by means of an index lookup (ref access), again referring to “predicates”, and thus should not be counted again for selectivity.
Note that the filter may need to be applied nevertheless (especially in case of type conversions); see subsumed_sargable_join_predicates.
Since these refer to the same array as filter_predicates, they will never overlap with filter_predicates, and so we can reuse the same memory using an alias (a union would not be allowed, since OverflowBitset is a class with non-trivial default constructor), even though the meaning is entirely separate. If N = num_where_predicates in the hypergraph, then bits 0..(N-1) belong to filter_predicates, and the rest to applied_sargable_join_predicates.
Bitmap of WHERE predicates that we are including on this access path, referring to the “predicates” array internal to the join optimizer.
Since bit masks are much cheaper to deal with than creating Item objects, and we don't invent new conditions during join optimization (all of them are known when we begin optimization), we stick to manipulating bit masks during optimization, saying which filters will be applied at this node (a 1-bit means the filter will be applied here; if there are multiple ones, they are ANDed together).
This is used during join optimization only; before iterators are created, we will add FILTER access paths to represent these instead, removing the dependency on the array. Said FILTER paths are by convention created with materialize_subqueries = false, since the by far most common case is that there are no subqueries in the predicate. In other words, if you wish to represent a filter with materialize_subqueries = true, you will need to make an explicit FILTER node.
See also nested_loop_join().equijoin_predicates, which is for filters being applied before nested-loop joins, but is otherwise the same idea.
| int8_t AccessPath::immediate_update_delete_table {-1} |
For UPDATE and DELETE statements: The node index of a table which can be updated or deleted from immediately as the rows are read from the iterator, if this path is only read from once.
-1 if there is no such table in this path.
Note that this is an index into CostingReceiver's array of nodes, and is not necessarily equal to the table number within the query block given by Table_ref::tableno().
The table, if any, is currently always the outermost table in the path.
It is possible to have plans where it would be safe to operate "immediately" on more than one table. For example, if we do a merge join, it is safe to perform immediate deletes on tables on the inner side of the join, since both sides are read only once. (However, we currently do not support merge joins.)
Another possibility is when the outer table of a nested loop join is guaranteed to return at most one row (typically, a unique index lookup aka. eq_ref). Then it's safe to delete immediately from both sides of the nested loop join. But we don't to this yet.
Hash joins read both sides exactly once, However, with hash joins, the scans on the inner tables are not positioned on the correct row when the result of the join is returned, so the immediate delete logic will need to be changed to reposition the underlying scans before doing the immediate deletes. While this can be done, it makes the benefit of immediate deletes less obvious for these tables, and it can also be a loss in some cases, because we lose the deduplication provided by the Unique object used for buffered deletes (the immediate deletes could end up spending time repositioning to already deleted rows). So we currently don't attempt to do immediate deletes from inner tables of hash joins either.
The outer table of a hash join can be deleted from immediately if the inner table fits in memory. If the hash join spills to disk, though, neither the rows of the outer table nor the rows of the inner table come out in the order of the underlying scan, so it is not safe in general to perform immediate deletes on the outer table of a hash join.
If support for immediate operations on multiple tables is added, this member could be changed from a node index to a NodeMap.
| double AccessPath::init_once_cost {0.0} |
Of init_cost, how much of the initialization needs only to be done once per query block.
(This is a cost, not a proportion.) Ie., if the access path can reuse some its initialization work if Init() is called multiple times, this member will be nonzero. A typical example is a materialized table with rematerialize=false; the second time Init() is called, it's a no-op. Most paths will have init_once_cost = 0.0, ie., repeated scans will cost the same. We do not intend to use this field to model cache effects.
This is currently not printed in EXPLAIN, only optimizer trace.
If nonzero, a bitmap of other tables whose joined-in rows must already be loaded when rows from this access path are evaluated; that is, this access path must be put on the inner side of a nested-loop join (or multiple such joins) where the outer side includes all of the given tables.
The most obvious case for this is dependent tables in LATERAL, but a more common case is when we have pushed join conditions referring to those tables; e.g., if this access path represents t1 and we have a condition t1.x=t2.x that is pushed down into an index lookup (ref access), t2 will be set in this bitmap. We can still join in other tables, deferring t2, but the bit(s) will then propagate, and we cannot be on the right side of a hash join until parameter_tables is zero again. (Also see DisallowParameterizedJoinPath() for when we disallow such deferring, as an optimization.)
As a special case, we allow setting RAND_TABLE_BIT, even though it is normally part of a table_map, not a NodeMap. In this case, it specifies that the access path is entirely noncachable, because it depends on something nondeterministic or an outer reference, and thus can never be on the right side of a hash join, ever.
1.9.2