Postgres 9.5

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;

INSERT 0 100000

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

QUERY PLAN

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

Planning time: 0.036 ms

Execution time: 78.213 ms

EXPLAIN

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

QUERY PLAN

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

Planning time: 0.032 ms

Execution time: 81.744 ms

EXPLAIN