Skip site navigation (1) Skip section navigation (2)

First query slow, subsequent queries fast

From: Dmitry Karasik <dmitry(at)karasik(dot)eu(dot)org>
To: pgsql-novice(at)postgresql(dot)org
Subject: First query slow, subsequent queries fast
Date: 2004-08-31 07:44:16
Message-ID: 84eklnn44v.fsf@plab.ku.dk (view raw or flat)
Thread:
Lists: pgsql-novice
Hello all,

I observe a strange situation where if I issue a query it is slow, but
subsequent queries are fast. Obviously there are some caching schemes in
effect, but my question is rather about why might the first query be 
_that_ slow. I realize that the query itself might be flawed, so probably
you can come up with some input. Here it is:

# query #1:

# explain analyze select * from messages where id in ( select msgkey
from queues where username like 'alk%' and hostname like '%' and time > '26
July 2004' and time < '26 August 2004' and inbound = 1);
                                                                                                              QUERY PLAN                                                                                                              
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=79.40..82.46 rows=1 width=64) (actual
 time=80801.543..99005.053 rows=1996 loops=1)
   ->  HashAggregate  (cost=79.40..79.40 rows=1 width=4) (actual
 time=80789.461..80809.255 rows=1996 loops=1)
         ->  Index Scan using queues_idx_username on queues
 (cost=0.00..79.39 rows=3 width=4) (actual time=110.044..80741.483
 rows=10320 loops=1)
               Index Cond: ((username >= 'alk'::text) AND (username <
 'all'::text))
               Filter: ((username ~~ 'alk%'::text) AND (hostname ~~
 '%'::text) AND ("time" > '2004-07-26 00:00:00+02'::timestamp with time
 zone) AND ("time" < '2004-08-26 00:00:00+02'::timestamp with time zone)
 AND (inbound = 1))
   ->  Index Scan using messages_pkey on messages  (cost=0.00..3.05 rows=1
 width=64) (actual time=9.094..9.100 rows=1 loops=1996)
         Index Cond: (messages.id = "outer".msgkey)
 Total runtime: 99009.470 ms
(8 rows)


# query #2:

# explain analyze select * from messages where id in ( select msgkey
from queues where username like 'alk%' and hostname like '%' and time > '26
July 2004' and time < '26 August 2004' and inbound = 1);
                                                                                                              QUERY PLAN                                                                                                              
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=79.40..82.46 rows=1 width=64) (actual
 time=155.644..199.876 rows=1996 loops=1)
   ->  HashAggregate  (cost=79.40..79.40 rows=1 width=4) (actual
 time=155.604..162.959 rows=1996 loops=1)
         ->  Index Scan using queues_idx_username on queues
 (cost=0.00..79.39 rows=3 width=4) (actual time=0.216..141.122 rows=10320
 loops=1)
               Index Cond: ((username >= 'alk'::text) AND (username <
 'all'::text))
               Filter: ((username ~~ 'alk%'::text) AND (hostname ~~
 '%'::text) AND ("time" > '2004-07-26 00:00:00+02'::timestamp with time
 zone) AND ("time" < '2004-08-26 00:00:00+02'::timestamp with time zone)
 AND (inbound = 1))
   ->  Index Scan using messages_pkey on messages  (cost=0.00..3.05 rows=1
 width=64) (actual time=0.012..0.014 rows=1 loops=1996)
         Index Cond: (messages.id = "outer".msgkey)
 Total runtime: 201.371 ms
(8 rows)



-- 
Sincerely,
	Dmitry

--- www.karasik.eu.org ---



In response to

pgsql-novice by date

Next:From: Pradeepkumar, Pyatalo (IE10)Date: 2004-09-01 11:43:08
Subject: Applicationl Crash - Connection reset by peer !!!
Previous:From: Pradeepkumar, Pyatalo (IE10)Date: 2004-08-31 06:28:21
Subject: Re: viewing function source code

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group