

Tuning Postgres Queries - garysieling
http://www.garysieling.com/blog/tuning-postgres-queries

======
rosser
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
"[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.

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

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

------
MaxGabriel
Why did you switch from Oracle to Postgres?

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

