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 setseed(0.1);--stabilise random() between re-runs

create table orders (ship_country text,order_count int);
insert into orders values
('USA',        random()*10),
('Canada',     random()*10),
('Mexico',     random()*10),
('Puerto Rico',random()*10),
('USA',        random()*10)
returning *;

setseed

SELECT 1

CREATE TABLE

ship_country
order_count

USA
7

Canada
6

Mexico
8

Puerto Rico
9

USA
6

INSERT 0 5

SELECT 
  CASE
    WHEN ship_country = 'USA' OR ship_country = 'Canada' THEN 0.0
    ELSE 10.0
  END AS shipping_cost,
  COUNT(*) AS order_count
FROM orders
GROUP BY shipping_cost;

shipping_cost
order_count

10.0
2

0.0
3

SELECT 2

explain analyze verbose
SELECT 
  CASE
    WHEN ship_country = 'USA' OR ship_country = 'Canada' THEN 0.0
    ELSE 10.0
  END AS shipping_cost,
  COUNT(*) AS order_count
FROM orders
GROUP BY shipping_cost;

QUERY PLAN

HashAggregate  (cost=35.40..38.40 rows=200 width=40) (actual time=0.016..0.017 rows=2 loops=1)

  Output: (CASE WHEN ((ship_country = 'USA'::text) OR (ship_country = 'Canada'::text)) THEN 0.0 ELSE 10.0 END), count(*)

  Group Key: CASE WHEN ((orders.ship_country = 'USA'::text) OR (orders.ship_country = 'Canada'::text)) THEN 0.0 ELSE 10.0 END

  Batches: 1  Memory Usage: 40kB

  ->  Seq Scan on public.orders  (cost=0.00..29.05 rows=1270 width=32) (actual time=0.008..0.010 rows=5 loops=1)

        Output: CASE WHEN ((ship_country = 'USA'::text) OR (ship_country = 'Canada'::text)) THEN 0.0 ELSE 10.0 END

Planning Time: 0.055 ms

Execution Time: 0.053 ms

EXPLAIN