Postgres 16

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 stockstatus (
    stockdate DATE NOT NULL,        
    product CHARACTER(60) NOT NULL,  
    status INT NOT NULL,            
    CONSTRAINT pk_stockstatus PRIMARY KEY (stockdate, product)
);

CREATE TABLE


INSERT INTO stockstatus (stockdate, product, status) VALUES
('2023-12-01', 'Table', 10),
('2023-12-05', 'Table', 20),
  
('2023-12-05', 'Desk', 10),
('2023-12-10', 'Desk', 20) ,
('2023-12-15', 'Desk', 10);

INSERT 0 5


select * from stockstatus ;


stockdate
product
status


2023-12-01
Table
10

2023-12-05
Table
20

2023-12-05
Desk
10

2023-12-10
Desk
20

2023-12-15
Desk
10



SELECT 5


select * from stockstatus1 ;

ERROR:  relation "stockstatus1" does not exist
LINE 1: select * from stockstatus1 ;
                      ^


WITH date_ranges AS (
    SELECT 
        s.product,
        s.stockdate,
        LEAD(s.stockdate) OVER (PARTITION BY s.product ORDER BY s.stockdate) AS next_stockdate,
        s.status
    FROM 
        stockstatus s
),
periods AS (
    SELECT 
        product,
        stockdate AS start_date,
        COALESCE(next_stockdate, '9999-12-31'::date) AS end_date,
        status,
        next_stockdate
    FROM date_ranges
),
daily_status AS (
    SELECT 
        p.product,
        p.start_date,
        p.status,
        p.next_stockdate,
        CASE 
            WHEN p.next_stockdate IS NULL THEN LEAST(p.end_date, '2023-12-31'::date) - GREATEST(p.start_date, '2023-12-01'::date) + 1
            ELSE LEAST(p.end_date, '2023-12-31'::date) - GREATEST(p.start_date, '2023-12-01'::date)
        END AS days_in_period
    FROM periods p
    WHERE p.end_date >= '2023-12-01' AND p.start_date <= '2023-12-31'
),
monthly_status AS (
    SELECT 
        ds.product,
        SUM(ds.days_in_period * ds.status) AS weighted_status_sum,
        COUNT(*) AS total_days_in_period,


product
weighted_status_sum
days_in_month
status


Desk
320
31
11

Table
580
31
19



SELECT 2

stockdate	product	status
2023-12-01	Table	10
2023-12-05	Table	20
2023-12-05	Desk	10
2023-12-10	Desk	20
2023-12-15	Desk	10