Postgres 17

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?.

-- Comparing an index that covers the range filter vs. filtering on the table rows
create table transactions as
 select
  gen_random_uuid() as id,
  random(1,100) as user_id,
  now() - random(1,90)*interval '1 day' as transaction_date
 from generate_series(1,100000) n
;

SELECT 100000

create index tx_usr_dat on transactions (user_id, transaction_date);
create index tx_dat     on transactions (transaction_date);

CREATE INDEX

CREATE INDEX

vacuum analyze transactions;

VACUUM

prepare q(int, int) as
 SELECT * FROM transactions WHERE user_id BETWEEN $1 AND $2 ORDER BY transaction_date DESC LIMIT 100 OFFSET 100
;
explain( analyze, buffers) execute q(1,10);

PREPARE

QUERY PLAN

Limit  (cost=54.75..109.20 rows=100 width=28) (actual time=0.565..1.629 rows=100 loops=1)

  Buffers: shared hit=1067

  ->  Index Scan Backward using tx_dat on transactions  (cost=0.29..5440.29 rows=9990 width=28) (actual time=0.024..1.609 rows=200 loops=1)

        Filter: ((user_id >= 1) AND (user_id <= 10))

        Rows Removed by Filter: 1823

        Buffers: shared hit=1067

Planning:

  Buffers: shared hit=35

Planning Time: 2.211 ms

Execution Time: 1.873 ms

EXPLAIN

drop index tx_dat;
create index tx_dat_usr on transactions (transaction_date, user_id);

DROP INDEX

CREATE INDEX

vacuum analyze transactions;

VACUUM

explain( analyze, buffers) execute q(1,10);

QUERY PLAN

Limit  (cost=44.99..89.68 rows=100 width=28) (actual time=0.170..0.312 rows=100 loops=1)

  Buffers: shared hit=207

  ->  Index Scan Backward using tx_dat_usr on transactions  (cost=0.29..4533.64 rows=10143 width=28) (actual time=0.017..0.297 rows=200 loops=1)

        Index Cond: ((user_id >= 1) AND (user_id <= 10))

        Buffers: shared hit=207

Planning:

  Buffers: shared hit=31

Planning Time: 0.511 ms

Execution Time: 0.336 ms

EXPLAIN