Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
SELECT '[5:7]={1,2,3}'::int[];

int4

[5:7]={1,2,3}

SELECT 1

SELECT ('[5:7]={1,2,3}'::int[])[5];

int4

1

SELECT 1

SELECT ('[5:7]={1,2,3}'::int[])[array_lower('[5:7]={1,2,3}'::int[], 1):array_upper('[5:7]={1,2,3}'::int[], 1)];

int4

{1,2,3}

SELECT 1

WITH cte(a) AS (SELECT '[5:7]={1,2,3}'::int[])
SELECT a[array_lower(a, 1):array_upper(a, 1)]
FROM   cte;

a

{1,2,3}

SELECT 1

-- table for benchark
-- testing 3 solutions for Postgres 9.5
CREATE TEMP TABLE tbl (a int[]);
INSERT INTO tbl -- with varying subscripts
SELECT ('[' || g%10 || ':' || 2*(g%10) || ']={1'
            || repeat(','||g::text, g%10) || '}')::int[]
FROM   generate_series(1,100000) g;

CREATE TABLE

INSERT 0 100000

-- round 1
EXPLAIN (ANALYZE, TIMING OFF) SELECT substring(a::text, '{.*$')::int[]      FROM tbl;
EXPLAIN (ANALYZE, TIMING OFF) SELECT a[array_lower(a, 1):array_upper(a, 1)] FROM tbl;
EXPLAIN (ANALYZE, TIMING OFF) SELECT a[-2147483648:2147483647]              FROM tbl;
EXPLAIN (ANALYZE, TIMING OFF) SELECT a[:]                                   FROM tbl;

QUERY PLAN

Seq Scan on tbl  (cost=0.00..3792.04 rows=127024 width=32) (actual rows=100000 loops=1)

Planning Time: 0.132 ms

Execution Time: 358.986 ms

EXPLAIN

QUERY PLAN

Seq Scan on tbl  (cost=0.00..2839.36 rows=127024 width=32) (actual rows=100000 loops=1)

Planning Time: 0.052 ms

Execution Time: 45.265 ms

EXPLAIN

QUERY PLAN

Seq Scan on tbl  (cost=0.00..2204.24 rows=127024 width=32) (actual rows=100000 loops=1)

Planning Time: 0.050 ms

Execution Time: 38.579 ms

EXPLAIN

QUERY PLAN

Seq Scan on tbl  (cost=0.00..2204.24 rows=127024 width=32) (actual rows=100000 loops=1)

Planning Time: 0.049 ms

Execution Time: 37.988 ms

EXPLAIN

-- round 2
EXPLAIN (ANALYZE, TIMING OFF) SELECT substring(a::text, '{.*$')::int[]      FROM tbl;
EXPLAIN (ANALYZE, TIMING OFF) SELECT a[array_lower(a, 1):array_upper(a, 1)] FROM tbl;
EXPLAIN (ANALYZE, TIMING OFF) SELECT a[-2147483648:2147483647]              FROM tbl;
EXPLAIN (ANALYZE, TIMING OFF) SELECT a[:]                                   FROM tbl;

QUERY PLAN

Seq Scan on tbl  (cost=0.00..3792.04 rows=127024 width=32) (actual rows=100000 loops=1)

Planning Time: 0.051 ms

Execution Time: 359.487 ms

EXPLAIN

QUERY PLAN

Seq Scan on tbl  (cost=0.00..2839.36 rows=127024 width=32) (actual rows=100000 loops=1)

Planning Time: 0.055 ms

Execution Time: 57.583 ms

EXPLAIN

QUERY PLAN

Seq Scan on tbl  (cost=0.00..2204.24 rows=127024 width=32) (actual rows=100000 loops=1)

Planning Time: 0.050 ms

Execution Time: 48.094 ms

EXPLAIN

QUERY PLAN

Seq Scan on tbl  (cost=0.00..2204.24 rows=127024 width=32) (actual rows=100000 loops=1)

Planning Time: 0.070 ms

Execution Time: 39.625 ms

EXPLAIN