Because the WHERE
condition of the query involves only equality checks:
WHERE "songs"."processed" = 't'
AND "songs"."working" = 't'
and then you have:
SELECT DISTINCT ON (songs.rank, songs.shared_id) ...
which is similar to GROUP BY songs.rank, songs.shared_id
I would first try adding a compound index on (first the columns in WHERE
, then the columns in DISTINCT ON
):
(processed, working, rank, shared_id)
The ordering: ORDER BY rank DESC
may be better optimized if you have the index as:
(processed, working, rank DESC, shared_id)
Not really sure if this would contribute to efficiency but you can test.
Addition by @Erwin
As per request in comment
In principal (default) b-tree indexes can be scanned forward and backward at the same speed. But sorting can make a difference in multi-column indexes where you combine the sort order of multiple columns. The query starts with:
SELECT DISTINCT ON (songs.rank, songs.shared_id)
In combination with ORDER BY rank DESC
this dictates that the result be ordered by rank DESC, shared_id
effectively. After the (simplified) WHERE clause WHERE processed AND working
has been applied and before LIMIT
can be applied.
I have my doubts if the DISTINCT
clause is actually useful. But while it is there, the optimal index for the query should be (just as @ypercube suspected):
CREATE INDEX songs_special_idx
ON songs (processed, working, rank DESC, shared_id);
Looks like one of the rare cases where explicit ordering of index columns would benefit the query. There is an excellent explanation in the chapter Indexes and ORDER BY of the manual.
If the WHERE condition is stable (always WHERE processed AND working
), a partial multi-column index would be smaller and faster, yet:
CREATE INDEX songs_special_idx
ON songs (rank DESC, shared_id)
WHERE processed AND working;
If I understand what you're trying to achieve, then I think what you want is a CTE with a UNION list of all the possible tags you want to compare against, then RIGHT JOIN the tag table against the CTE.
Example -
WITH ListOfTags (Tag) AS (SELECT 'Foo' UNION SELECT 'bar' UNION SELECT 'other' UNION SELECT 'thing')
SELECT *
FROM item_tag i
INNER JOIN tags t
ON i.tag_id = t.tag_id
RIGHT JOIN ListOfTags l
ON l.Tag like t.tag
WHERE i.item_id is null
This might not be the most elegant way of doing it, but it might work for you
Best Answer
You should use foreign keys and primary keys to join the tables, and then use WHERE clause to filter rows:
Postgres allows to use
USING (join column list)
instead ofON
:Quoted from docs:
Now you can rewrite your query like this:
Have a look at:
Postgres docs:
Postgres tutorial: