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 cnt2 FROM t2);
SELECT * FROM v2;

cnt2

11

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

?column?

1.18181818181818

SELECT (SELECT v1.cnt1  FROM v1)/ (SELECT v2.cnt2 FROM v2)::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) -- float gives 1 - no decimal!
From num cross join div;

?column?

1.00000000000000000000

WITH num AS
(
   SELECT count(*)::float as cnt1 FROM v1
 ),
div AS
(
   SELECT count (*)::float as cnt2 FROM v2
 )
 SELECT (num.cnt1::float/div.cnt2::float) 
From num cross join div;

?column?

1

WITH num AS
(
   SELECT count(*)::numeric as cnt1 FROM v1
 ),
div AS
(
   SELECT count (*)::numeric as cnt2 FROM v2
 )
 SELECT (num.cnt1::numeric/div.cnt2::numeric) 
From num cross join div;

?column?

1.00000000000000000000