5 ms·

Tuning Postgres Queries

43 points by garysieling 13y ago

MaxGabriel 13y ago
Why did you switch from Oracle to Postgres?
- np422 13y ago
  I've helped a few clients to migrate from oracle to postgres or edb. 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.
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...
jeffdavis 13y ago
"In Postgres, [TRUNCATE is] a DELETE without a where clause." 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.
- garysieling 13y ago
  Thanks, I updated the post. 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.
  - jeffdavis 13y ago
    "Thanks, I updated the post... In Postgres, it behaves as a DELETE without a where clause..." 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.

43 points by garysieling 13y ago

MaxGabriel 13y ago

Why did you switch from Oracle to Postgres?

np422 13y ago
I've helped a few clients to migrate from oracle to postgres or edb. 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.

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

jeffdavis 13y ago

"In Postgres, [TRUNCATE is] a DELETE without a where clause." 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.

garysieling 13y ago
Thanks, I updated the post. 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.
- jeffdavis 13y ago
  "Thanks, I updated the post... In Postgres, it behaves as a DELETE without a where clause..." 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.