Postgres 15

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();
--https://stackoverflow.com/q/74351164/5298879
--https://stackoverflow.com/a/74351256/5298879
create table customers(id serial primary key, name text);
create table orders(
  id serial primary key, 
  order_date date, 
  customer_id integer references customers(id)
  );
with inserted_customer as (insert into customers(name) select 'Bob' returning id)
  insert into orders (order_date,customer_id) select now()::date,inserted_customer.id from inserted_customer;
with inserted_customer as (insert into customers(name) select 'Ted' returning id)
  insert into orders (order_date,customer_id) select now()::date-'1 day'::interval,inserted_customer.id from inserted_customer;
insert into orders (order_date,customer_id) 
select 
  now()::date-'2 days'::interval,
  1;
select 
  customers.*,
  orders.* 
from customers 
  inner join orders 
  on customers.id=orders.customer_id;


version


PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1



CREATE TABLE



CREATE TABLE



INSERT 0 1



INSERT 0 1



INSERT 0 1




id
name
id
order_date
customer_id


1
Bob
1
2022-11-07
1

2
Ted
2
2022-11-06
2

1
Bob
3
2022-11-05
1



SELECT 3


with o as (
  SELECT 
    id, 
    customer_id,
    order_date 
  FROM orders)
SELECT 
  customers.id, 
  customers.name, 
  o.order_date
FROM customers, 
     o
WHERE o.id = (
    SELECT id
    FROM o
    WHERE o.customer_id = customers.id
    LIMIT 1)
ORDER BY customer_id, order_date;


id
name
order_date


1
Bob
2022-11-07

2
Ted
2022-11-06



SELECT 2


SELECT 
  c.id, 
  c.name, 
  max(o.order_date) as most_recent_order_date
FROM customers c 
  inner join orders o 
  on o.customer_id=c.id
group by c.id, c.name
ORDER BY c.id;


id
name
most_recent_order_date


1
Bob
2022-11-07

2
Ted
2022-11-06



SELECT 2