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

Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?

From: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
To: Stefan Keller <sfkeller(at)gmail(dot)com>
Cc: Andy Colson <andy(at)squeakycode(dot)net>, pgsql-performance(at)postgresql(dot)org
Subject: Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
Date: 2012-03-01 16:34:32
Message-ID: CAMkU=1wPQRB=nm+D5WwXe8ptbOdTvRwTS1T3DNYH0sSqRR8eBw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, Feb 28, 2012 at 12:48 PM, Stefan Keller <sfkeller(at)gmail(dot)com> wrote:
> Hi
>
> 2012/2/28 Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>> It is hard to figure out what problem you are facing.  Is your data
>> not getting loaded into cache, or is it not staying there?
>
> One could say both:
> I'd like to warm up the cache befor hand in order to speed up the
> first query right away.
> And it's not staying there because when there comes a second slightly
> different query it's slow again and I would expect that the tuples of
> that table stay.

Only the pages needed for a given query are loaded in the first place.
 So even if they do stay, a new query that needs different pages
(because it accesses a different part of the index, and of the table)
won't find them already loaded, except by accident.

>
>>> Just after the second query. You can try it yourself online here:
>>> http://bit.ly/A8duyB
>
> I should have said after the first query.
>
>> The second instance of the exact same query is fast.
>
> Right.
>
>> How long until all similar but not identical queries are fast?
>
> Good question. Can't tell for sure because it not so easy to make it repeatable.
> I tested the following:
>
> SELECT count(*) FROM osm_point WHERE tags @> 'amenity=>restaurant'
>
> SELECT count(*) FROM osm_point WHERE tags @> 'cuisine=>pizza'
>
> SELECT count(*) FROM osm_point WHERE tags @> 'tourism=>hotel'
>
> SELECT count(*) FROM osm_point WHERE tags @> 'historic=>castle'
>
> SELECT count(*) FROM osm_point WHERE tags @> 'natural=>peak'
> AND to_number(ele, '9999') >= 4000
>
> I would say that after the 4th query it remains fast (meaning less
> than a second).

Hmm.  I ran out of example queries before they started being fast.

Cheers,

Jeff

In response to

pgsql-performance by date

Next:From: Daniele VarrazzoDate: 2012-03-01 16:40:14
Subject: Bad estimation for "where field not in"
Previous:From: Marcin MirosławDate: 2012-03-01 12:19:03
Subject: Re: [planner] Ignore "order by" in subselect if parrent do count(*)

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