CODEWITHSUNDEEP
sqldatabasepostgresql
91DarioDev
91DarioDev

Reputation: 1700

Postgres: design table to save associated pairs

I'm using Postgres.

Having a chat bot I need to create a table to store matches:

A paired with B and consequently B paired with A.

I was considering if create a table like:

CREATE TABLE matches (user_a INT, user_b INT);

And save the pair once like:

INSERT INTO matches (1, 2);

Or twice like:

INSERT INTO matches (1, 2);
INSERT INTO matches (2, 1);

I will need to query the table also with only one column to check user “1”

In the first case I will need to use:

SELECT * FROM matches WHERE user_a=1 or user_b=1;

In the second case I can query just with: 

SELECT * FROM matches WHERE user_a=1;

Because saving both the sides of the match.

I also will need to join this table with another table. In the first case I need to do

SELECT * FROM users LEFT JOIN matches ON matches.user_a=users.user_id OR matches.user_b=users.user_id;

In the second case I can avoid the OR doing:

SELECT * FROM users LEFT JOIN matches ON matches.user_a=users.user_id;

Which of the two approaches of using the same table would be the best practice? I think the first one could save half of the space, but the second one maybe could be more normalized and better for performance.

Upvotes: 0

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

In general, OR kills JOIN performance. It is much harder for the optimizer to optimize. That suggests that storing two rows in a table rather than 1 is often going result in better performance -- perhaps counter-intuitively, because it doubles the data values.

That said, if you do go for the more concise form, there are things you can do, such as:

SELECT u.*, m.*
FROM users u LEFT JOIN
     (matches m CROSS JOIN LATERAL
      (VALUES (m.user_a), (m.user_b)
      ) v(user_id)
     )
     ON v.user_id = u.user_id ;

This should use an index on users(user_id). That said, your version or IN should use an index on that table. But it is much harder to use an index on matches.

Upvotes: 1

Related Questions