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?.
create table test(created_at timestamp);

--sample timestamps around every hour between now and 30 years from now
insert into test select g+random()*'1h'::interval 
from generate_series(now(),now()+'30 years','1h')_(g);

CREATE TABLE

INSERT 0 262993

create index on test((created_at::time));

CREATE INDEX

analyze test;

ANALYZE

explain (analyze,timing)
select created_at
from test
where created_at::time between make_time(10,0,0) and make_time(15,30,0);

QUERY PLAN

Bitmap Heap Scan on test  (cost=1272.65..3631.26 rows=59730 width=8) (actual time=5.102..12.266 rows=60346 loops=1)

  Recheck Cond: (((created_at)::time without time zone >= '10:00:00'::time without time zone) AND ((created_at)::time without time zone <= '15:30:00'::time without time zone))

  Heap Blocks: exact=1164

  ->  Bitmap Index Scan on test_created_at_idx  (cost=0.00..1257.72 rows=59730 width=0) (actual time=4.944..4.945 rows=60346 loops=1)

        Index Cond: (((created_at)::time without time zone >= '10:00:00'::time without time zone) AND ((created_at)::time without time zone <= '15:30:00'::time without time zone))

Planning Time: 0.189 ms

Execution Time: 14.313 ms

EXPLAIN

explain (analyze,timing)
select created_at
from test
where created_at::time between '2023-12-07 10:00'::time and '2023-12-07 15:30'::time;

QUERY PLAN

Bitmap Heap Scan on test  (cost=1272.65..3631.26 rows=59730 width=8) (actual time=4.093..11.213 rows=60346 loops=1)

  Recheck Cond: (((created_at)::time without time zone >= '10:00:00'::time without time zone) AND ((created_at)::time without time zone <= '15:30:00'::time without time zone))

  Heap Blocks: exact=1164

  ->  Bitmap Index Scan on test_created_at_idx  (cost=0.00..1257.72 rows=59730 width=0) (actual time=3.942..3.942 rows=60346 loops=1)

        Index Cond: (((created_at)::time without time zone >= '10:00:00'::time without time zone) AND ((created_at)::time without time zone <= '15:30:00'::time without time zone))

Planning Time: 0.080 ms

Execution Time: 13.266 ms

EXPLAIN

explain (analyze,timing)
select created_at
from test
where DATE_PART('hour',   created_at) >= 10
AND   DATE_PART('minute', created_at) >= 0
OR    DATE_PART('hour',   created_at) <= 15
AND   DATE_PART('minute', created_at) <= 30;

QUERY PLAN

Seq Scan on test  (cost=0.00..9053.79 rows=55196 width=8) (actual time=0.022..102.872 rows=210427 loops=1)

  Filter: (((date_part('hour'::text, created_at) >= '10'::double precision) AND (date_part('minute'::text, created_at) >= '0'::double precision)) OR ((date_part('hour'::text, created_at) <= '15'::double precision) AND (date_part('minute'::text, created_at) <= '30'::double precision)))

  Rows Removed by Filter: 52566

Planning Time: 0.072 ms

Execution Time: 109.913 ms

EXPLAIN

discard all;

DISCARD ALL

prepare cast_to_time_s(int,int,double precision,int,int,double precision) as 
select created_at
from test
where created_at::time between make_time($1,$2,$3) and make_time($4,$5,$6);

prepare four_date_parts(int,int,int,int) as 
select created_at
from test
where DATE_PART('hour',   created_at) >= $1
AND   DATE_PART('minute', created_at) >= $2
OR    DATE_PART('hour',   created_at) <= $3
AND   DATE_PART('minute', created_at) <= $4;

drop table if exists timings;
create table timings (attempt_n int, variant text, started timestamp, finished timestamp);
do $f$
declare i int; started_ timestamp; finished_ timestamp;
begin
for i in 1..50 loop
   started_=clock_timestamp();
   execute ('execute cast_to_time_s(10,0,0,15,30,0)');
   finished_=clock_timestamp();
   insert into timings select i,'cast_to_time_s',started_,finished_;
   started_=clock_timestamp();
   execute ('execute four_date_parts(10,0,15,30)');
   finished_=clock_timestamp();
   insert into timings select i,'four_date_parts',started_,finished_;
end loop; 
end $f$;
select variant,
       avg(finished-started)"avg execution time - lower is better" 
from timings group by variant order by 2 asc;

PREPARE

PREPARE

DROP TABLE

CREATE TABLE

DO

variant
avg execution time - lower is better

cast_to_time_s
00:00:00.012207

four_date_parts
00:00:00.110047

SELECT 2