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

How to optimize sql requests? – Postgresql

How to optimize requests? Hello there is a chat which consists of the following tables

Chat rooms

id, type(USER_TO_USER, USER_TO_SHOP, USER_TO_PLATFORM), name

Chat Room Members room_participants

id, participantType (USER, SHOP, PLATFORM), participant_id

list of messages

room_messages - id, participant_id, text, is_read

Additionally:

users table

id, first_name, last_name, image, image_src

shops table

id, name, logo, logo_src

The task is:

Display a list of the room with its members for a specific user + the last message of the room (as VK)
the first thing I did was a request to get all the rooms: in which a specific user is a member

select r.id, r.name from rooms as r
left join room_participants rp on r.id = rp.room_id
where rp."participantType" = 'USER' and rp.participant_id = 1

the second request I make is to get members (other than the current user) for each room

select
    room_id,
    case
        when "participantType" = 'USER' and participant_id != 1 then (SELECT id FROM users WHERE id = participant_id)
        when "participantType" = 'SHOP' then (SELECT id FROM shops WHERE id = participant_id)
    END AS participant_id,

    case
        when "participantType" = 'USER' and participant_id != 1 then (SELECT first_name FROM users WHERE id = participant_id)
        when "participantType" = 'SHOP' then (SELECT name FROM shops WHERE id = participant_id)
    END AS participant_name,

    case
        when "participantType" = 'USER' and participant_id != 1 then (SELECT image FROM users WHERE id = participant_id)
        when "participantType" = 'SHOP' then (SELECT logo FROM shops WHERE id = participant_id)
    END AS participant_image,

    case
        when "participantType" = 'USER' and participant_id != 1 then (SELECT image_src FROM users WHERE id = participant_id)
        when "participantType" = 'SHOP' then (SELECT logo_src FROM shops WHERE id = participant_id)
    END AS participant_image_src

from room_participants

where room_id in (1)

The question is, how can these queries be optimized?

and the second question, if these are optimal queries, then how with the last query, how to remove empty lines

введите сюда описание изображения

Tags:

2

Answers


  1. 0

    You can try using joins instead sub-queries:

    select
    room_id,
    COALESCE(u.id, s.id) AS participant_id,
    COALESCE(u.first_name, s.name) AS participant_name,
    COALESCE(u.image, s.logo) AS participant_image,
    COALESCE(u.image_src, s.logo_src) AS participant_image_src

from room_participants p
left join shops s
    on p.participant_id = s.id
    and p.participantType = 'SHOP'
left join users u
    on p.participant_id = u.id
    and p.participantType = 'USER'
    AND p.participant_id != 1

where room_id in (1)
    AND COALESCE(u.id, s.id) IS NOT NULL
    Login or Signup to reply.
  2. 0

    use union with inner join for better performance compare using left join

    select rp.room_id, u.id as participant_id, u.first_name as participant_name, u.image as participant_image, 
    u.image_src participant_image_src
from room_participants rp
join users u on u.id = rp.participant_id
where rp.room_id in (1)
and rp.participantType = 'USER' and rp.participant_id != 1

UNION ALL 

select rp.room_id, s.id as participant_id, s.name as participant_name, s.logo as participant_image, 
    s.logo_src participant_image_src
from room_participants rp
join shops s on s.id = rp.participant_id
where rp.room_id in (1)
and rp.participantType = 'SHOP' and rp.participant_id != 1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search