|
With 1,000 rows:
|
|
|
|
Without index:
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like '_____-j7d0g-%';
|
|
QUERY PLAN
|
|
--------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..33.51 rows=3 width=224)
|
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
QUERY PLAN
|
|
---------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..33.51 rows=14 width=224)
|
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
With index
|
|
|
|
arvados_dev=> CREATE INDEX links_varchar_index_on_head_uuid ON links (head_uuid varchar_pattern_ops);
|
|
CREATE INDEX
|
|
arvados_dev=> explain select * from links where head_uuid like '_____-j7d0g-%';
|
|
QUERY PLAN
|
|
--------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..33.51 rows=3 width=224)
|
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
QUERY PLAN
|
|
----------------------------------------------------------------------------------------------------------------------
|
|
Bitmap Heap Scan on links (cost=4.38..23.84 rows=14 width=224)
|
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
-> Bitmap Index Scan on links_varchar_index_on_head_uuid (cost=0.00..4.38 rows=13 width=0)
|
|
Index Cond: (((head_uuid)::text ~>=~ 'zzzzz-j7d0g-'::text) AND ((head_uuid)::text ~<~ 'zzzzz-j7d0g.'::text))
|
|
(4 rows)
|
|
|
|
|
|
======
|
|
|
|
With 2,000 rows:
|
|
|
|
Without index:
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like '_____-j7d0g-%';
|
|
QUERY PLAN
|
|
--------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..51.06 rows=5 width=224)
|
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
QUERY PLAN
|
|
---------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..51.06 rows=21 width=224)
|
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
With index:
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like '_____-j7d0g-%';
|
|
QUERY PLAN
|
|
--------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..51.06 rows=3 width=224)
|
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
QUERY PLAN
|
|
----------------------------------------------------------------------------------------------------------------------
|
|
Bitmap Heap Scan on links (cost=4.38..29.20 rows=14 width=224)
|
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
-> Bitmap Index Scan on links_varchar_index_on_head_uuid (cost=0.00..4.38 rows=13 width=0)
|
|
Index Cond: (((head_uuid)::text ~>=~ 'zzzzz-j7d0g-'::text) AND ((head_uuid)::text ~<~ 'zzzzz-j7d0g.'::text))
|
|
(4 rows)
|
|
|
|
|
|
============
|
|
|
|
With 3,000 rows
|
|
|
|
Without index:
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like '_____-j7d0g-%';
|
|
QUERY PLAN
|
|
--------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..78.39 rows=4 width=224)
|
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
QUERY PLAN
|
|
---------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..81.51 rows=14 width=224)
|
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
|
|
With index:
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like '_____-j7d0g-%';
|
|
QUERY PLAN
|
|
--------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..81.51 rows=3 width=224)
|
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
QUERY PLAN
|
|
----------------------------------------------------------------------------------------------------------------------
|
|
Bitmap Heap Scan on links (cost=4.38..33.75 rows=14 width=224)
|
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
-> Bitmap Index Scan on links_varchar_index_on_head_uuid (cost=0.00..4.38 rows=13 width=0)
|
|
Index Cond: (((head_uuid)::text ~>=~ 'zzzzz-j7d0g-'::text) AND ((head_uuid)::text ~<~ 'zzzzz-j7d0g.'::text))
|
|
(4 rows)
|
|
|
|
|
|
============
|
|
|
|
With 4,000 rows
|
|
|
|
Without index:
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like '_____-j7d0g-%';
|
|
QUERY PLAN
|
|
---------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..129.51 rows=3 width=224)
|
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
QUERY PLAN
|
|
----------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..129.51 rows=14 width=224)
|
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
|
|
With index:
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like '_____-j7d0g-%';
|
|
QUERY PLAN
|
|
---------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..105.01 rows=3 width=224)
|
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
QUERY PLAN
|
|
----------------------------------------------------------------------------------------------------------------------
|
|
Bitmap Heap Scan on links (cost=4.38..35.73 rows=14 width=224)
|
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
-> Bitmap Index Scan on links_varchar_index_on_head_uuid (cost=0.00..4.38 rows=13 width=0)
|
|
Index Cond: (((head_uuid)::text ~>=~ 'zzzzz-j7d0g-'::text) AND ((head_uuid)::text ~<~ 'zzzzz-j7d0g.'::text))
|
|
(4 rows)
|
|
|
|
|
|
==========
|
|
|
|
With 5,000 rows:
|
|
|
|
Without index:
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like '_____-j7d0g-%';
|
|
QUERY PLAN
|
|
---------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..129.51 rows=3 width=224)
|
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
QUERY PLAN
|
|
----------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..129.51 rows=14 width=224)
|
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
|
|
With index:
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like '_____-j7d0g-%';
|
|
QUERY PLAN
|
|
---------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..129.51 rows=3 width=224)
|
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
QUERY PLAN
|
|
----------------------------------------------------------------------------------------------------------------------
|
|
Bitmap Heap Scan on links (cost=4.38..37.31 rows=14 width=224)
|
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
-> Bitmap Index Scan on links_varchar_index_on_head_uuid (cost=0.00..4.38 rows=13 width=0)
|
|
Index Cond: (((head_uuid)::text ~>=~ 'zzzzz-j7d0g-'::text) AND ((head_uuid)::text ~<~ 'zzzzz-j7d0g.'::text))
|
|
(4 rows)
|
|
|
|
|
|
===========
|
|
|
|
with 10,000 rows:
|
|
|
|
Without index:
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like '_____-j7d0g-%';
|
|
QUERY PLAN
|
|
---------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..248.01 rows=3 width=224)
|
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
QUERY PLAN
|
|
----------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..248.01 rows=14 width=224)
|
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
With index:
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like '_____-j7d0g-%';
|
|
QUERY PLAN
|
|
---------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..248.01 rows=3 width=224)
|
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
QUERY PLAN
|
|
----------------------------------------------------------------------------------------------------------------------
|
|
Bitmap Heap Scan on links (cost=4.39..43.87 rows=14 width=224)
|
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
-> Bitmap Index Scan on links_varchar_index_on_head_uuid (cost=0.00..4.38 rows=13 width=0)
|
|
Index Cond: (((head_uuid)::text ~>=~ 'zzzzz-j7d0g-'::text) AND ((head_uuid)::text ~<~ 'zzzzz-j7d0g.'::text))
|
|
(4 rows)
|
|
|
|
|
|
========
|
|
|
|
With 20,000 rows:
|
|
|
|
Without index:
|
|
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like '_____-j7d0g-%';
|
|
QUERY PLAN
|
|
---------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..484.68 rows=3 width=224)
|
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
QUERY PLAN
|
|
----------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..484.68 rows=14 width=224)
|
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
|
|
With index:
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like '_____-j7d0g-%';
|
|
QUERY PLAN
|
|
---------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..484.68 rows=3 width=224)
|
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
QUERY PLAN
|
|
----------------------------------------------------------------------------------------------------------------------
|
|
Bitmap Heap Scan on links (cost=4.39..47.42 rows=14 width=224)
|
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
-> Bitmap Index Scan on links_varchar_index_on_head_uuid (cost=0.00..4.39 rows=13 width=0)
|
|
Index Cond: (((head_uuid)::text ~>=~ 'zzzzz-j7d0g-'::text) AND ((head_uuid)::text ~<~ 'zzzzz-j7d0g.'::text))
|
|
(4 rows)
|
|
|
|
|
|
|
|
==========
|
|
|
|
With 30,000 rows
|
|
|
|
Without index:
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like '_____-j7d0g-%';
|
|
QUERY PLAN
|
|
---------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..717.81 rows=4 width=224)
|
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
QUERY PLAN
|
|
----------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..717.81 rows=20 width=224)
|
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
|
|
With index:
|
|
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like '_____-j7d0g-%';
|
|
QUERY PLAN
|
|
---------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..726.01 rows=3 width=224)
|
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
QUERY PLAN
|
|
----------------------------------------------------------------------------------------------------------------------
|
|
Bitmap Heap Scan on links (cost=4.39..49.05 rows=14 width=224)
|
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
-> Bitmap Index Scan on links_varchar_index_on_head_uuid (cost=0.00..4.39 rows=13 width=0)
|
|
Index Cond: (((head_uuid)::text ~>=~ 'zzzzz-j7d0g-'::text) AND ((head_uuid)::text ~<~ 'zzzzz-j7d0g.'::text))
|
|
(4 rows)
|
|
|
|
|
|
|
|
===========
|
|
|
|
With 50,000 rows
|
|
|
|
Without index:
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like '_____-j7d0g-%';
|
|
QUERY PLAN
|
|
----------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..1203.01 rows=2 width=223)
|
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
QUERY PLAN
|
|
-----------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..1203.01 rows=14 width=223)
|
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
With index:
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like '_____-j7d0g-%';
|
|
QUERY PLAN
|
|
----------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..1203.01 rows=2 width=223)
|
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
QUERY PLAN
|
|
----------------------------------------------------------------------------------------------------------------------
|
|
Bitmap Heap Scan on links (cost=4.41..54.04 rows=14 width=223)
|
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
-> Bitmap Index Scan on links_varchar_index_on_head_uuid (cost=0.00..4.40 rows=14 width=0)
|
|
Index Cond: (((head_uuid)::text ~>=~ 'zzzzz-j7d0g-'::text) AND ((head_uuid)::text ~<~ 'zzzzz-j7d0g.'::text))
|
|
(4 rows)
|
|
|
|
===========
|
|
|
|
With 160,000 rows
|
|
|
|
Without index:
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like '_____-j7d0g-%';
|
|
QUERY PLAN
|
|
----------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..3811.89 rows=1 width=225)
|
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
QUERY PLAN
|
|
-----------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..3811.89 rows=30 width=225)
|
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
With index:
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like '_____-j7d0g-%';
|
|
QUERY PLAN
|
|
----------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..3828.01 rows=1 width=225)
|
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
QUERY PLAN
|
|
----------------------------------------------------------------------------------------------------------------------
|
|
Bitmap Heap Scan on links (cost=4.60..113.44 rows=30 width=225)
|
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
-> Bitmap Index Scan on links_varchar_index_on_head_uuid (cost=0.00..4.59 rows=30 width=0)
|
|
Index Cond: (((head_uuid)::text ~>=~ 'zzzzz-j7d0g-'::text) AND ((head_uuid)::text ~<~ 'zzzzz-j7d0g.'::text))
|
|
(4 rows)
|
|
|
|
|
|
===========
|
|
|
|
With 250,000 rows
|
|
|
|
Without index:
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like '_____-j7d0g-%';
|
|
QUERY PLAN
|
|
----------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..5985.29 rows=1 width=224)
|
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
QUERY PLAN
|
|
-----------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..5985.29 rows=11 width=224)
|
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
|
|
|
|
With index:
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like '_____-j7d0g-%';
|
|
QUERY PLAN
|
|
----------------------------------------------------------
|
|
Seq Scan on links (cost=0.00..5985.29 rows=1 width=224)
|
|
Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text)
|
|
(2 rows)
|
|
|
|
arvados_dev=> explain select * from links where head_uuid like 'zzzzz-j7d0g-%';
|
|
QUERY PLAN
|
|
----------------------------------------------------------------------------------------------------------------------
|
|
Bitmap Heap Scan on links (cost=4.43..46.52 rows=11 width=224)
|
|
Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text)
|
|
-> Bitmap Index Scan on links_varchar_index_on_head_uuid (cost=0.00..4.42 rows=11 width=0)
|
|
Index Cond: (((head_uuid)::text ~>=~ 'zzzzz-j7d0g-'::text) AND ((head_uuid)::text ~<~ 'zzzzz-j7d0g.'::text))
|
|
(4 rows)
|
|
|