FAQ
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.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)
* 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.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)
*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

Search Discussions

  • Kevin Grittner at Jun 6, 2011 at 3:49 pm
    First off, this is posted to the wrong list -- this list is for
    discussion of development of the PostgreSQL product. There is a
    list for performance questions where this belongs:
    pgsql-performance@postgresql.org. I'm moving this to the
    performance list with a blind copy to the -hackers list so people
    know where the discussion went.

    Nick Raj wrote:
    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)
    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?
    Because an access to a RAM buffer is much, much faster than a disk
    access.
    Which option will be true?
    It depends entirely on how much of the data needed for the query is
    cached. Sometimes people will run a set of queries to "warm" the
    cache before letting users in.
    MY postgresql.conf file having setting like this (this is original
    setting, i haven't modify anything)
    shared_buffers = 28MB
    #work_mem = 1MB # min 64kB
    #maintenance_work_mem = 16MB # min 1MB
    If you're concerned about performance, these settings (and several
    others) should probably be adjusted:

    http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

    -Kevin

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJun 6, '11 at 3:00p
activeJun 6, '11 at 3:49p
posts2
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Kevin Grittner: 1 post Nick Raj: 1 post

People

Translate

site design / logo © 2022 Grokbase