5 ms·

This reads more like "a bunch of things I don't understand about the PostgreSQL query planner" than it does "tuning" anything. Pretty much everything you're des

by rosser 13y ago

This reads more like "a bunch of things I don't understand about the PostgreSQL query planner" than it does "tuning" anything. Pretty much everything you're describing is documented in The Fine Manual. 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;"

jeffdavis 13y ago
"[TRUNCATE] does, in fact, release the disk space immediately — immediately upon 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.
- rosser 13y ago
  Interesting. I've never encountered that behavior, and it doesn't appear to be mentioned in a quick perusal of the manual page. The scenario you describe does make sense, and definitely could happen, though the impact would indeed be momentary and transient. Thanks for the clarification!
- garysieling 13y ago
  Thanks, I've updated the post. 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.
garysieling 13y ago
Yeah, that's fair. I updated the post for the part that was wrong. 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-postgresql-query-tuning http://stackoverflow.com/questions/1540877/guides-for-postgr...

by rosser 13y ago

jeffdavis 13y ago

"[TRUNCATE] does, in fact, release the disk space immediately — immediately upon 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.

rosser 13y ago
Interesting. I've never encountered that behavior, and it doesn't appear to be mentioned in a quick perusal of the manual page. The scenario you describe does make sense, and definitely could happen, though the impact would indeed be momentary and transient. Thanks for the clarification!
garysieling 13y ago
Thanks, I've updated the post. 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.

garysieling 13y ago

Yeah, that's fair. I updated the post for the part that was wrong. 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-postgresql-query-tuning http://stackoverflow.com/questions/1540877/guides-for-postgr...