CODEWITHSUNDEEP
postgresql
Beta4
Beta4

Reputation: 111

Postgresql join between many tables

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

Upvotes: 0

Views: 76

Answers (3)

Beta4
Beta4

Reputation: 111

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.

Upvotes: 0

Neil Anderson
Neil Anderson

Reputation: 1355

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.

Upvotes: 2

Super Mario
Super Mario

Reputation: 939

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

Upvotes: 1

Related Questions