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 cust (cust_code VARCHAR(20), sugg_order integer, ordered_qty integer, ord_date date);
INSERT INTO cust VALUES ('cust_1', 45, 450, '2018-01-28');
INSERT INTO cust VALUES ('cust_1', 45, 350, '2018-02-28');
INSERT INTO cust VALUES ('cust_1', 45,  50, '2018-03-28');
INSERT INTO cust VALUES ('cust_1', 45, 451, '2018-04-01');
INSERT INTO cust VALUES ('cust_1', 45, 150, '2018-04-28');
INSERT INTO cust VALUES ('cust_1', 45,  87, '2018-05-28');
INSERT INTO cust VALUES ('cust_1', 45,  93, '2018-06-28');
INSERT INTO cust VALUES ('cust_1', 45,  56, '2018-07-28');
INSERT INTO cust VALUES ('cust_1', 45, 400, '2018-08-28');
INSERT INTO cust VALUES ('cust_1', 45, 234, '2017-01-28');
INSERT INTO cust VALUES ('cust_1', 45, 432, '2017-02-28');
INSERT INTO cust VALUES ('cust_1', 45, 657, '2017-03-28');
INSERT INTO cust VALUES ('cust_1', 45,  43, '2017-04-28');
INSERT INTO cust VALUES ('cust_1', 45,  32, '2017-05-28');
INSERT INTO cust VALUES ('cust_1', 45, 111, '2017-06-28');
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

1 rows affected

1 rows affected

select * from cust;


cust_code
sugg_order
ordered_qty
ord_date


cust_1
45
450
2018-01-28

cust_1
45
350
2018-02-28

cust_1
45
50
2018-03-28

cust_1
45
451
2018-04-01

cust_1
45
150
2018-04-28

cust_1
45
87
2018-05-28

cust_1
45
93
2018-06-28

cust_1
45
56
2018-07-28

cust_1
45
400
2018-08-28

cust_1
45
234
2017-01-28

cust_1
45
432
2017-02-28

cust_1
45
657
2017-03-28

cust_1
45
43
2017-04-28

cust_1
45
32
2017-05-28

cust_1
45
111
2017-06-28


WITH start_tax_year AS
(
  SELECT
    CASE
      WHEN DATE_PART('MONTH', NOW()) >= 4 
        THEN TO_DATE(TO_CHAR(DATE_PART('YEAR', NOW())::INT,     '9999') || '04' || '01', 'YYYYMMDD')
        ELSE TO_DATE(TO_CHAR(DATE_PART('YEAR', NOW())::INT - 1, '9999') || '04' || '01', 'YYYYMMDD')
    END AS start_of_tax_year
)
SELECT * FROM cust, start_tax_year
WHERE ord_date <= NOW() AND ord_date >= (TABLE start_tax_year)
ORDER BY ord_date

cust_code	sugg_order	ordered_qty	ord_date
cust_1	45	450	2018-01-28
cust_1	45	350	2018-02-28
cust_1	45	50	2018-03-28
cust_1	45	451	2018-04-01
cust_1	45	150	2018-04-28
cust_1	45	87	2018-05-28
cust_1	45	93	2018-06-28
cust_1	45	56	2018-07-28
cust_1	45	400	2018-08-28
cust_1	45	234	2017-01-28
cust_1	45	432	2017-02-28
cust_1	45	657	2017-03-28
cust_1	45	43	2017-04-28
cust_1	45	32	2017-05-28
cust_1	45	111	2017-06-28