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 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit |
SELECT 1
Create Table tbl ( id Int, date_1 Date, date_2 Date );
Insert Into tbl VALUES ( 1, '2022-01-01', '2022-01-31' ),
( 2, '2022-01-01', '2023-01-31' ),
( 3, '2024-01-01', '2024-03-31' );
Select * From tbl;
CREATE TABLE
INSERT 0 3
| id | date_1 | date_2 |
|---|---|---|
| 1 | 2022-01-01 | 2022-01-31 |
| 2 | 2022-01-01 | 2023-01-31 |
| 3 | 2024-01-01 | 2024-03-31 |
SELECT 3
/* same month - same year */
Select *
From tbl
Where To_Char(date_1, 'yyyymm') = To_Char(date_2, 'yyyymm')
| id | date_1 | date_2 |
|---|---|---|
| 1 | 2022-01-01 | 2022-01-31 |
SELECT 1
Select *
From tbl
Where Date_Trunc('Month', date_1) = Date_Trunc('Month', date_2)
| id | date_1 | date_2 |
|---|---|---|
| 1 | 2022-01-01 | 2022-01-31 |
SELECT 1
Select *
From tbl
Where Extract(YEAR From date_1) = Extract(YEAR From date_2) And
Extract(MONTH From date_1) = Extract(MONTH From date_2)
| id | date_1 | date_2 |
|---|---|---|
| 1 | 2022-01-01 | 2022-01-31 |
SELECT 1
Select *
From tbl
Where DATE_PART('YEAR', date_1) = DATE_PART('YEAR', date_2) And
DATE_PART('MONTH', date_1) = DATE_PART('MONTH', date_2)
| id | date_1 | date_2 |
|---|---|---|
| 1 | 2022-01-01 | 2022-01-31 |
SELECT 1
/* same month - any year */
Select *
From tbl
Where To_Char(date_1, 'mm') = To_Char(date_2, 'mm')
| id | date_1 | date_2 |
|---|---|---|
| 1 | 2022-01-01 | 2022-01-31 |
| 2 | 2022-01-01 | 2023-01-31 |
SELECT 2
Select *
From tbl
Where Extract(MONTH From date_1) = Extract(MONTH From date_2)
| id | date_1 | date_2 |
|---|---|---|
| 1 | 2022-01-01 | 2022-01-31 |
| 2 | 2022-01-01 | 2023-01-31 |
SELECT 2
Select *
From tbl
Where DATE_PART('MONTH', date_1) = DATE_PART('MONTH', date_2)
| id | date_1 | date_2 |
|---|---|---|
| 1 | 2022-01-01 | 2022-01-31 |
| 2 | 2022-01-01 | 2023-01-31 |
SELECT 2