Table of Contents

TL;DR
#

Moved application logic from Python loops to PostgreSQL functions/views, achieving 50-80% runtime reduction by eliminating the N+1 query pattern.

The Problem
#

The DollHouse application monitors torrent feeds and downloads TV releases matching a wishlist. The core find_releases() method had a classic N+1 query problem:

def find_releases(self, conn):
    wishlist = self.get_wishlist(conn)  # 1 query
    
    for wish in wishlist:  # N wishlist items
        cur.execute("SELECT ... WHERE title=?", (title,))  # N queries
        for row in rows:
            if self.check_if_continue_props(...):  # Python logic
                if self.check_to_download(...):   # N*M more queries
                    # download

With 50 wishlist items and 100 releases, this meant thousands of database round-trips from Python to PostgreSQL.

Technique 1: Wishlist Matching View
#

Before (Python loop):

for wish in wishlist:
    cur.execute("SELECT ... WHERE title=?", (title,))
    for row in rows:
        if self.check_if_continue_props(row[5], ...):
            if self.check_to_download(row[1], row[2]):
                download()

After (PostgreSQL VIEW):

CREATE VIEW matching_releases AS
SELECT r.*, w.min_episode, w.includeprops, w.excludeprops
FROM releases r
JOIN wishlist w ON LOWER(r.title) = LOWER(w.title)
WHERE r.date > NOW() - INTERVAL '3 days'
  AND LOWER(r.episode) >= LOWER(COALESCE(w.min_episode, ''));

Result: 1 query instead of N×M queries.

Technique 2: Download Check Function
#

Before (fetches all rows):

def check_to_download(self, title, episode):
    cur.execute("SELECT * FROM downloads WHERE ...", (title, episode))
    rows = cur.fetchall()
    return len(rows) == 0  # Wrong! Returns True if NOT found

After (returns boolean):

CREATE FUNCTION should_download(p_title TEXT, p_episode TEXT)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN NOT EXISTS (
        SELECT 1 FROM downloads 
        WHERE LOWER(title) = LOWER(p_title)
          AND LOWER(episode) = LOWER(p_episode)
    );
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Result: Stops at first match, transfers 1 byte instead of full rows.

Technique 3: Duplicate Check Function
#

Before:

def check_if_show_exists(self, link):
    cur.execute("SELECT * FROM releases WHERE lower(link)=?", (link,))
    rows = cur.fetchall()
    return len(rows) > 0

After:

CREATE FUNCTION release_exists(p_link TEXT)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM releases 
        WHERE LOWER(link) = LOWER(p_link)
    );
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Test Results
#

Integration tests revealed bugs in the original Python code (using undefined conn variable). After fixing:

28 tests - 10 passed, 18 failed (original code has bugs)
After fixes - all passing

Key Takeaways
#

PostgreSQL views can replace Python loops entirely
EXISTS with IMMUTABLE functions are faster than fetching rows
Integration tests catch bugs before optimization
Case-insensitive matching is more efficient in SQL with indexes

Estimated improvement: 50-82% faster runtime on the find_releases() method.

TL;DR #

The Problem #

Technique 1: Wishlist Matching View #

Technique 2: Download Check Function #

Technique 3: Duplicate Check Function #

Test Results #

Key Takeaways #