Postgres 12

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 my_table 
(
    the_id varchar(5) NOT NULL, 
    the_amount int NOT NULL, 
    the_date date NOT NULL
)

INSERT INTO my_table
VALUES ('LMUS1', '200', '2019-02-12'), 
       ('LMUS1', '200', '2019-02-11'), 
       ('LMUS1', '300', '2019-02-13'), 
       ('LMUS2', '100', '2019-02-10'), 
       ('LMUS2', '150', '2019-02-09');
5 rows affected

SELECT the_amount, the_date
FROM my_table 
WHERE the_id = 'LMUS1' 
and the_date = (select min(the_date) from my_table WHERE the_id = 'LMUS1' );


the_amount
the_date


200
2019-02-11


select min(the_date) 
from my_table 
WHERE the_id = 'LMUS1';


min


2019-02-11


select the_amount, min(the_date) 
from my_table 
WHERE the_id = 'LMUS1';
ERROR:  column "my_table.the_amount" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select the_amount, min(the_date) 
               ^


select the_amount, min(the_date) 
from my_table 
WHERE the_id = 'LMUS1'
group by the_amount;


the_amount
min


200
2019-02-11

300
2019-02-13