Question 114737 in PostgreSQL Question posted in
The official documentation can be found here.

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
Tags:

2

Answers


  1. 0

    No, PostgreSQL does not perform binary searches. The only access methods are listed in pg_am: sequential scan and index scans. So PostgreSQL will have to scan the whole function result. What you can to is load the function result into a temporary table, create an index on that and use that in your query, but that won’t win for a one-time query.

    I recommend keeping a permanent lookup table for the dates around.

    Login or Signup to reply.
  2. 0

    PostgreSQL doesn’t offer binary search joins. But it does offer both merge joins, and hash joins. The details of how these can be hooked up to CTE will depend on both the version of PostgreSQL, and your column data types, which you haven’t shared. You can see what it will actually do on your system with your table with EXPLAIN, or better yet EXPLAIN (ANALYZE, BUFFERS).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search