例如,如果我们不相信排序顺序扫描(sequential-scan-and-sort)是最好的办法,我们可以尝试这样的做法: SETenable_sort=off; EXPLAINSELECT*FROMtenk1 t1, onek t2WHEREt1.unique1<100ANDt1.unique2=t2.unique2; QUERY PLAN---MergeJoin(cost=0.56..292.65rows=10width=488)MergeCond: (t1.unique2=t2.unique2...
Sort Key: tr.role_id, tu.user_id Sort Method: quicksort Memory:25kB -> Hash Join (cost=2.41..3.80rows=9width=57) (actualtime=0.077..0.091rows=7loops=1) Hash Cond: ((tur.role_id)::text= (tr.role_id)::text) -> Hash Join (cost=1.24..2.48rows=9width=19) (actualtime=0.031.....
groups records together based on a GROUP BY or aggregate function (like sum()). 6、排序 - Sort order by 操作 sorts a record set based on the specified sort key. 7、数量限制 - limit returns a specified number of rows from a record set. 8、with as 临时表 - Common table expressions -...
eg:将testtab02上的索引删除,然后对testtab02排序(sort key:)后走merge join 二、与执行计划相关的配置项 1.ENABLE_*参数 一般不需要选择改变以下参数的默认值 2.COST基准值参数 执行计划在选择最优路径时,不同路径的cost值只有相对意义。 默认情况下,他们以顺序扫描一个数据块的开销作为基准单位,即:将顺序扫描...
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.427 rows=100 loops=1) Hash Cond: (t2.unique2 = t1.unique2) -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) (actual time=...
testdb=# EXPLAIN SELECT * FROM tbl_a WHERE id < 300 ORDER BY data; QUERY PLAN --- Sort (cost=182.34..183.09 rows=300 width=8) Sort Key: data -> Seq Scan on tbl_a (cost=0.00..170.00 rows=300 width=8) Filter: (id < 300) (...
testdb=# EXPLAIN SELECT * FROM tbl_a WHERE id < 300 ORDER BY data; QUERY PLAN --- Sort (cost=182.34..183.09 rows=300 width=8) Sort Key: data -> Seq Scan on tbl_a (cost=0.00..170.00 rows=300 width=8) Filter: (id < 300) (...
[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ] #from_item 可以是以下选项之一 [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ] ...
//原表有200行数据,limit100使用的是quicksortcentos=#explainanalyzeselect*fromtest1orderbyalimit100;QUERYPLAN---Limit(cost=10.64..10.89rows=100width=8)(actualtime=0.077..0.093rows=100loops=1)->Sort(cost=10.64..11.14rows=200width=8)(actualtime=0.076..0.083rows=100loops=1)SortKey:aSortMethod:q...
VALUES ( _expression_ [, ...] ) [, ...] [ ORDER BY _sort_expression_ [ ASC | DESC | USING _operator_ ] [, ...] ] [ LIMIT { _count_ | ALL } ] [ OFFSET _start_ [ ROW | ROWS ] ] [ FETCH { FIRST | NEXT } [ _count_ ] { ROW | ROWS } ONLY ]更多内容可以参考...