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)