No measurable difference with or without the indexes for “explain analyze” With 10,000 links Without index: arvados_dev=> explain analyze select * from links where head_uuid like '_____-j7d0g-%'; QUERY PLAN ------------------------------------------------------------------------------------------------------------ Seq Scan on links (cost=0.00..240.40 rows=10005 width=234) (actual time=0.016..30.470 rows=10014 loops=1) Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text) Total runtime: 69.508 ms (3 rows) arvados_dev=> explain analyze select * from links where head_uuid like 'zzzzz-j7d0g-%'; QUERY PLAN ------------------------------------------------------------------------------------------------------------ Seq Scan on links (cost=0.00..240.40 rows=10014 width=234) (actual time=0.011..29.645 rows=10014 loops=1) Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text) Total runtime: 54.383 ms (3 rows) With index: arvados_dev=> explain analyze select * from links where head_uuid like '_____-j7d0g-%'; QUERY PLAN ------------------------------------------------------------------------------------------------------------ Seq Scan on links (cost=0.00..240.40 rows=10005 width=234) (actual time=0.012..29.185 rows=10014 loops=1) Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text) Total runtime: 54.080 ms (3 rows) arvados_dev=> explain analyze select * from links where head_uuid like 'zzzzz-j7d0g-%'; QUERY PLAN ------------------------------------------------------------------------------------------------------------ Seq Scan on links (cost=0.00..240.40 rows=10014 width=234) (actual time=0.017..29.035 rows=10014 loops=1) Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text) Total runtime: 54.272 ms (3 rows) =========== With 20,000 links Without index arvados_dev=> explain analyze select * from links where head_uuid like '_____-j7d0g-%'; QUERY PLAN ------------------------------------------------------------------------------------------------------------ Seq Scan on links (cost=0.00..475.43 rows=19686 width=234) (actual time=0.012..64.738 rows=20014 loops=1) Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text) Total runtime: 125.166 ms (3 rows) arvados_dev=> explain analyze select * from links where head_uuid like 'zzzzz-j7d0g-%'; QUERY PLAN ------------------------------------------------------------------------------------------------------------ Seq Scan on links (cost=0.00..475.43 rows=19695 width=234) (actual time=0.017..63.461 rows=20014 loops=1) Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text) Total runtime: 117.584 ms (3 rows) With index: arvados_dev=> explain analyze select * from links where head_uuid like '_____-j7d0g-%'; QUERY PLAN ------------------------------------------------------------------------------------------------------------ Seq Scan on links (cost=0.00..479.40 rows=20003 width=234) (actual time=0.019..64.330 rows=20014 loops=1) Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text) Total runtime: 135.665 ms (3 rows) arvados_dev=> explain analyze select * from links where head_uuid like 'zzzzz-j7d0g-%'; QUERY PLAN ------------------------------------------------------------------------------------------------------------ Seq Scan on links (cost=0.00..479.40 rows=20013 width=234) (actual time=0.011..63.831 rows=20014 loops=1) Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text) Total runtime: 122.056 ms (3 rows) ============ With 100,000 rows: Without index: --------------------------------------------------------------------------------------------------------------- Seq Scan on links (cost=0.00..2380.82 rows=99396 width=230) (actual time=0.016..315.595 rows=100014 loops=1) Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text) Total runtime: 580.910 ms (3 rows) arvados_dev=> explain analyze select * from links where head_uuid like 'zzzzz-j7d0g-%'; QUERY PLAN --------------------------------------------------------------------------------------------------------------- Seq Scan on links (cost=0.00..2380.82 rows=99396 width=230) (actual time=0.014..314.564 rows=100014 loops=1) Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text) Total runtime: 581.566 ms (3 rows) With index: arvados_dev=> explain analyze select * from links where head_uuid like '_____-j7d0g-%'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Seq Scan on links (cost=0.00..2388.40 rows=100002 width=230) (actual time=0.016..320.711 rows=100014 loops=1) Filter: ((head_uuid)::text ~~ '_____-j7d0g-%'::text) Total runtime: 620.608 ms (3 rows) arvados_dev=> explain analyze select * from links where head_uuid like 'zzzzz-j7d0g-%'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Seq Scan on links (cost=0.00..2388.40 rows=100002 width=230) (actual time=0.014..317.493 rows=100014 loops=1) Filter: ((head_uuid)::text ~~ 'zzzzz-j7d0g-%'::text) Total runtime: 592.811 ms (3 rows)