3 ms·
"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,
by 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 agoThanks, 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.