表结构说明
`MAX_ROWS_SENT` bigint(21)unsigned NOT NULL DEFAULT '0',`SUM_ROWS_EXAMINED` bigint(21)unsigned NOT NULL DEFAULT '0',`MIN_ROWS_EXAMINED` bigint(21)unsigned NOT NULL DEFAULT '0',`MAX_ROWS_EXAMINED` bigint(21)unsigned NOT ...
执行计划
segments:2)(cost=0.00.20.88 rows=1 width=13)Rows out:1 rows at destination with 0.305 ms to first row,0.537 ms to end,start offset by 0.289 ms.->Seq Scan on names(cost=0.00.20.88 rows=1 width=13)Rows out:Avg 1 rows x 2 ...
EXPLAIN输出
QUERY PLAN-Sort(cost=717.34.717.59 rows=101 width=488)(actual time=7.761.7.774 rows=100 loops=1)Sort Key:t1.fivethous Sort Method:quicksort Memory:77kB->Hash Join(cost=230.47.713.98 rows=101 width=488)(actual time=0.711.7....
SQL Detail
`Error_code` int(11)DEFAULT NULL,`Rows_sent` bigint(20)DEFAULT NULL,`Rows_examined` bigint(20)DEFAULT NULL,`Rows_affected` bigint(20)DEFAULT NULL,`Logical_read` bigint(20)DEFAULT NULL,`Phy_sync_read` bigint(20)DEFAULT NULL...
RDS PostgreSQL慢SQL问题
QUERY PLAN-Gather(cost=1000.00.97331.31 rows=1 width=4)(actual time=0.217.302.316 rows=1 loops=1)Workers Planned:2 Workers Launched:2->Parallel Seq Scan on t1(cost=0.00.96331.21 rows=1 width=4)(actual time=191.208.289.240 ...
B-TREE索引
QUERY PLAN-Bitmap Heap Scan on test(cost=12.17.571.91 rows=500 width=10)Recheck Cond:(lower((name):text)='a1':text)->Bitmap Index Scan on test_lower_idx(cost=0.00.12.04 rows=500 width=0)Index Cond:(lower((name):text)='a1':...
使用全局临时表
语法 CREATE GLOBAL TEMPORARY|TEMP TABLE table-name { column-definition[,column-definition]*}[ON COMMIT {DELETE|PRESERVE} ROWS]支持ON COMMIT DELETE ROWS子句，即当前事务提交后清空临时表中的数据。支持ON COMMIT PRESERVE ROWS...
使用全局临时表
语法 CREATE GLOBAL TEMPORARY|TEMP TABLE table-name { column-definition[,column-definition]*}[ON COMMIT {DELETE|PRESERVE} ROWS]支持ON COMMIT DELETE ROWS子句，即当前事务提交后清空临时表中的数据。支持ON COMMIT PRESERVE ROWS...
默认优化模式
optimizer_mode-first_rows_10(1 row)以下示例显示SELECT命令中使用的优化模式作为提示：SELECT/*+FIRST_ROWS(7)*/*FROM emp;empno|ename|job|mgr|hiredate|sal|comm|deptno-+-+-+-+-+-+-+-7369|SMITH|CLERK|7902|17-DEC-80 00:00:00|800....
Semi-Join的并行加速
nodes:2)(cost=6187.68 rows=100928)->Nested loop inner join(cost=1052.43 rows=1577)->Filter:(nation.N_NAME='KENYA')(cost=2.29 rows=3)->Table scan on nation(cost=2.29 rows=25)->Parallel index lookup on supplier using ...
默认优化模式
optimizer_mode-first_rows_10(1 row)以下示例显示SELECT命令中使用的优化模式作为提示：SELECT/*+FIRST_ROWS(7)*/*FROM emp;empno|ename|job|mgr|hiredate|sal|comm|deptno-+-+-+-+-+-+-+-7369|SMITH|CLERK|7902|17-DEC-80 00:00:00|800....
SHOW BINLOG EVENTS
rows|1|912|table_id:258 flags:STMT_END_F|binlog.000001|912|Table_map|1|973|table_id:367(transfer_test.accounts)|binlog.000001|973|Write_rows|1|1017|table_id:367 flags:STMT_END_F|binlog.000001|1017|Table_map|1|1078|table_id...
Hash Join的并行加速
workers:4)(cost=10.82 rows=4)->Parallel inner hash join(t2.c2=t1.c1)(cost=0.57 rows=1)->Parallel table scan on t2,with parallel partitions:1(cost=0.03 rows=1)->Parallel hash->Parallel table scan on t1,with parallel ...
全局索引
QUERY PLAN-Global Partitioned Index Scan(1/3)using m_peaktemp_idx_global on measurement(cost=0.50.8.52 rows=1 width=20)Index Cond:(peaktemp=5)(2 rows)Global Index Only Scan explain select city_id from measurement where ...
MySQL 8.0 Server层最新架构详解
Table scan on R3(cost=0.35 rows=1)->Hash->Inner hash join(no condition)(cost=0.70 rows=1)->Table scan on R2(cost=0.35 rows=1)->Hash->Table scan on R1(cost=0.35 rows=1)|->Nested loop inner join(cost=0.55.0.55 rows=0)->...
使用批量更新
segments:4)(cost=0.00.581.02 rows=50 width=22)Hash Key:public.target_table.c1->Assert(cost=0.00.581.01 rows=50 width=22)Assert Cond:NOT public.target_table.c1 IS NULL->Split(cost=0.00.581.01 rows=50 width=22)->Nested Loop...
分区剪枝
QUERY PLAN-Append(cost=0.01.136.35 rows=2268 width=20)(actual time=0.067.0.068 rows=0 loops=1)InitPlan 1(returns$0)->Result(cost=0.00.0.01 rows=1 width=8)(actual time=0.051.0.053 rows=1 loops=1)->Seq Scan on measurement_y...
Parallelism Hints
QUERY PLAN-Gather(cost=1000.00.241061.04 rows=2014215 width=97)Workers Planned:4->Parallel Seq Scan on pgbench_accounts(cost=0.00.38639.54 rows=503554 width=97)(3 rows)现在，为 PARALLEL hint的并行度参数指定的值为 6。...
Parallelism Hints
QUERY PLAN-Gather(cost=1000.00.241061.04 rows=2014215 width=97)Workers Planned:4->Parallel Seq Scan on pgbench_accounts(cost=0.00.38639.54 rows=503554 width=97)(3 rows)现在，为 PARALLEL hint的并行度参数指定的值为 6。...
分区剪枝
QUERY PLAN-Append(cost=0.01.136.35 rows=2268 width=20)(actual time=0.067.0.068 rows=0 loops=1)InitPlan 1(returns$0)->Result(cost=0.00.0.01 rows=1 width=8)(actual time=0.051.0.053 rows=1 loops=1)->Seq Scan on measurement_y...
分区剪枝
QUERY PLAN-Append(cost=0.01.136.35 rows=2268 width=20)(actual time=0.067.0.068 rows=0 loops=1)InitPlan 1(returns$0)->Result(cost=0.00.0.01 rows=1 width=8)(actual time=0.051.0.053 rows=1 loops=1)->Seq Scan on measurement_y...
定制执行计划（pg_hint_plan）
返回如下结果：QUERY PLAN-Hash Join(cost=2.86.11406.38 rows=101 width=4)Hash Cond:(c1.a=c2.a)->Gather(cost=0.00.7652.13 rows=1000101 width=4)Workers Planned:3->Parallel Seq Scan on c1(cost=0.00.7652.13 rows=322613 width=4)-...
使用Aviator函数作为过滤器的最佳实践
Aviator函数：#首先获取到JSON中的最内层数组rows，接下来对数组进行遍历，当遇到某条记录为"city":"BJ"时,将"tags":""改为"tags":"数据服务"rows=$0.data.rows;for row in rows { if(row.city="BJ"){ row.tags="数据服务;} } return$0;...
pg_trgm
segments:3)(cost=0.00.259.63 rows=422 width=32)(actual time=137.606.179.674 rows=431 loops=1)->Seq Scan on test_trgm(cost=0.00.254.00 rows=141 width=32)(actual time=0.961.136.977 rows=146 loops=1)Filter:(t~'%abcd%':text)...
基于Go的应用开发
nil { fmt.Println("query data error",err)return } defer rows.Close()var id int var name string var age int for rows.Next(){ err=rows.Scan(&id,&name,&age)if err!nil { fmt.Println("scan data error",err)return } fmt.Println(...
并行INSERT
segments:6)(cost=0.00.432.04 rows=100000 width=8)->Partial Seq Scan on t1(cost=0.00.431.37 rows=16667 width=4)Optimizer:PolarDB PX Optimizer(5 rows)其中的 PX Hash 6:3 表示6个并行查询 t1 的进程通过Motion算子将数据传递给3个...
窗口函数
用例 结果 id,c1,sum(id)over(order by id)id|c1|sum-+-+-1|a|2 1|a|2 3|b|5 4|c|9(4 rows)id,c1,sum(id)over()id|c1|sum-+-+-1|a|9 1|a|9 3|b|9 4|c|9(4 rows)可以看出，当使用排序子句时，求和值为第一行（最小值）到当前行的和，并且...
PutTimeseriesData
required TimeseriesRows rows_data=2;optional MetaUpdateMode meta_update_mode=3;} 名称 类型 是否必选 描述 table_name string 是 时序表名。rows_data TimeseriesRows 是 多行时序数据。meta_update_mode MetaUpdateMode 否 元数据...
集合返回函数
array|subscript|value-+-+-{-1,-2}|1|-1 {-1,-2}|2|-2 {100,200,300}|1|100 {100,200,300}|2|200 {100,200,300}|3|300(5 rows)-unnest a 2D array:CREATE OR REPLACE FUNCTION unnest2(anyarray)RETURNS SETOF anyelement AS$select$1[i]...
并行INSERT
segments:6)(cost=0.00.432.04 rows=100000 width=8)->Partial Seq Scan on t1(cost=0.00.431.37 rows=16667 width=4)Optimizer:PolarDB PX Optimizer(5 rows)其中的 PX Hash 6:3 表示6个并行查询 t1 的进程通过Motion算子将数据传递给3个...
公用表表达式
segments:3)(cost=0.00.3085.25 rows=86100 width=8)->Subquery Scan on x1(cost=0.00.1937.25 rows=28700 width=8)Filter:(x1.a>1)->Shared Scan(share slice:id 1:0)(cost=321.00.355.50 rows=28700 width=8)->Seq Scan on t1(cost=0.00....
使用EXPLAIN阅读查询计划
segments:2)(cost=0.00.20.88 rows=1 width=13)Rows out:1 rows at destination with 0.305 ms to first row,0.537 ms to end,start offset by 0.289 ms.->Seq Scan on names(cost=0.00.20.88 rows=1 width=13)Rows out:Avg 1 rows x 2 ...
pg_hint_plan
返回结果如下：QUERY PLAN-Hash Join(cost=2.86.11406.38 rows=101 width=4)Hash Cond:(c1.a=c2.a)->Gather(cost=0.00.7652.13 rows=1000101 width=4)Workers Planned:3->Parallel Seq Scan on c1(cost=0.00.7652.13 rows=322613 width=4)-...
Explain和Explain Analyze
返回结果 QUERY PLAN Sort(cost=0.00.7795.30 rows=3 width=80)Sort Key:l_returnflag,l_linestatus->Gather(cost=0.00.7795.27 rows=3 width=80)->Project(cost=0.00.7795.27 rows=3 width=80)->Project(cost=0.00.7794.27 rows=3 width=...
pg_hint_plan
返回结果如下：QUERY PLAN-Hash Join(cost=2.86.11406.38 rows=101 width=4)Hash Cond:(c1.a=c2.a)->Gather(cost=0.00.7652.13 rows=1000101 width=4)Workers Planned:3->Parallel Seq Scan on c1(cost=0.00.7652.13 rows=322613 width=4)-...
查询和响应ECS系统事件
EventTypeName rows=rows=InstanceSystemEventSet.InstanceSystemEventType[]查询已完结（Executed、Avoided、Canceled、Failed）状态的系统事件：aliyun ecs DescribeInstanceHistoryEvents-RegionId<TheRegionId>-InstanceId...
示例
QUERY PLAN-Update on t1_hash(cost=0.00.92.18 rows=24 width=14)Update on t1_hash_p1 Update on t1_hash_p2 t1_hash->Append(cost=0.00.92.18 rows=24 width=14)Subplans Removed:1->Seq Scan on t1_hash_p1(cost=0.00.46.03 rows=12 ...
BatchWriteRow
每个TableInBatchWriteRowRequest中put_rows、update_rows、delete_rows包含的RowInBatchWriteRowResponse对象的顺序分别与TableInBatchWriteRowRequest中put_rows、update_rows、delete_rows包含的PutRowInBatchWriteRowRequest、...
关联子查询上拉
QUERY PLAN-Seq Scan on t1(actual time=67.631.1641827.119 rows=100000 loops=1)Filter:(SubPlan 1)SubPlan 1->Result(actual time=0.005.13.124 rows=50000 loops=100000)One-Time Filter:(t1.b=1)->Seq Scan on t2(actual time=0.005.7...
Access Method Hints
QUERY PLAN-Gather(cost=1000.00.45094.80 rows=1 width=97)Workers Planned:2->Parallel Seq Scan on pgbench_accounts(cost=0.00.44094.70 rows=1 width=97)Filter:(aid=100)(4 rows)除了如之前示例中所示使用 EXPLAIN 命令以外，还可...
- 产品推荐
- 这些文档可能帮助您