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

select version();


version


PostgreSQL 10.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit


CREATE TABLE payment (amount INTEGER, pay_date DATE);
INSERT INTO payment VALUES
(54,  '2019-09-01'),
(56,  '2019-09-01'),
(154, '2019-09-02'),
(156, '2019-09-02'),
(254, '2019-09-03'),
(256, '2019-09-03');
6 rows affected

SELECT 
  *, 
  SUM(amount) OVER (PARTITION BY pay_date) AS the_sums
FROM payment;


amount
pay_date
the_sums


54
2019-09-01
110

56
2019-09-01
110

154
2019-09-02
310

156
2019-09-02
310

254
2019-09-03
510

256
2019-09-03
510


SELECT 
  *, 
  SUM(amount) OVER (PARTITION BY pay_date) AS the_sums
FROM payment
WHERE the_sums > 200;
-- No alias in WHERE clause
ERROR:  column "the_sums" does not exist
LINE 5: WHERE the_sums > 200;
              ^


SELECT 
  *, 
  SUM(amount) OVER (PARTITION BY pay_date) AS the_sums
FROM payment
WHERE SUM(amount) OVER (PARTITION BY pay_date) > 200;

ERROR:  window functions are not allowed in WHERE
LINE 5: WHERE SUM(amount) OVER (PARTITION BY pay_date) > 200;
              ^

amount	pay_date	the_sums
54	2019-09-01	110
56	2019-09-01	110
154	2019-09-02	310
156	2019-09-02	310
254	2019-09-03	510
256	2019-09-03	510