Index scan speed doesn't depend on underlying data type and size (PostgreSQL)

1_ini_data.sql

--util function
create or replace function random_string(length integer) returns text as
$$
declare
  chars text[] := '{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}';
  result text := '';
  i integer := 0;
begin
  if length < 0 then
    raise exception 'Given length cannot be less than 0';
  end if;
  for i in 1..length loop
    result := result || chars[1+random()*(array_length(chars, 1)-1)];
  end loop;
  return result;
end;
$$ language plpgsql;

-- "short" text keys (10 chars per key)
create table txt_short(data text primary key, created timestamp default now());
insert into txt_short (data) select random_string(10) from generate_series(1, 1000000);

-- "long" text keys (500 chars per key)
create table txt_long(data text primary key, created timestamp default now());
insert into txt_short (data) select random_string(500) from generate_series(1, 1000000);

-- integer keys (int4)
create table inttest(id int4 primary key, created timestamp default now());
insert into inttest(id) select generate_series::int4 from generate_series(1, 1000000);

2_results.txt

test=# select created from txt_long where data = 'uwAHqSHixVGN4LR4XZVJpShjTYoKl8ng5wxvPFdNltkoFCtmlP6brnLLMAe8ISoNPmJo3wDnqxd6AXsvvyXnms892nHKF6hfs1UvygjpeNvotokpnIdaBmjEZ1Yp7GU0IyvHff7L33AwrumfDRHOE1cn3BdAR8Aj761nl78nAIk2CYhPzynD0Q13beC3mMmtSngEuo247m6JLnjLmXYmxZpZD2c0OPtqDa48Q6CXsIqE7aZt88g6gWguYJuwionvPr4pxGNrYE6fofZxmG4UmkOL4JHm85iXwmNu2kmaysGoYpmL6qpraEDeXUReZ9CXvZSxKFZK8p8hfu3klsdM7p1eLRJubWSX6uVQA5jJus0an3LYvxu3ovh9N24xXWVdR14b6nu1gubTxv3uuxxisfsGhwEFTjsulwWrkRsRNTuLQxGKvE4otv5bsJqM3kHohngRGYsc3nxSlEnhSqVMmaxfto2xYJmG8ShN2b15PyXBDKrfBO2x';
          created
---------------------------
 2016-10-11 21:17:05.17858
(1 row)

Time: 0.562 ms

test=# select created from txt_short where data = 'SXxSUdcvrJ';                                                                                                                                            created
----------------------------
 2016-10-11 21:07:19.386883
(1 row)

Time: 0.534 ms                                                                                                                 

test=# select created from inttest where id = 500000;                                                                                                                                                      created
----------------------------
 2016-10-11 21:31:10.176188
(1 row)

Time: 0.569 ms                                                                                                                                                                                   test=# insert into inttest(id) select generate_series::int4 from generate_series(1, 1000000);^C
test=# explain analyze verbose select created from txt_long where data = 'uwAHqSHixVGN4LR4XZVJpShjTYoKl8ng5wxvPFdNltkoFCtmlP6brnLLMAe8ISoNPmJo3wDnqxd6AXsvvyXnms892nHKF6hfs1UvygjpeNvotokpnIdaBmjEZ1Yp7GU0IyvHff7L33AwrumfDRHOE1cn3BdAR8Aj761nl78nAIk2CYhPzynD0Q13beC3mMmtSngEuo247m6JLnjLmXYmxZpZD2c0OPtqDa48Q6CXsIqE7aZt88g6gWguYJuwionvPr4pxGNrYE6fofZxmG4UmkOL4JHm85iXwmNu2kmaysGoYpmL6qpraEDeXUReZ9CXvZSxKFZK8p8hfu3klsdM7p1eLRJubWSX6uVQA5jJus0an3LYvxu3ovh9N24xXWVdR14b6nu1gubTxv3uuxxisfsGhwEFTjsulwWrkRsRNTuLQxGKvE4otv5bsJqM3kHohngRGYsc3nxSlEnhSqVMmaxfto2xYJmG8ShN2b15PyXBDKrfBO2x';

                                                                         QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using txt_long_pkey on public.txt_long  (cost=0.80..8.82 rows=1 width=8) (actual time=0.042..0.043 rows=1 loops=1)
   Output: created
   Index Cond: (txt_long.data = 'uwAHqSHixVGN4LR4XZVJpShjTYoKl8ng5wxvPFdNltkoFCtmlP6brnLLMAe8ISoNPmJo3wDnqxd6AXsvvyXnms892nHKF6hfs1UvygjpeNvotokpnIdaBmjEZ1Yp7GU0IyvHff7L33AwrumfDRHOE1cn3BdAR8Aj761nl78nAIk2CYhPzynD0Q13beC3mMmtSngEuo247m6JLnjLmXYmxZpZD2c0OPtqDa48Q6CXsIqE7aZt88g6gWguYJuwionvPr4pxGNrYE6fofZxmG4UmkOL4JHm85iXwmNu2kmaysGoYpmL6qpraEDeXUReZ9CXvZSxKFZK8p8hfu3klsdM7p1eLRJubWSX6uVQA5jJus0an3LYvxu3ovh9N24xXWVdR14b6nu1gubTxv3uuxxisfsGhwEFTjsulwWrkRsRNTuLQxGKvE4otv5bsJqM3kHohngRGYsc3nxSlEnhSqVMmaxfto2xYJmG8ShN2b15PyXBDKrfBO2x'::text)
 Planning time: 0.075 ms
 Execution time: 0.069 ms
(5 rows)


test=# explain analyze verbose select created from txt_short where data = 'SXxSUdcvrJ';
                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Index Scan using txt_short_pkey on public.txt_short  (cost=0.42..8.44 rows=1 width=8) (actual time=0.033..0.034 rows=1 loops=1)
   Output: created
   Index Cond: (txt_short.data = 'SXxSUdcvrJ'::text)
 Planning time: 0.079 ms
 Execution time: 0.060 ms
(5 rows)


test=# explain analyze verbose select created from inttest where id = 500000;                                                                                                                                                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Index Scan using inttest_pkey on public.inttest  (cost=0.42..8.44 rows=1 width=8) (actual time=0.019..0.020 rows=1 loops=1)
   Output: created
   Index Cond: (inttest.id = 500000)
 Planning time: 0.078 ms
 Execution time: 0.046 ms
(5 rows)

3_however__index_sizes.txt

test=# SELECT
    t.tablename,
    indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
    pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='public' and t.tablename in ('inttest', 'txt_short', 'txt_long')
ORDER BY 1,2;
 tablename |   indexname    | num_rows | table_size | index_size
-----------+----------------+----------+------------+------------
 inttest   | inttest_pkey   |    1e+06 | 42 MB      | 21 MB
 txt_long  | txt_long_pkey  |    1e+06 | 521 MB     | 873 MB
 txt_short | txt_short_pkey |    1e+06 | 50 MB      | 39 MB
(3 rows)


-- See also: https://medium.com/@ankane/large-text-indexes-in-postgres-5d7f1677f89f