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

Stephan,

you cache is too low :) Try to increase shared_buffers, for example,
for 2Gb I'd set it to 100,000

On Wed, 7 Dec 2005, Stephan Vollmer wrote:

> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>

     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83