CODEWITHSUNDEEP
databasepostgresqlindexing
Tree Frog
Tree Frog

Reputation: 666

Postgres index for a conditional join

I have 3 tables (all simplified here)

job (id int, type char(1))
data (job_id int, owner int, creator int, value int)
user (id int)

My query is

select user.id, job.id, sum(data.value)
from job
join data on job.id = data.job_id
join user on case job.type when 'O' then data.owner else data.creator end = user.id
group by user.id, job.id

How do I create an index in Postgres which caters for the case statement in the join?

Job would have maybe a dozen rows, users 1000s and data millions.

Thanks

Upvotes: 0

Views: 359

Answers (2)

Tree Frog
Tree Frog

Reputation: 666

Seems this isn't possible. Solved my issue by refactoring code to add a row into data for each type, along with a new "type" column and then indexed against this.

Upvotes: 0

Frank Heikens
Frank Heikens

Reputation: 127307

In your example, you don't need the table "user" to get the results:

SELECT 
    CASE 
        WHEN job.type = '0' THEN DATA.OWNER
        ELSE DATA.creator
    END AS user_id,
    job.ID,
    DATA.VALUE
FROM
    job
    JOIN DATA ON job.ID = DATA.job_id -- indexes on the id's
GROUP BY 1,2;

Edit: Assumption: There is a foreign key between "data" and "user" that checks if a user exists.

Upvotes: 1

Related Questions