Left join pre-filtering on first table in postgres

Question

I want to run a query to get user photo album ids, names, and picture count in the album. This query works:

SELECT album.id, album.name, count(pictures.*)
FROM album
LEFT JOIN pictures
    ON (pictures.album_id=album.id)
WHERE album.owner = ?
GROUP BY album.id;

I have tons of pictures, and lots of albums, but the join is running before filtering for the user I'm interested in.

I have seen other answers that filter inside the join based on the 2nd table's values, but I want to filter on album.owner which is not included in the 2nd table. How can I filter before the join? (and is it efficient? will I break indexes?)

score 2 · Accepted Answer · answered Feb 02 '16 at 20:20

For this query:

SELECT a.id, a.name, count(p.album_id)
FROM album a LEFT JOIN
     pictures p
    ON p.album_id = a.id
WHERE a.owner = ?
GROUP BY a.id;

You want an index on album(owner, id, name). This should speed your query.

However, it will probably be faster if phrased like this:

SELECT a.id, a.name,
       (SELECT count(*)
        FROM pictures p
        WHERE p.album_id = a.id
       )
FROM album a 
WHERE a.owner = ?

Here you want the above index and an index on pictures(album_id).

Left join pre-filtering on first table in postgres

1 Answers1