|
1
|
No measurable difference with or without the indexes for “explain analyze”
|
|
2
|
|
|
3
|
|
|
4
|
With 10,000 links
|
|
5
|
|
|
6
|
Without index:
|
|
7
|
|
|
8
|
arvados_dev=> explain analyze select * from links where head_uuid like '_____-j7d0g-%';
|
|
9
|
QUERY PLAN
|
|
10
|
------------------------------------------------------------------------------------------------------------
|
|
11
|
Seq Scan on links (cost=0.00..240.40 rows=10005 width=234) (actual time=0.016..30.470 rows=10014 loops=1)
|
|
12
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
13
|
Total runtime: 69.508 ms
|
|
14
|
(3 rows)
|
|
15
|
|
|
16
|
arvados_dev=> explain analyze select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
17
|
QUERY PLAN
|
|
18
|
------------------------------------------------------------------------------------------------------------
|
|
19
|
Seq Scan on links (cost=0.00..240.40 rows=10014 width=234) (actual time=0.011..29.645 rows=10014 loops=1)
|
|
20
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
21
|
Total runtime: 54.383 ms
|
|
22
|
(3 rows)
|
|
23
|
|
|
24
|
|
|
25
|
With index:
|
|
26
|
|
|
27
|
arvados_dev=> explain analyze select * from links where head_uuid like '_____-j7d0g-%';
|
|
28
|
QUERY PLAN
|
|
29
|
------------------------------------------------------------------------------------------------------------
|
|
30
|
Seq Scan on links (cost=0.00..240.40 rows=10005 width=234) (actual time=0.012..29.185 rows=10014 loops=1)
|
|
31
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
32
|
Total runtime: 54.080 ms
|
|
33
|
(3 rows)
|
|
34
|
|
|
35
|
arvados_dev=> explain analyze select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
36
|
QUERY PLAN
|
|
37
|
------------------------------------------------------------------------------------------------------------
|
|
38
|
Seq Scan on links (cost=0.00..240.40 rows=10014 width=234) (actual time=0.017..29.035 rows=10014 loops=1)
|
|
39
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
40
|
Total runtime: 54.272 ms
|
|
41
|
(3 rows)
|
|
42
|
|
|
43
|
|
|
44
|
===========
|
|
45
|
|
|
46
|
With 20,000 links
|
|
47
|
|
|
48
|
Without index
|
|
49
|
|
|
50
|
arvados_dev=> explain analyze select * from links where head_uuid like '_____-j7d0g-%';
|
|
51
|
QUERY PLAN
|
|
52
|
------------------------------------------------------------------------------------------------------------
|
|
53
|
Seq Scan on links (cost=0.00..475.43 rows=19686 width=234) (actual time=0.012..64.738 rows=20014 loops=1)
|
|
54
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
55
|
Total runtime: 125.166 ms
|
|
56
|
(3 rows)
|
|
57
|
|
|
58
|
arvados_dev=> explain analyze select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
59
|
QUERY PLAN
|
|
60
|
------------------------------------------------------------------------------------------------------------
|
|
61
|
Seq Scan on links (cost=0.00..475.43 rows=19695 width=234) (actual time=0.017..63.461 rows=20014 loops=1)
|
|
62
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
63
|
Total runtime: 117.584 ms
|
|
64
|
(3 rows)
|
|
65
|
|
|
66
|
|
|
67
|
With index:
|
|
68
|
|
|
69
|
arvados_dev=> explain analyze select * from links where head_uuid like '_____-j7d0g-%';
|
|
70
|
QUERY PLAN
|
|
71
|
------------------------------------------------------------------------------------------------------------
|
|
72
|
Seq Scan on links (cost=0.00..479.40 rows=20003 width=234) (actual time=0.019..64.330 rows=20014 loops=1)
|
|
73
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
74
|
Total runtime: 135.665 ms
|
|
75
|
(3 rows)
|
|
76
|
|
|
77
|
arvados_dev=> explain analyze select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
78
|
QUERY PLAN
|
|
79
|
------------------------------------------------------------------------------------------------------------
|
|
80
|
Seq Scan on links (cost=0.00..479.40 rows=20013 width=234) (actual time=0.011..63.831 rows=20014 loops=1)
|
|
81
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
82
|
Total runtime: 122.056 ms
|
|
83
|
(3 rows)
|
|
84
|
|
|
85
|
|
|
86
|
============
|
|
87
|
|
|
88
|
With 100,000 rows:
|
|
89
|
|
|
90
|
Without index:
|
|
91
|
|
|
92
|
---------------------------------------------------------------------------------------------------------------
|
|
93
|
Seq Scan on links (cost=0.00..2380.82 rows=99396 width=230) (actual time=0.016..315.595 rows=100014 loops=1)
|
|
94
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
95
|
Total runtime: 580.910 ms
|
|
96
|
(3 rows)
|
|
97
|
|
|
98
|
arvados_dev=> explain analyze select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
99
|
QUERY PLAN
|
|
100
|
---------------------------------------------------------------------------------------------------------------
|
|
101
|
Seq Scan on links (cost=0.00..2380.82 rows=99396 width=230) (actual time=0.014..314.564 rows=100014 loops=1)
|
|
102
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
103
|
Total runtime: 581.566 ms
|
|
104
|
(3 rows)
|
|
105
|
|
|
106
|
With index:
|
|
107
|
|
|
108
|
arvados_dev=> explain analyze select * from links where head_uuid like '_____-j7d0g-%';
|
|
109
|
QUERY PLAN
|
|
110
|
----------------------------------------------------------------------------------------------------------------
|
|
111
|
Seq Scan on links (cost=0.00..2388.40 rows=100002 width=230) (actual time=0.016..320.711 rows=100014 loops=1)
|
|
112
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
113
|
Total runtime: 620.608 ms
|
|
114
|
(3 rows)
|
|
115
|
|
|
116
|
arvados_dev=> explain analyze select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
117
|
QUERY PLAN
|
|
118
|
----------------------------------------------------------------------------------------------------------------
|
|
119
|
Seq Scan on links (cost=0.00..2388.40 rows=100002 width=230) (actual time=0.014..317.493 rows=100014 loops=1)
|
|
120
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
121
|
Total runtime: 592.811 ms
|
|
122
|
(3 rows)
|