Postgresql join between many tables

Question

Ok, I used MySQL/postgres for many years before moving to MongoDB for the past 6yrs and I'm drawing some blanks now. I've been looking around on the best way to do this and with my very simple dataset the querytime is an average of 20ms. Seems crazy for < 100 rows. I probably did this the super complex way :)

So the idea here is to get a list of the lessons the user has access to, along with their LATEST quiz score and LATEST lesson progress (how far they made it in the lesson).

SELECT DISTINCT ON (m.id) m.id,m.lesson_name,m.course_name,m.order,m.progress,m.thumb,m.media_id,m.length,m.quiz_id,m.score FROM (SELECT
    lessons.id,lessons.name AS lesson_name,courses.name AS course_name, lesson_courses."order", less_prog.progress, lessons.thumb, lessons.media_id, lessons.length, lesson_quiz.id as quiz_id, acclessq.score,acclessq.timestamp as atime, less_prog.timestamp as ltime
    FROM lessons
    INNER JOIN lesson_courses ON lessons."id" = lesson_courses.lessons_id
    INNER JOIN plan_courses ON plan_courses.courses_id = lesson_courses.courses_id
    INNER JOIN courses ON courses.id = lesson_courses.courses_id
    LEFT JOIN (
        SELECT progress,lessons_id,timestamp FROM lesson_progress WHERE accounts_id = 1
    ) as less_prog ON less_prog.lessons_id = lessons.id
    LEFT JOIN (
        SELECT lesson_quiz.id,lesson_quiz.lessons_id FROM lesson_quiz
    ) as lesson_quiz ON lessons.id=lesson_quiz.lessons_id
    LEFT JOIN (
        SELECT score,lesson_quiz_id,account_lesson_quiz.timestamp FROM account_lesson_quiz WHERE account_lesson_quiz.accounts_id = 1
    ) as acclessq ON lesson_quiz.id=acclessq.lesson_quiz_id
    LEFT JOIN (
        SELECT accounts_id,plans_id FROM account_plans WHERE accounts_id = 1
    ) as account_plans ON account_plans.plans_id = plan_courses.plans_id
    GROUP BY lessons.id,courses.id,courses.name,lesson_courses.order,less_prog.progress, lessons.thumb, lessons.media_id, lessons.length, quiz_id, acclessq.score,atime,ltime
    ORDER BY courses.id, lesson_courses."order",atime DESC,ltime DESC) m

I'm sure I've done it way too crazy, but I get the right results... So how can I simplify it and get the performance down for when we have more results?

UPDATE: Tried rewriting using WITH's. Dropped perf to an average of 15ms. It does look much cleaner but I still feel it's way too high for this little amount of data.

WITH lesson_quiz AS (SELECT lesson_quiz.id,lesson_quiz.lessons_id FROM 
lesson_quiz),
less_prog AS (SELECT progress,lessons_id,timestamp FROM lesson_progress 
WHERE accounts_id = 1),
acclessq AS (SELECT score,lesson_quiz_id,account_lesson_quiz.timestamp 
FROM account_lesson_quiz WHERE account_lesson_quiz.accounts_id = 1),
acc_plans AS (SELECT accounts_id,plans_id FROM account_plans WHERE 
accounts_id = 1)

SELECT DISTINCT ON (lessons.id)
lessons.id,lessons.name AS lesson_name,courses.name AS course_name, lesson_courses."order", less_prog.progress, lessons.thumb, lessons.media_id, lessons.length, lesson_quiz.id as quiz_id, acclessq.score
FROM lessons
INNER JOIN lesson_courses ON lessons."id" = lesson_courses.lessons_id
INNER JOIN plan_courses ON plan_courses.courses_id = lesson_courses.courses_id
INNER JOIN courses ON courses.id = lesson_courses.courses_id
LEFT JOIN less_prog ON less_prog.lessons_id = lessons.id
LEFT JOIN lesson_quiz ON lessons.id=lesson_quiz.lessons_id
LEFT JOIN acclessq ON lesson_quiz.id=acclessq.lesson_quiz_id
LEFT JOIN acc_plans ON acc_plans.plans_id = plan_courses.plans_id
ORDER BY id, acclessq.timestamp DESC, less_prog.timestamp DESC

Here is the query plan: https://explain.depesz.com/s/IvPl

Removing all of the distinct and group/order by reduces the time by 1-2ms but I'm still averaging 19ms and getting 301 rows vs the 3 I need — Beta4
– Beta4, Commented Nov 12, 2017 at 21:46

Neil Anderson · Accepted Answer · 2017-11-12 23:32:52Z

2

GROUP BY and DISTINCT are often achieving the same thing. Are you sure you need DISTINCT and if so does it perform better when you remove it?

Thanks for the query plan. Maybe an index on the timestamp DESC columns will help? Seems like the majority of the time is spent sorting.

edited Nov 12, 2017 at 23:32

answered Nov 12, 2017 at 21:13

Neil Anderson

1,39512 silver badges19 bronze badges

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

1 Comment

Beta4 Over a year ago

I did the GROUP BY and DISTINCT because I need to order the results before I "group" them with DISTINCT into unique results.

Super Mario · Accepted Answer · 2017-11-12 21:18:46Z

1

WITH Queries may look like more elegant. Another option is to create a View.

answered Nov 12, 2017 at 21:18

Super Mario

93912 silver badges19 bronze badges

3 Comments

Beta4 Over a year ago

I rewrote it trying WITH. I dropped the querytime by a little. Avg 15ms now (it does look much cleaner). I still feel this is WAY too high for this little number of rows. (see original post for example)

Beta4 Over a year ago

Also tried the View route, no help other than making the final query look cleaner but no perf gain.

Super Mario Over a year ago

I have one more UGLY idea: I pretty sure LEFT JOIN's are the the most expensive thing in query. The idea is to do something like that: less_prog AS( select NULL as progress, lessons_id, NULL as timestamp ( SELECT id as lessons_id FROM lessons INTERSECT SELECT lessons_id FROM lesson_progress WHERE accounts_id = 1 ) UNION SELECT progress, lessons_id, timestamp FROM lesson_progress WHERE accounts_id = 1 ) And then make LEFT JOIN less_prog ON less_prog.lessons_id = lessons.id Is it dropped the time?

Beta4 · Accepted Answer · 2017-11-15 13:11:23Z

0

It ended up coming down to the computer. I was testing on my latest edition, fully maxed Macbook Pro. Switching to my desktop, I'm not consistently getting >0.9ms.

answered Nov 15, 2017 at 13:11

Beta4

1111 gold badge1 silver badge7 bronze badges

Collectives™ on Stack Overflow

Postgresql join between many tables

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related