Effective search for a value retrieved from COALESCE in PostgreSQL

negl1gence 0
Viewed 46

I'm implementing a dialogs search module on a messaging app. Retrieving the list of dialogs names would be as follows:

select coalesce(c.custom_name, u.username) as dialog_name 
from user_chats uc 
  join chats c using(chat_id) 
  left join users u on uc.peer_id = u.user_id

So, basically, a dialog name is either a peer's username if it is a direct chat, or a custom name if it's something else (a group chat, for instance). I need a search on dialog_name, a value that's dynamically built on each query. Both chats.custom_name and users.username have indexes on them, but I can't think of a way to perform this search effectively and using indexes. Can someone, please, help me?

p.s. user_chats consists of user_id (user_id of the client), sel (selector, user_id of peer if direct, 2 billions + sequence for client's groups if group chat), chat_id, peer_id (if direct – user_id of peer)

sql postgresql
2 Answers

You can use function based index - expression index. I tried this on sample table which has a 10 million records.

CREATE TABLE test (
    id int4 NOT NULL,
    a1 text NULL,
    a2 text NULL,
    CONSTRAINT test_pk PRIMARY KEY (id)
);
CREATE INDEX test_a1_idx ON test USING btree (a1);
CREATE INDEX test_a2_idx ON test USING btree (a2);

When I use this query:

explain analyze 
select *, coalesce(a2, a1) as pname from test
where coalesce(a2, a1) = '2813'

Gather  (cost=1000.00..12644.34 rows=5000 width=44) (actual time=0.328..94.577 rows=96 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on test  (cost=0.00..11144.34 rows=2083 width=44) (actual time=1.117..33.147 rows=32 loops=3)
        Filter: (COALESCE(a2, a1) = '2813'::text)
        Rows Removed by Filter: 333302
Planning Time: 0.189 ms
Execution Time: 94.599 ms

After adding expression index to my table:

CREATE INDEX test_a3_idx ON test (coalesce(a2, a1));

My query results this plan:

Bitmap Heap Scan on test  (cost=59.17..6069.31 rows=5000 width=44) (actual time=0.036..0.183 rows=96 loops=1)
  Recheck Cond: (COALESCE(a2, a1) = '2813'::text)
  Heap Blocks: exact=96
  ->  Bitmap Index Scan on test_a3_idx  (cost=0.00..57.92 rows=5000 width=0) (actual time=0.025..0.025 rows=96 loops=1)
        Index Cond: (COALESCE(a2, a1) = '2813'::text)
Planning Time: 0.192 ms
Execution Time: 0.204 ms

This is gets best performance over using

where a2 = '2813' or a1 = '2813'
Ramin Faracov0

I ended up creating a separate chat_names table, which eliminates the need to use COALESCE in WHERE clause. As for my initial question, I don't think it is possible to use indexes for a COALESCE of values from different tables

negl1gence0
Related