Skip to content
This repository has been archived by the owner on Apr 1, 2023. It is now read-only.

Unique Secondary index full scan defective #144

Open
jerryhfeng opened this issue Jan 19, 2021 · 0 comments
Open

Unique Secondary index full scan defective #144

jerryhfeng opened this issue Jan 19, 2021 · 0 comments
Assignees
@johnfangAFW
Labels
m1

Comments

@jerryhfeng
Copy link
Contributor

When we try to get a row with unique secondary index, the ybuniqueidxkeysuffix (as a key) binding value is not provided, thus the operation is changed into a full scan.

We should find out

  1. if we can provide ybuniqueidxkeysuffix in this case to avoid full scan. This could include bigger change of totally remove ybuniqueidxkeysuffix in the system if this make sense for chogori-sql.
  2. Even when we can't provide all key columns in a query, if we are providing a prefix key columns, we should not do the full scan, as in this case.

A sample trace of the issue.
where we are search a row on pg_namespace(2615)'s unique secondary index pg_namespace_nspname_index(2684)...

[0089:22:41:32.982.501]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_session.cc:419 @LoadTable]Loading table descriptor for {1, 2615}, id: 00000001000030008000000000000a37
[0089:22:41:32.982.509]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_session.cc:424 @LoadTable]Table cache MISS: {1, 2615}
[0089:22:41:32.982.516]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/catalog/sql_catalog_manager.cc:706 @GetTableSchema]Get table schema ns oid: 1, table oid: 2615, table id: 00000001000030008000000000000a37
[0089:22:41:32.982.522]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/catalog/sql_catalog_manager.cc:710 @GetTableSchema]Returned cached table schema name: pg_namespace, id: 00000001000030008000000000000a37 successfully
[0089:22:41:32.982.531]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_tabledesc.cc:82 @PgTableDesc]Table attr_num_map: [-2] = 0 for id: 0, name: oid
[0089:22:41:32.982.539]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_tabledesc.cc:82 @PgTableDesc]Table attr_num_map: [1] = 1 for id: 1, name: nspname
[0089:22:41:32.982.546]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_tabledesc.cc:82 @PgTableDesc]Table attr_num_map: [2] = 2 for id: 2, name: nspowner
[0089:22:41:32.982.553]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_tabledesc.cc:82 @PgTableDesc]Table attr_num_map: [3] = 3 for id: 3, name: nspacl
[0089:22:41:32.982.560]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_tabledesc.cc:88 @PgTableDesc]PgTableDesc table_id: 00000001000030008000000000000a37, ns_id: 00000001000030008000000000000000, schema_version: 0, hash_columns: 1, key_columns: 1, columns: 4, transactional: 1
[0089:22:41:32.982.569]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_select.cc:75 @prepare]new secondary_index_query_ created.
[0089:22:41:32.982.577]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_select.cc:101 @PrepareSecondaryIndex]secondary_index_query_ preparing as subquery.
[0089:22:41:32.982.586]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_select.cc:129 @PrepareSubquery]PgSelectIndex preparing as secondary index subquery.
[0089:22:41:32.982.595]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_session.cc:419 @LoadTable]Loading table descriptor for {1, 2684}, id: 00000001000030008000000000000a7c
[0089:22:41:32.982.605]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_session.cc:433 @LoadTable]Table cache HIT: {1, 2684}
[0089:22:41:32.982.616]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_tabledesc.cc:115 @PgTableDesc]Table attr_num_map: [1] = 0 for id: 0, name: nspname
[0089:22:41:32.982.626]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_tabledesc.cc:115 @PgTableDesc]Table attr_num_map: [-102] = 1 for id: 1, name: ybuniqueidxkeysuffix
[0089:22:41:32.982.631]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_tabledesc.cc:115 @PgTableDesc]Table attr_num_map: [-101] = 2 for id: 2, name: ybidxbasectid
[0089:22:41:32.982.636]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_tabledesc.cc:121 @PgTableDesc]PgTableDesc table_id: 00000001000030008000000000000a7c, ns_id: 00000001000030008000000000000000, schema_version: 0, hash_columns: 0, key_columns: 2, columns: 3, transactional: 1
[0089:22:41:32.982.644]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_column.cc:156 @AllocKeyBind]Allocating key binding SqlOpExpr for column name: nspname, order: 1 for read request
[0089:22:41:32.982.652]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_column.cc:156 @AllocKeyBind]Allocating key binding SqlOpExpr for column name: ybuniqueidxkeysuffix, order: -102 for read request
[0089:22:41:32.982.660]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:1004 @YBCPgFindTypeEntity]PgGateAPI: YBCPgFindTypeEntity 19
[0089:22:41:32.982.668]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:1009 @YBCPgGetType]PgGateAPI: YBCPgGetType
[0089:22:41:32.982.676]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:815 @YBCPgNewConstant]PgGateAPI: YBCPgNewConstant 105553128994360, 0
[0089:22:41:32.982.684]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:568 @YBCPgDmlBindColumn]PgGateAPI: YBCPgDmlBindColumn 1
[0089:22:41:32.982.692]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_dml.cc:157 @BindColumn]Bind column with attr_num: 1, name: nspname, value(PgExpr: Opcode: PG_EXPR_CONSTANT, type: 5)
[0089:22:41:32.982.699]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_dml.cc:440 @PrepareExpression]Finished binding PgExpr (PgExpr: Opcode: PG_EXPR_CONSTANT, type: 5) to (SqlOpExpr: type: VALUE, expr: {type: 4, isNull: 0, value: pg_catalog})
[0089:22:41:32.982.709]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:1004 @YBCPgFindTypeEntity]PgGateAPI: YBCPgFindTypeEntity 26
[0089:22:41:32.982.714]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:1009 @YBCPgGetType]PgGateAPI: YBCPgGetType
[0089:22:41:32.982.719]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:808 @YBCPgNewColumnRef]PgGateAPI: YBCPgNewColumnRef -2
[0089:22:41:32.982.727]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:539 @YBCPgDmlAppendTarget]PgGateAPI: YBCPgDmlAppendTarget
[0089:22:41:32.982.734]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_dml.cc:94 @AppendTargetVar]Append target (PgExpr: Opcode: PG_EXPR_COLREF, type: 102)
[0089:22:41:32.982.741]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_dml.cc:440 @PrepareExpression]Finished binding PgExpr (PgExpr: Opcode: PG_EXPR_COLREF, type: 102) to (SqlOpExpr: type: COLUMN_ID, expr: id: 0, name: oid)
[0089:22:41:32.982.753]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:1004 @YBCPgFindTypeEntity]PgGateAPI: YBCPgFindTypeEntity 19
[0089:22:41:32.982.762]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:1009 @YBCPgGetType]PgGateAPI: YBCPgGetType
[0089:22:41:32.982.770]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:808 @YBCPgNewColumnRef]PgGateAPI: YBCPgNewColumnRef 1
[0089:22:41:32.982.779]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:539 @YBCPgDmlAppendTarget]PgGateAPI: YBCPgDmlAppendTarget
[0089:22:41:32.982.786]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_dml.cc:94 @AppendTargetVar]Append target (PgExpr: Opcode: PG_EXPR_COLREF, type: 5)
[0089:22:41:32.982.795]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_dml.cc:440 @PrepareExpression]Finished binding PgExpr (PgExpr: Opcode: PG_EXPR_COLREF, type: 5) to (SqlOpExpr: type: COLUMN_ID, expr: id: 1, name: nspname)
[0089:22:41:32.982.806]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:1004 @YBCPgFindTypeEntity]PgGateAPI: YBCPgFindTypeEntity 26
[0089:22:41:32.982.816]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:1009 @YBCPgGetType]PgGateAPI: YBCPgGetType
[0089:22:41:32.982.821]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:808 @YBCPgNewColumnRef]PgGateAPI: YBCPgNewColumnRef 2
[0089:22:41:32.982.827]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:539 @YBCPgDmlAppendTarget]PgGateAPI: YBCPgDmlAppendTarget
[0089:22:41:32.982.832]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_dml.cc:94 @AppendTargetVar]Append target (PgExpr: Opcode: PG_EXPR_COLREF, type: 102)
[0089:22:41:32.982.838]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_dml.cc:440 @PrepareExpression]Finished binding PgExpr (PgExpr: Opcode: PG_EXPR_COLREF, type: 102) to (SqlOpExpr: type: COLUMN_ID, expr: id: 2, name: nspowner)
[0089:22:41:32.982.845]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:1004 @YBCPgFindTypeEntity]PgGateAPI: YBCPgFindTypeEntity 1034
[0089:22:41:32.982.851]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:1009 @YBCPgGetType]PgGateAPI: YBCPgGetType
[0089:22:41:32.982.858]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:808 @YBCPgNewColumnRef]PgGateAPI: YBCPgNewColumnRef 3
[0089:22:41:32.982.864]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:539 @YBCPgDmlAppendTarget]PgGateAPI: YBCPgDmlAppendTarget
[0089:22:41:32.982.871]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_dml.cc:94 @AppendTargetVar]Append target (PgExpr: Opcode: PG_EXPR_COLREF, type: 9)
[0089:22:41:32.982.878]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_dml.cc:440 @PrepareExpression]Finished binding PgExpr (PgExpr: Opcode: PG_EXPR_COLREF, type: 9) to (SqlOpExpr: type: COLUMN_ID, expr: id: 3, name: nspacl)
[0089:22:41:32.982.885]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:1004 @YBCPgFindTypeEntity]PgGateAPI: YBCPgFindTypeEntity 17
[0089:22:41:32.982.891]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:1009 @YBCPgGetType]PgGateAPI: YBCPgGetType
[0089:22:41:32.982.898]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:808 @YBCPgNewColumnRef]PgGateAPI: YBCPgNewColumnRef -8
[0089:22:41:32.982.904]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:539 @YBCPgDmlAppendTarget]PgGateAPI: YBCPgDmlAppendTarget
[0089:22:41:32.982.910]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_dml.cc:94 @AppendTargetVar]Append target (PgExpr: Opcode: PG_EXPR_COLREF, type: 9)
[0089:22:41:32.982.917]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_dml.cc:440 @PrepareExpression]Finished binding PgExpr (PgExpr: Opcode: PG_EXPR_COLREF, type: 9) to (SqlOpExpr: type: COLUMN_ID, expr: id: -8, name: ybctid)
[0089:22:41:32.982.928]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:1004 @YBCPgFindTypeEntity]PgGateAPI: YBCPgFindTypeEntity 17
[0089:22:41:32.982.937]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:1009 @YBCPgGetType]PgGateAPI: YBCPgGetType
[0089:22:41:32.982.943]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:808 @YBCPgNewColumnRef]PgGateAPI: YBCPgNewColumnRef -101
[0089:22:41:32.982.949]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:539 @YBCPgDmlAppendTarget]PgGateAPI: YBCPgDmlAppendTarget
[0089:22:41:32.982.958]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_dml.cc:94 @AppendTargetVar]Append target (PgExpr: Opcode: PG_EXPR_COLREF, type: 9)
[0089:22:41:32.982.970]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_dml.cc:440 @PrepareExpression]Finished binding PgExpr (PgExpr: Opcode: PG_EXPR_COLREF, type: 9) to (SqlOpExpr: type: COLUMN_ID, expr: id: 2, name: ybidxbasectid)
[0089:22:41:32.982.979]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:746 @YBCPgSetForwardScan]PgGateAPI: YBCPgSetForwardScan 1
[0089:22:41:32.982.986]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_gate_api.cc:751 @YBCPgExecSelect]PgGateAPI: YBCPgExecSelect
[0089:22:41:32.982.992]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_op.cc:689 @SetRequestTotalLimit]Set request limit as 0
[0089:22:41:32.983.002]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_dml_read.cc:126 @DeleteEmptyPrimaryBinds]Deleting empty primary binds and found missing primary key: 1
[0089:22:41:32.983.012]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_dml_read.cc:129 @DeleteEmptyPrimaryBinds]Full scan is needed
[0089:22:41:32.983.019]-k2_pg-(140061843561152) [DEBUG] [/build/src/k2/connector/yb/pggate/pg_dml.cc:316 @ProcessSecondaryIndexRequest]Processing secondary index request, has_sql_op: 1
@jerryhfeng jerryhfeng added the m0.1 Minimal sql txn label Jan 25, 2021
@iafuture iafuture added m1 and removed m0.1 Minimal sql txn labels Mar 3, 2021
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees

@johnfangAFW johnfangAFW

Labels
m1
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@iafuture @jerryhfeng @johnfangAFW