On Friday, February 22, 2013, Carlo Stonebanks wrote:
My understanding of PG’s cluster is that this is a one-time command that creates a re-ordered table and doesn’t maintain the clustered order until the command is issued again. During the CLUSTER, the table is read and write locked. So, in order for me to use this I would need to set up a timed event to CLUSTER occasionally.
Correct.
The EXPLAIN ANALYZE is showing it is taking a long time to prepare the bitmap (i.e.-> Bitmap Index Scan on log_2013_01_session_idx (cost=0.00..63186.52
rows=2947664 width=0) (actual time=32611.918..32611.918 rows=2772042 loops=1)" Index Cond: (session_id = 27)" the bitmap scan is actually very fast. Jeff sasys that the bitmap is not cached, so I will assume the PG general caches being created are of general use.
To clarify the "actual time" thing, the first number is not when the node received its first row from its downstream (or when the node was started, if it has no downstream). I believe that that number is when the node produced its first row to send upstream, and 2nd number is when it produced its last row. Since a bitmap index scan only produces one "row" (the bitmap itself), these number will always be the same. In other words, the "actual time" field does not give you measure of the start-up time of the node. Indeed, there is no easy way to figure that out from the output of EXPLAIN. Or at least this is my understanding from trial and error, this doesn't seem to be documented anywhere.
What tells you that the bitmap creation is fast is that it gets much faster when run on already-cached data, so the time is going to reading in data, not turning the data into the bitmap.
I think what I need to do is figure out is:
1) Why does it take 36 seconds to set up the general index caches?
They are not general index caches, just general data caches. The index pages compete with all the other data in the system. Anyway, running the explains as "explain (analyze, buffers)" would go a long way towards figuring out why it takes so long to read the index, especially if you can set track_io_timing = on first.
And then the next question would be, once they are in the cache, why don't they stay there? For that you would have to know what other types of activities are going on that might be driving the data out of the cache.
Cheers,
Jeff