Postgres 13

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 TEMP TABLE mycustomers (
  id serial PRIMARY KEY
, created_date date
, created_time int
);

INSERT INTO mycustomers(created_date, created_time)
VALUES
  ('2020-05-27',  1000)
, ('2020-05-27', 23456)
, ('2020-05-27', 84000);
3 rows affected

TABLE mycustomers;


id
created_date
created_time


1
2020-05-27
1000

2
2020-05-27
23456

3
2020-05-27
84000


-- while stuck, create a multicolumn index
CREATE INDEX multicolumn_created ON mycustomers (created_date, created_time);
-- while stuck
SELECT *, created_date + make_interval(secs => created_time) AS created_at
FROM   MyCustomers
WHERE  (created_date, created_time)
     > (date '2020-05-27 00:36:48', EXTRACT(epoch FROM time '2020-05-27 00:36:48')::int);


id
created_date
created_time
created_at


2
2020-05-27
23456
2020-05-27 06:30:56

3
2020-05-27
84000
2020-05-27 23:20:00


-- to show index in query plan
SET enable_seqscan = off;

-- can use index!
EXPLAIN ANALYZE
SELECT *
FROM   MyCustomers
WHERE (created_date, created_time)
    > (date '2020-05-27 00:36:48', EXTRACT(epoch FROM time '2020-05-27 00:36:48')::int);


QUERY PLAN


Index Scan using multicolumn_created on mycustomers  (cost=0.13..8.15 rows=1 width=12) (actual time=0.021..0.022 rows=2 loops=1)

  Index Cond: (ROW(created_date, created_time) > ROW('2020-05-27'::date, 2208))

Planning Time: 0.058 ms

Execution Time: 0.046 ms


-- proper fix
BEGIN;

DROP INDEX multicolumn_created;

ALTER TABLE mycustomers
   ALTER created_date TYPE timestamp USING (created_date + make_interval(secs => created_time)) 
 , DROP COLUMN created_time;

ALTER TABLE mycustomers RENAME created_date TO created_at;  -- or whatever

CREATE INDEX simple_created ON mycustomers (created_at);

COMMIT;
-- simple query
SELECT * FROM MyCustomers WHERE created_at > '2020-05-27 00:36:48.131509';


id
created_at


2
2020-05-27 06:30:56

3
2020-05-27 23:20:00


-- can use new index (obviously)
EXPLAIN ANALYZE
SELECT * FROM MyCustomers WHERE created_at > '2020-05-27 00:36:48.131509';


QUERY PLAN


Index Scan using simple_created on mycustomers  (cost=0.13..8.15 rows=1 width=12) (actual time=0.007..0.009 rows=2 loops=1)

  Index Cond: (created_at > '2020-05-27 00:36:48.131509'::timestamp without time zone)

Planning Time: 0.039 ms

Execution Time: 0.022 ms

id	created_date	created_time	created_at
2	2020-05-27	23456	2020-05-27 06:30:56
3	2020-05-27	84000	2020-05-27 23:20:00