Increase query speed in PostgreSQL

Question

I am experiencing slow query times on my database (all tested locally so far) and not sure how to go about it. The database itself has 44 tables and some of these tables have over 1 million records (mainly the movies, actresses and actors tables).

The table is made via JMDB using the flat files on IMDB. Also the SQL query that I am about to show is from that said program (that too experiences very slow search times). I have tried to include as much information as I can, such as the query plan etc.

"QUERY PLAN"<br /> 
"HashAggregate  (cost=46492.52..46493.50 rows=98 width=46)"<br />
"  Output: public.movies.title, public.movies.movieid, public.movies.year"<br />
"  ->  Append  (cost=39094.17..46491.79 rows=98 width=46)"<br />
"        ->  HashAggregate  (cost=39094.17..39094.87 rows=70 width=46)"<br />
"              Output: public.movies.title, public.movies.movieid, public.movies.year"<br />
"              ->  Seq Scan on movies  (cost=0.00..39093.65 rows=70 width=46)"<br />
"                    Output: public.movies.title, public.movies.movieid, public.movies.year"<br />
"                    Filter: (((title)::text ~~* '%Babe%'::text) AND ((title)::text !~~* '""%}'::text))"<br />
"        ->  Nested Loop  (cost=0.00..7395.94 rows=28 width=46)"<br />
"              Output: public.movies.title, public.movies.movieid, public.movies.year"<br />
"              ->  Seq Scan on akatitles  (cost=0.00..7159.24 rows=28 width=4)"<br />
"                    Output: akatitles.movieid, akatitles.language, akatitles.title, <akatitles.addition"<br />
"                    Filter: (((title)::text ~~* '%Babe%'::text) AND ((title)::text !~~* '""%}'::text))"<br />
"              ->  Index Scan using movies_pkey on movies  (cost=0.00..8.44 rows=1 width=46)"<br />
"                    Output: public.movies.movieid, public.movies.title, public.movies.year, public.movies.imdbid"
"                    Index Cond: (public.movies.movieid = akatitles.movieid)"<br />

SELECT * FROM (
    (SELECT DISTINCT title, movieid, year 
    FROM movies 
    WHERE title ILIKE '%Babe%' AND NOT (title ILIKE '"%}'))
UNION
    (SELECT movies.title, movies.movieid, movies.year 
    FROM movies 
    INNER JOIN akatitles ON movies.movieid=akatitles.movieid 
    WHERE akatitles.title ILIKE '%Babe%' AND NOT (akatitles.title ILIKE '"%}'))
) AS union_tmp2;

Returns 612 Rows in 9078ms<br />
Database backup (plain text) is 1.61GB

It's a really complex query and I am not fully cognizant on it, like I said it was spat out by JMDB.

Do you have any suggestions on how I can increase the speed ?

Can you (if possible) run an explain analyze on the table so we can be sure if the costs are accurate? Judging by the amount of rows in the query I would expect that you are missing an index. — Wolph
– Wolph, Commented Mar 22, 2010 at 5:16
After reading your query again it I would expect most of the slowdown to be in the title ILIKE '%Babe%' parts. You could "easily" make that a lot faster by using fulltext indexes for the searching. — Wolph
– Wolph, Commented Mar 22, 2010 at 5:20
What version of postgres are you using? 8.4 released last year has some significant performance improvements. — crowne
– crowne, Commented Mar 22, 2010 at 5:26

Frank Heikens · Accepted Answer · 2010-03-22 07:26:36Z

3

This is your problem:

" -> Seq Scan on movies (cost=0.00..39093.65 rows=70 width=46)"
" Output: public.movies.title, public.movies.movieid, public.movies.year"
" Filter: (((title)::text ~~* '%Babe%'::text) AND ((title)::text !~~* '""%}'::text))"

A sequential scan and huge costs because the database can't use any index on '%Babe%'. Take a look at full text search, than you can create a proper index and let the queryplanner use it.

answered Mar 22, 2010 at 7:26

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Timothy · Accepted Answer · 2010-03-22 08:21:07Z

2

A query that uses double-ended wild-cards (e.g. '%Babe%') cannot leverage any index, so the table will result in a sequential scan instead of an index scan.

If you were searching for 'Babe%', then your index should work.

answered Mar 22, 2010 at 8:21

Timothy

2,47719 silver badges15 bronze badges

Collectives™ on Stack Overflow

Increase query speed in PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related