First query is slow, subsequent queries fast

Поиск
Список
Период
Сортировка
От Stephan Vollmer
Тема First query is slow, subsequent queries fast
Дата
Msg-id 4396BD7B.1050708@gmx.de
обсуждение исходный текст
Ответы Re: First query is slow, subsequent queries fast  (Oleg Bartunov <oleg@sai.msu.su>)
Список 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 по дате отправления:

Предыдущее
От: "Rick Schumeyer"
Дата:
Сообщение: table partitioning: effects of many sub-tables (was COPY too slow...)
Следующее
От: Oleg Bartunov
Дата:
Сообщение: Re: First query is slow, subsequent queries fast