agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Mike Sofen <msofen@runbox.com>
To: 'Brice André' <brice@famille-andre.be>
To: pgsql-sql@lists.postgresql.org
Subject: RE: SQL schema and query optimisation for fast cross-table query execution
Date: Sat, 25 Jan 2020 05:18:42 -0800
Message-ID: <0f9f01d5d381$f8216e00$e8644a00$@runbox.com> (raw)
In-Reply-To: <CAOBG12=LHSmZGkOqzRDgomjXQ73u-OazaqbD-RpuDkB-TkyN8A@mail.gmail.com>
References: <CAOBG12=LHSmZGkOqzRDgomjXQ73u-OazaqbD-RpuDkB-TkyN8A@mail.gmail.com>
From: Brice André <brice@famille-andre.be> Sent: Saturday, January 25, 2020 12:49 AM
wrote:
The current DB schema is as follows:
user table : this table records all users registered on the web-site
- user_id
- ...
challenge table: this table records all ongoing challenges
- challenge_id
- ...
user_challenge_association table : this table records which user is registered to which challenge
- user_id
- challenge_id
activity table : this table records all activities of the users
- user_id
- date
- ...
a naive implementation of my query could be something like this:
SELECT * FROM activity WHERE user_id IN (SELECT user_id FROM user_challenge_association WHERE challenge_id IN (SELECT challenge_id FROM user_challenge_association WHERE user_id = ||current_user||)) ORDER BY date
Brice
---------------------------------------------------------------
Brice, you are correct, “IN” clauses are horrific performers because they turn every member of the IN into an OR, so the larger your IN list, the slower your query runs.
But every IN can be replaced with a join, and that leverages the power of the relational engine. With standard indexes on the primary and foreign keys, this will be extremely fast and scalable to many hundreds of millions of rows.
Taking your query and rewriting it with joins:
SELECT u.user_name, c.challenge_name, a.activity_name, a.activity_date
FROM activity a
Join user_challenge_association uca on (user_id)
Join challenge c on (challenge_id)
Join user u on (user_id)
Where u.user_id = ||current_user||)) ORDER BY date
Mike Sofen
view thread (2+ messages)
Message-ID: <0f9f01d5d381$f8216e00$e8644a00$@runbox.com>
Permalink: ../0f9f01d5d381$f8216e00$e8644a00$@runbox.com/
Also on: postgresql.org/message-id/0f9f01d5d381$f8216e00$e8644a00$@runbox.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: msofen@runbox.com, brice@famille-andre.be, pgsql-sql@lists.postgresql.org
Subject: RE: SQL schema and query optimisation for fast cross-table query execution
In-Reply-To: <0f9f01d5d381$f8216e00$e8644a00$@runbox.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox