Postgres 11

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?.

CREATE TABLE mytable (value INTEGER, mary TEXT)

CREATE INDEX fred_ix ON mytable (value);
INSERT INTO mytable VALUES (3, 'sadfasfa');
INSERT INTO mytable VALUES (3, 'sadfasfa');

INSERT INTO mytable VALUES (4, 'oioiuoiou');
INSERT INTO mytable VALUES (4, 'oioiuoiou');
INSERT INTO mytable VALUES (4, 'oioiuoiou');
INSERT INTO mytable VALUES (4, 'oioiuoiou');
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

SELECT DISTINCT(value) FROM mytable;

value

3

4

SELECT (DISTINCT value) FROM mytable;
ERROR:  syntax error at or near "DISTINCT"
LINE 1: SELECT (DISTINCT value) FROM mytable;
                ^

EXPLAIN ANALYZE SELECT DISTINCT (value) FROM mytable;

QUERY PLAN

HashAggregate  (cost=25.88..27.88 rows=200 width=4) (actual time=0.017..0.018 rows=2 loops=1)

  Group Key: value

  ->  Seq Scan on mytable  (cost=0.00..22.70 rows=1270 width=4) (actual time=0.008..0.009 rows=6 loops=1)

Planning Time: 0.049 ms

Execution Time: 0.056 ms