Skip to content

Commit 8755d9a

Browse files
RaduBerindeRaduBerinde
committed
opt: add a per-span cost to scan
This change adds a per-span "random IO" cost for scans. This is more consistent with how lookup joins are costed. This fixes an issue where we prefer hash join instead of lookup join even for a single left row when the right table is small, improving a handful of TPCE queries. The changes to TPCH Q2 and Q8 seem mildly beneficial from my testing. Fixes #41701. Release justification: low risk, high benefit change to existing functionality. Release note (performance improvement): Joins between very small tables are more likely to use lookup join.
1 parent 18f2183 commit 8755d9a

51 files changed

Lines changed: 1421 additions & 1545 deletions

File tree

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

‎pkg/sql/logictest/testdata/logic_test/prepare‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1171,14 +1171,14 @@ select
11711171
├── columns: k:1 str:2
11721172
├── immutable
11731173
├── stats: [rows=333.333333]
1174-
├── cost: 1050.04
1174+
├── cost: 1054.04
11751175
├── key: (1)
11761176
├── fd: (1)-->(2)
11771177
├── prune: (2)
11781178
├── scan t2
11791179
│ ├── columns: k:1 str:2
11801180
│ ├── stats: [rows=1000]
1181-
│ ├── cost: 1040.02
1181+
│ ├── cost: 1044.02
11821182
│ ├── key: (1)
11831183
│ ├── fd: (1)-->(2)
11841184
│ └── prune: (1,2)

‎pkg/sql/logictest/testdata/logic_test/tpch_vec‎

Lines changed: 2 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -567,8 +567,7 @@ EXPLAIN (VEC) SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_
567567
│ └ *colexec.selEQBytesBytesConstOp
568568
│ └ *colfetcher.ColBatchScan
569569
└ *rowexec.joinReader
570-
└ *colexec.mergeJoinInnerOp
571-
├ *colfetcher.ColBatchScan
570+
└ *rowexec.joinReader
572571
└ *colexec.selEQBytesBytesConstOp
573572
└ *colfetcher.ColBatchScan
574573

@@ -691,8 +690,7 @@ EXPLAIN (VEC) SELECT o_year, sum(CASE WHEN nation = 'BRAZIL' THEN volume ELSE 0
691690
│ │ ├ *colfetcher.ColBatchScan
692691
│ │ └ *colexec.hashJoiner
693692
│ │ ├ *rowexec.joinReader
694-
│ │ │ └ *colexec.mergeJoinInnerOp
695-
│ │ │ ├ *colfetcher.ColBatchScan
693+
│ │ │ └ *rowexec.joinReader
696694
│ │ │ └ *colexec.selEQBytesBytesConstOp
697695
│ │ │ └ *colfetcher.ColBatchScan
698696
│ │ └ *rowexec.joinReader

‎pkg/sql/opt/exec/execbuilder/testdata/distsql_agg‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -333,15 +333,15 @@ group-by
333333
├── grouping columns: b:2
334334
├── internal-ordering: +2 opt(1)
335335
├── stats: [rows=9.5617925, distinct(2)=9.5617925, null(2)=0]
336-
├── cost: 11.1256179
336+
├── cost: 15.1256179
337337
├── key: (2)
338338
├── fd: (2)-->(5)
339339
├── prune: (5)
340340
├── scan data2
341341
│ ├── columns: a:1 b:2
342342
│ ├── constraint: /1/2: [/1 - /1]
343343
│ ├── stats: [rows=10, distinct(1)=1, null(1)=0, distinct(2)=9.5617925, null(2)=0]
344-
│ ├── cost: 10.81
344+
│ ├── cost: 14.81
345345
│ ├── key: (2)
346346
│ ├── fd: ()-->(1)
347347
│ ├── ordering: +2 opt(1) [actual: +2]

‎pkg/sql/opt/exec/execbuilder/testdata/explain‎

Lines changed: 17 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -852,23 +852,23 @@ EXPLAIN (OPT,VERBOSE) SELECT * FROM tc WHERE a = 10 ORDER BY b
852852
sort
853853
├── columns: a:1 b:2
854854
├── stats: [rows=10, distinct(1)=1, null(1)=0]
855-
├── cost: 52.7943856
855+
├── cost: 56.7943856
856856
├── fd: ()-->(1)
857857
├── ordering: +2 opt(1) [actual: +2]
858858
├── prune: (2)
859859
├── interesting orderings: (+1)
860860
└── index-join tc
861861
├── columns: a:1 b:2
862862
├── stats: [rows=10, distinct(1)=1, null(1)=0]
863-
├── cost: 51.92
863+
├── cost: 55.92
864864
├── fd: ()-->(1)
865865
├── prune: (2)
866866
├── interesting orderings: (+1)
867867
└── scan tc@c
868868
├── columns: a:1 rowid:3
869869
├── constraint: /1/3: [/10 - /10]
870870
├── stats: [rows=10, distinct(1)=1, null(1)=0]
871-
├── cost: 10.81
871+
├── cost: 14.81
872872
├── key: (3)
873873
└── fd: ()-->(1)
874874

@@ -878,23 +878,23 @@ EXPLAIN (OPT,TYPES) SELECT * FROM tc WHERE a = 10 ORDER BY b
878878
sort
879879
├── columns: a:1(int!null) b:2(int)
880880
├── stats: [rows=10, distinct(1)=1, null(1)=0]
881-
├── cost: 52.7943856
881+
├── cost: 56.7943856
882882
├── fd: ()-->(1)
883883
├── ordering: +2 opt(1) [actual: +2]
884884
├── prune: (2)
885885
├── interesting orderings: (+1)
886886
└── index-join tc
887887
├── columns: a:1(int!null) b:2(int)
888888
├── stats: [rows=10, distinct(1)=1, null(1)=0]
889-
├── cost: 51.92
889+
├── cost: 55.92
890890
├── fd: ()-->(1)
891891
├── prune: (2)
892892
├── interesting orderings: (+1)
893893
└── scan tc@c
894894
├── columns: a:1(int!null) rowid:3(int!null)
895895
├── constraint: /1/3: [/10 - /10]
896896
├── stats: [rows=10, distinct(1)=1, null(1)=0]
897-
├── cost: 10.81
897+
├── cost: 14.81
898898
├── key: (3)
899899
└── fd: ()-->(1)
900900

@@ -942,20 +942,20 @@ inner-join (hash)
942942
├── columns: a:1 b:2 k:6 v:7
943943
├── multiplicity: left-rows(zero-or-one), right-rows(zero-or-more)
944944
├── stats: [rows=990, distinct(1)=99, null(1)=0, distinct(6)=99, null(6)=0]
945-
├── cost: 2219.96
945+
├── cost: 2227.96
946946
├── fd: (6)-->(7), (1)==(6), (6)==(1)
947947
├── prune: (2,7)
948948
├── scan tc
949949
│ ├── columns: a:1 b:2
950950
│ ├── stats: [rows=1000, distinct(1)=100, null(1)=10]
951-
│ ├── cost: 1100.02
951+
│ ├── cost: 1104.02
952952
│ ├── prune: (1,2)
953953
│ ├── interesting orderings: (+1)
954954
│ └── unfiltered-cols: (1-5)
955955
├── scan t
956956
│ ├── columns: k:6 v:7
957957
│ ├── stats: [rows=1000, distinct(6)=1000, null(6)=0]
958-
│ ├── cost: 1080.02
958+
│ ├── cost: 1084.02
959959
│ ├── key: (6)
960960
│ ├── fd: (6)-->(7)
961961
│ ├── prune: (6,7)
@@ -983,7 +983,7 @@ sort
983983
├── columns: a:1 b:2 [hidden: column6:6]
984984
├── immutable
985985
├── stats: [rows=333.333333]
986-
├── cost: 1179.26548
986+
├── cost: 1183.26548
987987
├── fd: (1,2)-->(6)
988988
├── ordering: +6
989989
├── prune: (1,2,6)
@@ -992,20 +992,20 @@ sort
992992
├── columns: column6:6 a:1 b:2
993993
├── immutable
994994
├── stats: [rows=333.333333]
995-
├── cost: 1116.71667
995+
├── cost: 1120.71667
996996
├── fd: (1,2)-->(6)
997997
├── prune: (1,2,6)
998998
├── interesting orderings: (+1)
999999
├── select
10001000
│ ├── columns: a:1 b:2
10011001
│ ├── immutable
10021002
│ ├── stats: [rows=333.333333]
1003-
│ ├── cost: 1110.04
1003+
│ ├── cost: 1114.04
10041004
│ ├── interesting orderings: (+1)
10051005
│ ├── scan tc
10061006
│ │ ├── columns: a:1 b:2
10071007
│ │ ├── stats: [rows=1000]
1008-
│ │ ├── cost: 1100.02
1008+
│ │ ├── cost: 1104.02
10091009
│ │ ├── prune: (1,2)
10101010
│ │ └── interesting orderings: (+1)
10111011
│ └── filters
@@ -1020,7 +1020,7 @@ sort
10201020
├── columns: a:1(int) b:2(int) [hidden: column6:6(int)]
10211021
├── immutable
10221022
├── stats: [rows=333.333333]
1023-
├── cost: 1179.26548
1023+
├── cost: 1183.26548
10241024
├── fd: (1,2)-->(6)
10251025
├── ordering: +6
10261026
├── prune: (1,2,6)
@@ -1029,20 +1029,20 @@ sort
10291029
├── columns: column6:6(int) a:1(int) b:2(int)
10301030
├── immutable
10311031
├── stats: [rows=333.333333]
1032-
├── cost: 1116.71667
1032+
├── cost: 1120.71667
10331033
├── fd: (1,2)-->(6)
10341034
├── prune: (1,2,6)
10351035
├── interesting orderings: (+1)
10361036
├── select
10371037
│ ├── columns: a:1(int) b:2(int)
10381038
│ ├── immutable
10391039
│ ├── stats: [rows=333.333333]
1040-
│ ├── cost: 1110.04
1040+
│ ├── cost: 1114.04
10411041
│ ├── interesting orderings: (+1)
10421042
│ ├── scan tc
10431043
│ │ ├── columns: a:1(int) b:2(int)
10441044
│ │ ├── stats: [rows=1000]
1045-
│ │ ├── cost: 1100.02
1045+
│ │ ├── cost: 1104.02
10461046
│ │ ├── prune: (1,2)
10471047
│ │ └── interesting orderings: (+1)
10481048
│ └── filters

‎pkg/sql/opt/exec/execbuilder/testdata/inverted_index‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -678,7 +678,7 @@ inner-join (lookup geo_table)
678678
├── lookup columns are key
679679
├── immutable
680680
├── stats: [rows=10000]
681-
├── cost: 112480.05
681+
├── cost: 112484.05
682682
├── key: (1,5)
683683
├── fd: (1)-->(2), (5)-->(6)
684684
├── prune: (1,5)
@@ -687,13 +687,13 @@ inner-join (lookup geo_table)
687687
│ ├── inverted-expr
688688
│ │ └── st_intersects(geo_table2.geom:2, geo_table.geom:6)
689689
│ ├── stats: [rows=10000, distinct(1)=999.956829, null(1)=0, distinct(5)=999.956829, null(5)=0]
690-
│ ├── cost: 41780.03
690+
│ ├── cost: 41784.03
691691
│ ├── key: (1,5)
692692
│ ├── fd: (1)-->(2)
693693
│ ├── scan geo_table2
694694
│ │ ├── columns: geo_table2.k:1 geo_table2.geom:2
695695
│ │ ├── stats: [rows=1000, distinct(1)=1000, null(1)=0]
696-
│ │ ├── cost: 1080.02
696+
│ │ ├── cost: 1084.02
697697
│ │ ├── key: (1)
698698
│ │ ├── fd: (1)-->(2)
699699
│ │ ├── prune: (1,2)

‎pkg/sql/opt/exec/execbuilder/testdata/stats‎

Lines changed: 14 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -204,13 +204,13 @@ distinct-on
204204
├── grouping columns: u:1 v:2
205205
├── internal-ordering: +1
206206
├── stats: [rows=20.0617284, distinct(1,2)=20.0617284, null(1,2)=0]
207-
├── cost: 37.7306173
207+
├── cost: 41.7306173
208208
├── key: (1,2)
209209
└── scan uv@uv_u_idx
210210
├── columns: u:1 v:2
211211
├── constraint: /1/3: (/NULL - /29]
212212
├── stats: [rows=33.3333333, distinct(1)=6.66666667, null(1)=0, distinct(1,2)=20.0617284, null(1,2)=0]
213-
├── cost: 36.6766667
213+
├── cost: 40.6766667
214214
├── ordering: +1
215215
├── prune: (2)
216216
└── interesting orderings: (+1) (+2)
@@ -226,13 +226,13 @@ distinct-on
226226
├── grouping columns: u:1 v:2
227227
├── internal-ordering: +1
228228
├── stats: [rows=33.3333333, distinct(1,2)=33.3333333, null(1,2)=0]
229-
├── cost: 37.8633333
229+
├── cost: 41.8633333
230230
├── key: (1,2)
231231
└── scan uv@uv_u_idx
232232
├── columns: u:1 v:2
233233
├── constraint: /1/3: (/NULL - /29]
234234
├── stats: [rows=33.3333333, distinct(1)=6.66666667, null(1)=0, distinct(1,2)=33.3333333, null(1,2)=0]
235-
├── cost: 36.6766667
235+
├── cost: 40.6766667
236236
├── ordering: +1
237237
├── prune: (2)
238238
└── interesting orderings: (+1) (+2)
@@ -248,15 +248,15 @@ distinct-on
248248
├── grouping columns: u:1 v:2
249249
├── internal-ordering: +1
250250
├── stats: [rows=100, distinct(1,2)=100, null(1,2)=0]
251-
├── cost: 113.53
251+
├── cost: 117.53
252252
├── key: (1,2)
253253
└── scan uv@uv_u_idx
254254
├── columns: u:1 v:2
255255
├── constraint: /1/3: (/NULL - /29]
256256
├── stats: [rows=100, distinct(1)=20, null(1)=0, distinct(1,2)=100, null(1,2)=0]
257257
│ histogram(1)= 0 50 0 20 8 5 12 5
258258
│ <--- 1 --- 2 --- 10 ---- 20
259-
├── cost: 110.01
259+
├── cost: 114.01
260260
├── ordering: +1
261261
├── prune: (2)
262262
└── interesting orderings: (+1) (+2)
@@ -272,15 +272,15 @@ distinct-on
272272
├── grouping columns: u:1 v:2
273273
├── internal-ordering: +1
274274
├── stats: [rows=25, distinct(1,2)=25, null(1,2)=0]
275-
├── cost: 112.78
275+
├── cost: 116.78
276276
├── key: (1,2)
277277
└── scan uv@uv_u_idx
278278
├── columns: u:1 v:2
279279
├── constraint: /1/3: (/NULL - /29]
280280
├── stats: [rows=100, distinct(1)=20, null(1)=0, distinct(1,2)=25, null(1,2)=0]
281281
│ histogram(1)= 0 50 0 20 8 5 12 5
282282
│ <--- 1 --- 2 --- 10 ---- 20
283-
├── cost: 110.01
283+
├── cost: 114.01
284284
├── ordering: +1
285285
├── prune: (2)
286286
└── interesting orderings: (+1) (+2)
@@ -301,20 +301,20 @@ limit
301301
├── cardinality: [0 - 1]
302302
├── immutable
303303
├── stats: [rows=1]
304-
├── cost: 222.06
304+
├── cost: 226.06
305305
├── key: ()
306306
├── fd: ()-->(1)
307307
├── select
308308
│ ├── columns: j:1
309309
│ ├── immutable
310310
│ ├── stats: [rows=10, distinct(1)=1, null(1)=10]
311-
│ ├── cost: 222.04
311+
│ ├── cost: 226.04
312312
│ ├── fd: ()-->(1)
313313
│ ├── limit hint: 1.00
314314
│ ├── scan tj
315315
│ │ ├── columns: j:1
316316
│ │ ├── stats: [rows=1000, distinct(1)=100, null(1)=10]
317-
│ │ ├── cost: 212.02
317+
│ │ ├── cost: 216.02
318318
│ │ ├── limit hint: 100.00
319319
│ │ └── prune: (1)
320320
│ └── filters
@@ -332,20 +332,20 @@ limit
332332
├── cardinality: [0 - 1]
333333
├── immutable
334334
├── stats: [rows=1]
335-
├── cost: 5.41
335+
├── cost: 9.41
336336
├── key: ()
337337
├── fd: ()-->(1)
338338
├── select
339339
│ ├── columns: j:1
340340
│ ├── immutable
341341
│ ├── stats: [rows=1, distinct(1)=1, null(1)=1]
342-
│ ├── cost: 5.39
342+
│ ├── cost: 9.39
343343
│ ├── fd: ()-->(1)
344344
│ ├── limit hint: 1.00
345345
│ ├── scan tj
346346
│ │ ├── columns: j:1
347347
│ │ ├── stats: [rows=5, distinct(1)=4, null(1)=1]
348-
│ │ ├── cost: 5.32
348+
│ │ ├── cost: 9.32
349349
│ │ ├── limit hint: 5.00
350350
│ │ └── prune: (1)
351351
│ └── filters

0 commit comments

Comments
 (0)