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

Re: [PERFORM] Interesting incosistent query timing

From: Ernest E Vogelsinger <ernest(at)vogelsinger(dot)at>
To: nikolaus(at)dilger(dot)cc
Cc: pgsql-performance(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: Re: [PERFORM] Interesting incosistent query timing
Date: 2003-06-17 23:01:09
Message-ID: 5.1.1.6.2.20030618005902.02e31a38@mail.vogelsinger.at (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performance
At 00:45 18.06.2003, nikolaus(at)dilger(dot)cc said:
--------------------[snip]--------------------
>Thanks for providing the additional information that
>the table has 2.3 million rows.
>
>See during the first execution you spend most of the
>time scanning the index id_mdata_dictid_string.  And
>since that one is quite large it takes 1500 msec to
>read the index from disk into memory.
>
>For the second execution you read the large index from
>memory.  Therfore it takes only 10 msec.
>
>Once you change the data you need to read from disk
>again and the query takes a long time.
--------------------[snip]-------------------- 

I came to the same conclusion - I installed a cron script that performs a
select against that index on a regular basis (3 minutes). After that even
the most complex queries against this huge table go like whoosssshhh ;-)

Would be interesting what one could do to _not_ have to take this basically
clumsy approach...


-- 
   >O     Ernest E. Vogelsinger
   (\)    ICQ #13394035
    ^     http://www.vogelsinger.at/



Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2003-06-17 23:21:41
Subject: Re: Postgres Connections Requiring Large Amounts of Memory
Previous:From: nikolausDate: 2003-06-17 22:45:38
Subject: Re: [PERFORM] Interesting incosistent query timing

pgsql-general by date

Next:From: Joseph ShraibmanDate: 2003-06-17 23:03:37
Subject: EFFECTIVE_CACHE_SIZE
Previous:From: ArguileDate: 2003-06-17 23:00:07
Subject: Re: Link to Bruce M's fs performance tuning doc

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