Hi,
I am using postgresql 8.4.6. I have made an index on my data-type that is
working fine. I mean output is coming properly.
When i execute the query first time, query takes a quite longer time but
second time execution of the same query takes very less time (despite
execution plan is same)
This is my first time execution of query
*explain analyze select * from vehicle_st1 where '(116.3,39.3,2007/06/11
11:11:11),(117.2,39.8,2007/09/13 11:11:11)' @ stpoint;*
QUERY
PLAN
-
Index Scan using st1 on vehicle_st1 (cost=0.00..8226.36 rows=2096
width=66) (actual time=65.962..1587.627 rows=9069 loops=1)
Index Cond: ('(116.30,39.30,2007-06-11
11:11:11+05:30),(117.20,39.80,2007-09-13 11:11:11+05:30)'::ndpoint
@ stpoint)
* Total runtime: 1594.446 ms*
(3 rows)
Second time
*explain analyze select * from vehicle_st1 where '(116.3,39.3,2007/06/11
11:11:11),(117.2,39.8,2007/09/13 11:11:11)' @ stpoint;*
QUERY
PLAN
-
Index Scan using st1 on vehicle_st1 (cost=0.00..8226.36 rows=2096
width=66) (actual time=0.156..14.316 rows=9069 loops=1)
Index Cond: ('(116.30,39.30,2007-06-11
11:11:11+05:30),(117.20,39.80,2007-09-13 11:11:11+05:30)'::ndpoint
@ stpoint)
*Total runtime: 19.525 ms*
(3 rows)
Third time
*It gives 17.148 ms*
Fourth time
*It gives 25.102 ms*
MY postgresql.conf file having setting like this (this is original setting,
i haven't modify anything)
#--
# RESOURCE USAGE (except WAL)
#--
# - Memory -
shared_buffers = 28MB# min 128kB
# (change requires restart)
#temp_buffers = 8MB# min 800kB
#max_prepared_transactions = 0# zero disables the feature
# (change requires restart)
# Note: Increasing max_prepared_transactions costs ~600 bytes of shared
memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.
#work_mem = 1MB# min 64kB
#maintenance_work_mem = 16MB# min 1MB
#max_stack_depth = 2MB# min 100kB
Why the same plan giving different execution time? (Reason may be data gets
buffered (cached) for the second time execution) Why there is so much
difference?
I want to know the estimate correct time of this query then which option is
true?
1. First one(1594 ms) when application just started, all buffer are empty.
But in practical situation they are not fully empty.
2. I have to taken the stable execution time (19-21 ms).
3. Average down these four execution time.
Which option will be true?
Thanks
Nick
