|
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)
|