Postgres index for a conditional join

Question

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

The condition needs values from two different tables. There is no way you can create an index for that. An index can only be defined on expressions using columns from the table you define the index for. — user330315
– user330315, Commented Jan 27, 2021 at 10:33
Was afraid that was the case.. back to the drawing board then. Thanks anyway. — Tree Frog
– Tree Frog, Commented Jan 27, 2021 at 10:38

Frank Heikens · Accepted Answer · 2021-01-27 11:04:17Z

1

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.

answered Jan 27, 2021 at 11:04

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tree Frog Over a year ago

Alas, there is no key between them. Data actually just contains the emails of the users which I need to join on to get the ids.

Tree Frog · Accepted Answer · 2021-01-27 11:23:10Z

0

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.

answered Jan 27, 2021 at 11:23

Tree Frog

6655 silver badges13 bronze badges

Collectives™ on Stack Overflow

Postgres index for a conditional join

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related