First query is slow, subsequent queries fast - Mailing list pgsql-performance

From Stephan Vollmer
Subject First query is slow, subsequent queries fast
Date
Msg-id 4396BD7B.1050708@gmx.de
Whole thread Raw
Responses Re: First query is slow, subsequent queries fast  (Oleg Bartunov <oleg@sai.msu.su>)
List pgsql-performance
Hi everybody!

This is my first posting to this list and I'm quite a PostgreSQL
newbie. My question is:

The first time I execute a query, it is very slow, but subsequent
queries are as fast as expected. I would be very glad if somebody
could explain why the first query is so slow and what I could do to
speed it up.

The query operates on a tsearch2 indexed column, but I experienced
the same issue on other tables as well, so I don't think it's a
tsearch2 issue.

To get a better overview of the queries and EXPLAIN outputs, I've
put them on a temporary website, together with the table definition
and my postgresql.conf:

<http://dblp.dyndns.org:8080/dblptest/explain.jsp>

I'm running PostgreSQL 8.1 on Windows XP SP2, Athlon64 3000+, 2 GB
RAM, 400 GB SATA HDD, 120 GB ATA HDD. The data reside on the first
HDD, the indexes in an index tablespace on the second HDD.

In the example below, the first query is still quite fast compared
to others. Sometimes the first query takes up to 9000 ms (see
website). I've run VACUUM FULL, but it didn't seem to solve the problem.

Thanks very much in advance,

- Stephan


--------------------------------------------------------
Query:
--------------------------------------------------------
SELECT keyword, overview
FROM publications
WHERE idx_fti @@ to_tsquery('default', 'linux & kernel')
ORDER BY rank_cd(idx_fti, 'linux & kernel') DESC;


--------------------------------------------------------
EXPLAIN for first query:
--------------------------------------------------------
Sort  (cost=859.89..860.48 rows=237 width=299) (actual
time=1817.962..1817.971 rows=10 loops=1)
  Sort Key: rank_cd(idx_fti, '''linux'' & ''kernel'''::tsquery)
  ->  Bitmap Heap Scan on publications  (cost=3.83..850.54 rows=237
width=299) (actual time=1817.839..1817.914 rows=10 loops=1)
        Filter: (idx_fti @@ '''linux'' & ''kernel'''::tsquery)
        ->  Bitmap Index Scan on idx_fti_idx  (cost=0.00..3.83
rows=237 width=0) (actual time=1817.792..1817.792 rows=10 loops=1)
              Index Cond: (idx_fti @@ '''linux'' & ''kernel'''::tsquery)
Total runtime: 1818.068 ms


--------------------------------------------------------
EXPLAIN for second query:
--------------------------------------------------------
Sort  (cost=859.89..860.48 rows=237 width=299) (actual
time=4.817..4.826 rows=10 loops=1)
  Sort Key: rank_cd(idx_fti, '''linux'' & ''kernel'''::tsquery)
  ->  Bitmap Heap Scan on publications  (cost=3.83..850.54 rows=237
width=299) (actual time=4.727..4.769 rows=10 loops=1)
        Filter: (idx_fti @@ '''linux'' & ''kernel'''::tsquery)
        ->  Bitmap Index Scan on idx_fti_idx  (cost=0.00..3.83
rows=237 width=0) (actual time=4.675..4.675 rows=10 loops=1)
              Index Cond: (idx_fti @@ '''linux'' & ''kernel'''::tsquery)
Total runtime: 4.914 ms

pgsql-performance by date:

Previous
From: "Rick Schumeyer"
Date:
Subject: table partitioning: effects of many sub-tables (was COPY too slow...)
Next
From: Oleg Bartunov
Date:
Subject: Re: First query is slow, subsequent queries fast