pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedFirst query is slow, subsequent queries fast
3+ messages / 2 participants
[nested] [flat]
* First query is slow, subsequent queries fast
@ 2005-12-07 10:46 Stephan Vollmer <svollmer@gmx.de>
2005-12-07 10:55 ` Re: First query is slow, subsequent queries fast Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 1 reply; 3+ messages in thread
From: Stephan Vollmer @ 2005-12-07 10:46 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: First query is slow, subsequent queries fast
2005-12-07 10:46 First query is slow, subsequent queries fast Stephan Vollmer <svollmer@gmx.de>
@ 2005-12-07 10:55 ` Oleg Bartunov <oleg@sai.msu.su>
2005-12-07 18:33 ` Re: First query is slow, subsequent queries fast Stephan Vollmer <svollmer@gmx.de>
0 siblings, 1 reply; 3+ messages in thread
From: Oleg Bartunov @ 2005-12-07 10:55 UTC (permalink / raw)
To: Stephan Vollmer <svollmer@gmx.de>; +Cc: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: First query is slow, subsequent queries fast
2005-12-07 10:46 First query is slow, subsequent queries fast Stephan Vollmer <svollmer@gmx.de>
2005-12-07 10:55 ` Re: First query is slow, subsequent queries fast Oleg Bartunov <oleg@sai.msu.su>
@ 2005-12-07 18:33 ` Stephan Vollmer <svollmer@gmx.de>
0 siblings, 0 replies; 3+ messages in thread
From: Stephan Vollmer @ 2005-12-07 18:33 UTC (permalink / raw)
To: pgsql-performance
Hi Oleg, thanks for your quick reply!
Oleg Bartunov wrote:
> you cache is too low :) Try to increase shared_buffers, for example,
> for 2Gb I'd set it to 100,000
Ok, I set shared_buffers to 100000 and indeed it makes a big
difference. Other queries than the ones I mentioned are faster, too.
Thanks very much for your help,
- Stephan
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-12-07 18:33 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-07 10:46 First query is slow, subsequent queries fast Stephan Vollmer <svollmer@gmx.de>
2005-12-07 10:55 ` Oleg Bartunov <oleg@sai.msu.su>
2005-12-07 18:33 ` Stephan Vollmer <svollmer@gmx.de>
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