Postgres 17

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE arrays (
    id SERIAL PRIMARY KEY,
    random_floats FLOAT[] NOT NULL
);

CREATE TABLE

INSERT INTO arrays (random_floats)
SELECT ARRAY(
    SELECT random() FROM generate_series(1, 500)
)
FROM generate_series(1, 1000);

INSERT 0 1000

vacuum analyze arrays;

VACUUM

with users_cte as (
  select * from pg_class
  where oid = 'arrays'::regclass
)
  select oid, relname, relpages, reltuples, reltoastrelid
 from users_cte
union all
select oid, relname, relpages, reltuples, reltoastrelid
 from pg_class
where oid = (select reltoastrelid from users_cte)
;

oid
relname
relpages
reltuples
reltoastrelid

16385
arrays
7
1000
16389

16389
pg_toast_16385
667
3000
0

SELECT 2

explain (analyze, verbose, buffers, wal, serialize off, costs off)
select id, random_floats
from arrays
;

QUERY PLAN

Seq Scan on public.arrays (actual time=0.006..0.071 rows=1000 loops=1)

  Output: id, random_floats

  Buffers: shared hit=7

Planning:

  Buffers: shared hit=18

Planning Time: 0.124 ms

Execution Time: 0.123 ms

EXPLAIN

explain (analyze, verbose, buffers, wal, serialize text, costs off)
select id, random_floats
from arrays
;

QUERY PLAN

Seq Scan on public.arrays (actual time=0.004..0.176 rows=1000 loops=1)

  Output: id, random_floats

  Buffers: shared hit=7

Planning Time: 0.017 ms

Serialization: time=85.105 ms  output=9416kB  format=text

  Buffers: shared hit=3333

Execution Time: 85.466 ms

EXPLAIN

explain (analyze, verbose, buffers, wal, serialize text, costs off)
select id, random_floats
, random_floats[42]
from arrays
;

QUERY PLAN

Seq Scan on public.arrays (actual time=0.023..5.897 rows=1000 loops=1)

  Output: id, random_floats, random_floats[42]

  Buffers: shared hit=3340

Planning Time: 0.050 ms

Serialization: time=84.998 ms  output=9438kB  format=text

  Buffers: shared hit=3333

Execution Time: 91.114 ms

EXPLAIN

explain (analyze, verbose, buffers, wal, serialize text, costs off)
select id, random_floats
, random_floats[42]
, random_floats[01]
from arrays
;

QUERY PLAN

Seq Scan on public.arrays (actual time=0.026..12.298 rows=1000 loops=1)

  Output: id, random_floats, random_floats[42], random_floats[1]

  Buffers: shared hit=6673

Planning Time: 0.051 ms

Serialization: time=100.056 ms  output=9459kB  format=text

  Buffers: shared hit=3333

Execution Time: 112.603 ms

EXPLAIN