Postgres 9.6

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 orders (
    id SERIAL,
    created_at timestamp NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO "orders" ("created_at")
VALUES
(now() - interval '1 hours'),
(now() - interval '6 hours'),
(now() - interval '12 hours'),
(now() - interval '2 days'),
(now() - interval '4 days'),
(now() - interval '6 days'),
(now() - interval '9 days');
7 rows affected

--= Count orders grouping by date
SELECT   created_at::date, COUNT(*) num_orders
FROM     orders
WHERE    created_at <  (now() - interval '1 days')
AND      created_at >= (now() - interval '8 days')
GROUP BY created_at::date;


created_at
num_orders


2018-11-24
1

2018-11-26
1

2018-11-28
1


--= Count orders grouping by date
WITH cn AS
(
    SELECT created_at::date, COUNT(*) num_orders
    FROM   orders
    WHERE  created_at <  (now() - interval '1 days')
    AND    created_at >= (now() - interval '8 days')
    GROUP BY created_at::date
)
SELECT to_char(AVG(num_orders), '9.99') AS "AvgOrderVelocity",
       to_char(SUM(num_orders) / 7::decimal(18,2), '0.99')  AS "AvgLast7Days",
       (SELECT COUNT(*)
        FROM   orders
        WHERE  created_at > (now() - interval '1 days')) "Last24h"
FROM   cn;


AvgOrderVelocity
AvgLast7Days
Last24h


 1.00
 0.43
3