[Solved] SQL: How to speedup a view in PostgreSQL? My query takes +2 seconds, even for a low record count database

EverSQL Database Performance Knowledge Base

SQL: How to speedup a view in PostgreSQL? My query takes +2 seconds, even for a low record count database

Database type:

I have two tables
rb_content : A simple table with movies and series (title/description/category)
rb_exclude : A table with a list of titles/descriptions to exclude from rb_content for a certain category.
I'm no SQL expert, so I'm probably using the wrong approach here to accomplish this.

SELECT title as rb_title, description as rb_description
FROM rb_content
WHERE title NOT IN (
    SELECT title
    FROM rb_content
    INNER JOIN rb_exclude
        ON  ((
            (rb_content.title ILIKE '%' || rb_exclude.txt || '%') AND
            (rb_content.category = rb_exclude.category) AND
            (rb_exclude.field = 0)
            ) OR (
            (rb_content.description ILIKE '%' || rb_exclude.txt || '%') AND
            (rb_content.category = rb_exclude.category) AND
            (rb_exclude.field = 1)
            )) AND (rb_exclude.active = 1)
);

Thanks!

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid OR Conditions By Using UNION (modified query below): In mosts cases, filtering using the OR operator cannot be applied using indexes. A more optimized alternative will be to split the query to two parts combined with a UNION clause, while each query holds one part of the original OR condition.
  2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  3. Use UNION ALL instead of UNION (query line: 34): Always use UNION ALL unless you need to eliminate duplicate records. By using UNION ALL, you'll avoid the expensive distinct operation the database applies when using a UNION clause.
Optimal indexes for this query:
CREATE INDEX rb_content_idx_title ON "rb_content" ("title");
CREATE INDEX rb_exclude_idx_field_active_category ON "rb_exclude" ("field","active","category");
The optimized query:
SELECT
        rb_content.title AS rb_title,
        rb_content.description AS rb_description 
    FROM
        rb_content 
    WHERE
        rb_content.title NOT IN (
            SELECT
                title 
            FROM
                ((SELECT
                    title AS title 
                FROM
                    rb_content 
                INNER JOIN
                    rb_exclude 
                        ON (
                            (
                                (
                                    rb_content.description ILIKE '%' || rb_exclude.txt || '%'
                                ) 
                                AND (
                                    rb_content.category = rb_exclude.category
                                ) 
                                AND (
                                    rb_exclude.field = 1
                                )
                            )
                        ) 
                        AND (
                            rb_exclude.active = 1
                        )) 
            UNION
            DISTINCT (SELECT
                title AS title 
            FROM
                rb_content 
            INNER JOIN
                rb_exclude 
                    ON (((rb_content.title ILIKE '%' || rb_exclude.txt || '%') 
                    AND (rb_content.category = rb_exclude.category) 
                    AND (rb_exclude.field = 0))) 
                    AND (rb_exclude.active = 1))
            ) AS union1
        )

Related Articles



* original question posted on StackOverflow here.