SQL_NO_CACHE for Postgresql?

Question

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?

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
– Frank Farmer, Commented May 26, 2011 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
– MarkR, Commented May 27, 2011 at 8:23
Postgresql doesn't use Direct IO, just the standard read/write calls. — araqnid
– araqnid, Commented May 27, 2011 at 12:43

MarkR · Accepted Answer · 2011-05-26 22:59:58Z

10

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/ )

answered May 26, 2011 at 22:59

MarkR

63.8k15 gold badges120 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

davek Over a year ago

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".

Collectives™ on Stack Overflow

SQL_NO_CACHE for Postgresql?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related