Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE event (event_id serial, event_date date);

INSERT INTO  event (event_date)  -- random dates from the 20th century ~ table of real people
SELECT '2000-1-1'::date - (random() * 36525)::int
FROM   generate_series (1, 108000);

DELETE FROM event WHERE random()< 0.08; -- generate some dead tuples to make it more real
ANALYZE event;

-- IMMUTABLE function
CREATE OR REPLACE FUNCTION f_mmdd(date)
  RETURNS int LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$func$
   SELECT ((extract('month' FROM $1) * 100) + extract('day' FROM $1))::int
$func$;

CREATE INDEX event_mmdd_event_date_idx ON event(f_mmdd(event_date), event_date);

CREATE TABLE

INSERT 0 108000

DELETE 8743

ANALYZE

CREATE FUNCTION

CREATE INDEX

CREATE OR REPLACE FUNCTION f_anniversary(_the_date date = current_date, _days int = 14)
  RETURNS SETOF event
  LANGUAGE plpgsql AS
$func$
DECLARE
   d  int := f_mmdd($1);
   d1 int := f_mmdd($1 + $2 - 1);  -- fix off-by-1 from upper bound
BEGIN
   IF d1 > d THEN
      RETURN QUERY
      SELECT *
      FROM   event e
      WHERE  f_mmdd(e.event_date) BETWEEN d AND d1
      ORDER  BY f_mmdd(e.event_date), e.event_date;

   ELSE  -- wrap around end of year
      RETURN QUERY
      SELECT *
      FROM   event e
      WHERE  f_mmdd(e.event_date) >= d OR
             f_mmdd(e.event_date) <= d1
      ORDER  BY (f_mmdd(e.event_date) >= d) DESC, f_mmdd(e.event_date), event_date;
      -- chronological across turn of the year
   END IF;
END
$func$;

CREATE FUNCTION

-- All queries without ORDER BY.

-- Basic "black magic" query:
EXPLAIN ANALYZE
SELECT *
FROM   event e
WHERE  f_mmdd(e.event_date) BETWEEN f_mmdd(current_date)
                            AND     f_mmdd(current_date + 14);

QUERY PLAN

Bitmap Heap Scan on event e  (cost=9.42..545.71 rows=496 width=8) (actual time=0.038..0.038 rows=0 loops=1)

  Recheck Cond: (((((EXTRACT(month FROM event_date) * '100'::numeric) + EXTRACT(day FROM event_date)))::integer >= (((EXTRACT(month FROM CURRENT_DATE) * '100'::numeric) + EXTRACT(day FROM CURRENT_DATE)))::integer) AND ((((EXTRACT(month FROM event_date) * '100'::numeric) + EXTRACT(day FROM event_date)))::integer <= (((EXTRACT(month FROM (CURRENT_DATE + 14)) * '100'::numeric) + EXTRACT(day FROM (CURRENT_DATE + 14))))::integer))

  ->  Bitmap Index Scan on event_mmdd_event_date_idx  (cost=0.00..9.29 rows=496 width=0) (actual time=0.035..0.035 rows=0 loops=1)

        Index Cond: (((((EXTRACT(month FROM event_date) * '100'::numeric) + EXTRACT(day FROM event_date)))::integer >= (((EXTRACT(month FROM CURRENT_DATE) * '100'::numeric) + EXTRACT(day FROM CURRENT_DATE)))::integer) AND ((((EXTRACT(month FROM event_date) * '100'::numeric) + EXTRACT(day FROM event_date)))::integer <= (((EXTRACT(month FROM (CURRENT_DATE + 14)) * '100'::numeric) + EXTRACT(day FROM (CURRENT_DATE + 14))))::integer))

Planning Time: 0.564 ms

Execution Time: 0.071 ms

EXPLAIN

-- "today"
EXPLAIN ANALYZE SELECT * FROM f_anniversary();

QUERY PLAN

Function Scan on f_anniversary  (cost=0.25..10.25 rows=1000 width=8) (actual time=48.476..48.691 rows=3852 loops=1)

Planning Time: 0.019 ms

Execution Time: 48.910 ms

EXPLAIN

-- end of year (wraps around)
EXPLAIN ANALYZE SELECT * FROM f_anniversary('2012-12-25'::date);

QUERY PLAN

Function Scan on f_anniversary  (cost=0.25..10.25 rows=1000 width=8) (actual time=8.411..8.607 rows=3852 loops=1)

Planning Time: 0.039 ms

Execution Time: 8.780 ms

EXPLAIN

-- "Advanced query" for today
EXPLAIN ANALYZE
WITH val AS (
   SELECT extract(year FROM age(now()::date + 14, min(event_date)))::int AS max_y
        , extract(year FROM age(now()::date,      max(event_date)))::int AS min_y
   FROM   event
   )
SELECT e.*
FROM  (
   SELECT ((current_date + d.d) - interval '1y' * y.y)::date AS event_date
   FROM   generate_series(0, 14) d
       , (SELECT generate_series(min_y, max_y) AS y FROM val) y
   ) x
JOIN   event e USING (event_date);
-- ORDER  BY e.mmdd < mmdd(current_date), e.mmdd, event_date;

QUERY PLAN

Hash Join  (cost=5066.14..8948.63 rows=50877 width=8) (actual time=207.459..269.719 rows=4140 loops=1)

  Hash Cond: ((((CURRENT_DATE + d.d) - ('1 year'::interval * ((generate_series(((EXTRACT(year FROM age(((now())::date)::timestamp with time zone, (max(event.event_date))::timestamp with time zone)))::integer), ((EXTRACT(year FROM age((((now())::date + 14))::timestamp with time zone, (min(event.event_date))::timestamp with time zone)))::integer))))::double precision)))::date = e.event_date)

  ->  Nested Loop  (cost=1966.86..2172.07 rows=15000 width=8) (actual time=71.155..71.480 rows=1530 loops=1)

        ->  Function Scan on generate_series d  (cost=0.00..0.15 rows=15 width=4) (actual time=0.018..0.022 rows=15 loops=1)

        ->  Materialize  (cost=1966.86..1986.92 rows=1000 width=4) (actual time=4.742..4.751 rows=102 loops=15)

              ->  ProjectSet  (cost=1966.86..1971.92 rows=1000 width=4) (actual time=71.125..71.141 rows=102 loops=1)

                    ->  Aggregate  (cost=1966.86..1966.90 rows=1 width=8) (actual time=71.120..71.121 rows=1 loops=1)

                          ->  Seq Scan on event  (cost=0.00..1470.57 rows=99257 width=4) (actual time=0.014..7.988 rows=99257 loops=1)

  ->  Hash  (cost=1470.57..1470.57 rows=99257 width=8) (actual time=136.188..136.189 rows=99257 loops=1)

        Buckets: 131072  Batches: 2  Memory Usage: 2966kB

        ->  Seq Scan on event e  (cost=0.00..1470.57 rows=99257 width=8) (actual time=0.015..8.830 rows=99257 loops=1)

Planning Time: 0.358 ms

Execution Time: 269.982 ms

EXPLAIN

-- "Advanced query" for end of year (wraps around)
EXPLAIN ANALYZE
WITH val AS (
   SELECT extract(year FROM age('2012-12-25'::date + 14, min(event_date)))::int AS max_y
        , extract(year FROM age('2012-12-25'::date,      max(event_date)))::int AS min_y
   FROM   event
   )
SELECT e.*
FROM  (
   SELECT (('2012-12-25'::date + d.d) - interval '1y' * y.y)::date AS event_date
   FROM   (SELECT generate_series(0, 14) AS d) d
        , (SELECT generate_series(min_y, max_y) AS y FROM val) y
   ) x
JOIN   event e USING (event_date);
-- ORDER  BY e.mmdd < mmdd('2012-12-25'::date), e.mmdd, event_date;

QUERY PLAN

Hash Join  (cost=5066.14..8571.24 rows=50877 width=8) (actual time=233.334..295.858 rows=4115 loops=1)

  Hash Cond: (((('2012-12-25'::date + (generate_series(0, 14))) - ('1 year'::interval * ((generate_series(((EXTRACT(year FROM age(('2012-12-25'::date)::timestamp with time zone, (max(event.event_date))::timestamp with time zone)))::integer), ((EXTRACT(year FROM age(('2013-01-08'::date)::timestamp with time zone, (min(event.event_date))::timestamp with time zone)))::integer))))::double precision)))::date = e.event_date)

  ->  Nested Loop  (cost=1966.86..2169.69 rows=15000 width=8) (actual time=76.871..77.167 rows=1530 loops=1)

        ->  ProjectSet  (cost=1966.86..1971.91 rows=1000 width=4) (actual time=76.859..76.874 rows=102 loops=1)

              ->  Aggregate  (cost=1966.86..1966.89 rows=1 width=8) (actual time=76.854..76.855 rows=1 loops=1)

                    ->  Seq Scan on event  (cost=0.00..1470.57 rows=99257 width=4) (actual time=0.013..7.830 rows=99257 loops=1)

        ->  Materialize  (cost=0.00..0.32 rows=15 width=4) (actual time=0.000..0.001 rows=15 loops=102)

              ->  ProjectSet  (cost=0.00..0.09 rows=15 width=4) (actual time=0.003..0.006 rows=15 loops=1)

                    ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

  ->  Hash  (cost=1470.57..1470.57 rows=99257 width=8) (actual time=156.265..156.266 rows=99257 loops=1)

        Buckets: 131072  Batches: 2  Memory Usage: 2966kB

        ->  Seq Scan on event e  (cost=0.00..1470.57 rows=99257 width=8) (actual time=0.019..85.687 rows=99257 loops=1)

Planning Time: 0.306 ms

Execution Time: 296.055 ms

EXPLAIN