11

Is there a postgresql equivalent for the MySQL keyword SQL_NO_CACHE (or the SQL Server dbcc drop clean buffers) i.e. which you can simply include in a SQL statement or as part of a script?

UPDATE: this question

See and clear Postgres caches/buffers?

seems to say that the answer is "no", although it is now two years old. Are there any relevant changes in postgresql 9.0?

Community
  • 1
  • 1
davek
  • 22,499
  • 9
  • 75
  • 95
  • The OS's disk cache actually probably has more to do with this than any postgres cache. Note also that the answer on the question you linked is from Greg Smith, a well known postgres consultant/author/blogger/contributor, and he's commented on the answer as of this year. If there's anyone who knows the right answer to this question, it's him, and he says "There is no way to bypass or flush the database's cache. All you can do to clear it is restart the server." – Frank Farmer May 26 '11 at 23:08
  • @Frank if postgres uses direct IO (like InnoDB does) then its own cache is used instead of the OS cache. Most databases seems to use Direct IO. – MarkR May 27 '11 at 08:23
  • 2
    Postgresql doesn't use Direct IO, just the standard read/write calls. – araqnid May 27 '11 at 12:43

2 Answers2

9

The two things you've stated are not at all equivalent.

MySQL's SQL_NO_CACHE does NOT stop the engine from using cached data. It means that the query is not cached in the MySQL query cache.

The query cache is not the same as any disc cache the engine might have because it caches the results of queries, not blocks from the disc (or rows from a table etc). So it caches the results of a text statement.

The reason for using SQL_NO_CACHE is that you know you're going to be doing an infrequently-used query which returns a lot of data (hence would take up a lot of space in the cache).

But most MySQL installations are now advised to turn the query cache off to make more memory for page caches etc (see http://dom.as/tech/query-cache-tuner/ )

MarkR
  • 62,604
  • 14
  • 116
  • 151
  • thanks for clearing up some misunderstanding on my part. What I'm really trying to do is to analyze some decisions the optimizer takes, and want to exclude any "short-cuts" that might be possible by, for instance, ensuring that the overhead of creating a temp table is not missed by dint of it being already "there". – davek May 27 '11 at 06:53
0

No need for, cache in PostgreSQL works very different and is 100% reliable.

Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
  • 24
    I'm working on some benchmarks, and I'm trying to avoid hitting cached results. – davek May 26 '11 at 20:02
  • @davek: in this case you need to look into the `discard` command. But your benchmarks will be invalid. – Denis de Bernardy May 26 '11 at 20:30
  • Don't think reliability was what the OP was interested in. – MarkR May 26 '11 at 22:58
  • 2
    @davek: benchmarking should be done in a scenario that is as realistic as possible. Turning of caches in not realistic which means that any results obtained with the caches off are just numbers that have no meaning in the real world. – Eelke May 27 '11 at 05:38
  • 4
    @Eelke: I agree with you, although in this case I'm not benchmarking representative data loads, but rather discrete queries whose query plans I want to analyze. I just want to make sure I'm not missing any overhead. – davek May 27 '11 at 06:54
  • 1
    @davek, the state of cache does not change the results of a query plan as shown by `EXPLAIN`. The timing information from an `EXPLAIN ANALYZE`, however, may show faster times for cached data, but that should be the extent of the differences. In your case, I don't know that you need to worry about flushing the cache (esp since you're not concerned enough to reboot the OS between iterations to clear the OS'es cache). – Sean May 27 '11 at 23:08