Different execution time for same plan - Mailing list pgsql-hackers

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