Postgres 10

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 t1 (x INT);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (4);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

SELECT * FROM t1;

x

4

4

4

4

4

4

4

4

4

4

4

4

4

CREATE TABLE t2 (y INT);
INSERT INTO t2 VALUES (5);
INSERT INTO t2 VALUES (5);
INSERT INTO t2 VALUES (5);
INSERT INTO t2 VALUES (5);
INSERT INTO t2 VALUES (5);
INSERT INTO t2 VALUES (5);
INSERT INTO t2 VALUES (5);
INSERT INTO t2 VALUES (5);
INSERT INTO t2 VALUES (5);
INSERT INTO t2 VALUES (5);
INSERT INTO t2 VALUES (5);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

SELECT * FROM t2;

y

5

5

5

5

5

5

5

5

5

5

5

CREATE VIEW v1 AS (SELECT COUNT(*) AS cnt1 FROM t1);
SELECT * FROM v1;

cnt1

13

CREATE VIEW v2 AS (SELECT COUNT(*) AS cnt1 FROM t2);
SELECT * FROM v2;

cnt1

11

select (select count(*) from t1) / (select count(*) from t2)::float

?column?

1.18181818181818

WITH num AS
(
  SELECT count (*) as cnt1 FROM v1
),
div AS
(
  SELECT count (*) as cnt2 FROM v2
)
SELECT (num.cnt1::numeric/div.cnt2);
ERROR:  missing FROM-clause entry for table "num"
LINE 9: SELECT (num.cnt1::numeric/div.cnt2);
                ^