Databases
StackOverflow2013
Postsdbo
POIncrease query speed in PostgreSQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIncrease query speed in PostgreSQL
    primarykey
    data
    text
    <p>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). </p> <p>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.</p> <pre><code>"QUERY PLAN"&lt;br /&gt; "HashAggregate (cost=46492.52..46493.50 rows=98 width=46)"&lt;br /&gt; " Output: public.movies.title, public.movies.movieid, public.movies.year"&lt;br /&gt; " -&gt; Append (cost=39094.17..46491.79 rows=98 width=46)"&lt;br /&gt; " -&gt; HashAggregate (cost=39094.17..39094.87 rows=70 width=46)"&lt;br /&gt; " Output: public.movies.title, public.movies.movieid, public.movies.year"&lt;br /&gt; " -&gt; Seq Scan on movies (cost=0.00..39093.65 rows=70 width=46)"&lt;br /&gt; " Output: public.movies.title, public.movies.movieid, public.movies.year"&lt;br /&gt; " Filter: (((title)::text ~~* '%Babe%'::text) AND ((title)::text !~~* '""%}'::text))"&lt;br /&gt; " -&gt; Nested Loop (cost=0.00..7395.94 rows=28 width=46)"&lt;br /&gt; " Output: public.movies.title, public.movies.movieid, public.movies.year"&lt;br /&gt; " -&gt; Seq Scan on akatitles (cost=0.00..7159.24 rows=28 width=4)"&lt;br /&gt; " Output: akatitles.movieid, akatitles.language, akatitles.title, &lt;akatitles.addition"&lt;br /&gt; " Filter: (((title)::text ~~* '%Babe%'::text) AND ((title)::text !~~* '""%}'::text))"&lt;br /&gt; " -&gt; Index Scan using movies_pkey on movies (cost=0.00..8.44 rows=1 width=46)"&lt;br /&gt; " Output: public.movies.movieid, public.movies.title, public.movies.year, public.movies.imdbid" " Index Cond: (public.movies.movieid = akatitles.movieid)"&lt;br /&gt; </code></pre> <hr> <pre><code>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; </code></pre> <hr> <pre><code>Returns 612 Rows in 9078ms&lt;br /&gt; Database backup (plain text) is 1.61GB </code></pre> <p>It's a really complex query and I am not fully cognizant on it, like I said it was spat out by JMDB. </p> <p>Do you have any suggestions on how I can increase the speed ?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPac0
    1. USAnthoni Gardner
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COCan 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.
      singulars
      1. POIncrease query speed in PostgreSQL
      1. USWolph
    2. COAfter 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.
      singulars
      1. POIncrease query speed in PostgreSQL
      1. USWolph
    3. COWhat version of postgres are you using? 8.4 released last year has some significant performance improvements.
      singulars
      1. POIncrease query speed in PostgreSQL
      1. UScrowne
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload