Tuning Postgres Queries
garysieling.com
garysieling.com
For example: Your understanding of TRUNCATE is incorrect. It does, in fact, release the disk space immediately (almost; see followup by 'jeffdavis). I just a couple of weeks ago used TRUNCATE to free up over 50 gbytes from one of my databases, and didn't need to VACUUM anything. One minute, the tables and their indexes weighed tens of gibibytes. Seconds later, they weighed tens of kibibytes. In between? "BEGIN; TRUNCATE TABLE foo; TRUNCATE TABLE bar; COMMIT;"
Technically not, because postgres may still hold an open file handle until the next checkpoint. From a practical standpoint that's not an issue, except that it can cause confusion if you are monitoring disk space very closely.
But you are right that he is pretty far off: there is no need to vacuum and it's an O(1) operation.
I was unclear in what behavior we saw. The issue isn't that truncate isn't O(1), but that work that occurs after truncate has wildly inconsistent behavior in time (i.e. you see the same query on the same dataset take 10x the time).
To me, that suggests that either Postgres is doing some work in the background, which hits at a random time, or that some query that is expecting statistics to have updated.
I've been working with Postgres for a year or so now, but if you search for "Postgres tuning", you get a lot of articles on how to use explain, tune memory parameters, how parsing works, etc. Oracle has so many features that you can tinker with, if you sit with a query and try to make it faster, it feels like there are a lot more options.
This is starting point I've found so far- http://stackoverflow.com/questions/1540877/guides-for-postgr...
That's not true. Although it does obey some transactional semantics to avoid very strange things, it ultimately replaces the underlying files with one empty file.
So, TRUNCATE is an O(1) operation and there is no need to VACUUM after a TRUNCATE.
"Bitmap indexes are awesome, but good luck figuring out how to make them turn on."
They are enabled by default, and I've noticed that the planner is actually pretty aggressive about choosing them, so this one surprised me. There are cases where the planner can't choose a bitmap index scan plan, or maybe where it can but is just way off, but without an example it's hard to explain (or improve) the situation.
The optimizer is definitely aggressive about using bitmap indexes, what I'm after is "how could I creatively restructure this query to make them be used," which is likely hard for anyone to comment on without seeing what I'm doing.
I think there may still be some confusion. DELETE is an O(n) operation that actually examines the tuples; TRUNCATE is an O(1) operation that doesn't look at any tuples.
The most common reason would be license costs, at least officially, but in some cases clients have been so feed up with the oracle way of doing business that didn't really care about cost as long as they got rid of oracle.