From: Stephan Vollmer <svollmer@gmx.de>
To: pgsql-performance@postgresql.org
Subject: First query is slow, subsequent queries fast
Date: Wed, 07 Dec 2005 11:46:19 +0100
Message-ID: <4396BD7B.1050708@gmx.de> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: svollmer@gmx.de
Subject: Re: First query is slow, subsequent queries fast
In-Reply-To: <4396BD7B.1050708@gmx.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox