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@xxxxxxxxxx, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83
