On Tue, Feb 28, 2012 at 12:48 PM, Stefan Keller <sfkeller(at)gmail(dot)com> wrote:
> 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:
> I should have said after the first query.
>> The second instance of the exact same query is fast.
>> 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.
In response to
pgsql-performance by date
|Next:||From: Daniele Varrazzo||Date: 2012-03-01 16:40:14|
|Subject: Bad estimation for "where field not in"|
|Previous:||From: Marcin Mirosław||Date: 2012-03-01 12:19:03|
|Subject: Re: [planner] Ignore "order by" in subselect if parrent