Postgresql – Are joins in CTEs indexed or searchable in postgres

Does anyone know if Postgres is smart enough to search an in memory table in a binary fashion of some sort, or if not, is there a way to mark a column to tell postgres it is in order and can be searched in a binary fashion?

I know you cannot put an index on an in memory table / CTE in postgres. I’m mostly curious if anyone knows if postgres is already searching in a certain way, or if I can give it a hint to do it. Ultimately, I’m curious if it’s even worth trying to do what I’m doing.

In my scenario, I’m creating a series of dates (generate_series(...)), in order by day, along with some calculated info I’d like to include in a number of results. It’s the functional equivalent of a date lookup table, with the exception that I’m generating this table in memory (CTE) for a limited subset of dates. The general idea is that this would prevent me from needing to do certain calculations on every single row of data in my target table (which may and frequently does have the same date in a number of different records), but instead, only once for each date and then simply look it up in the target table.

However, this could potentially be a few years of dates (5+) and I imagine if postgres does a column scan on this data, it might be slower than just doing the calculations in line.

somewhat contrived example of what I’m doing (also untested so might have some syntax errors), but I think it gets the basic point across.

WITH dates AS (
  SELECT 
    day::date as day,
    EXTRACT(DOW FROM day) as day_of_week,
    EXTRACT(DOY FROM day) as day_of_year -- etc
  FROM generate_series('1/1/2015'::timestamp, '12/31/2020'::timestamp, '1 day'::interval) day
)
SELECT
  mt.*, d.day_of_week, d.day_of_year -- etc
FROM my_table mt INNER JOIN dates d ON mt.date = d.day