Re: Why could different data in a table be processed with different performance?
> Since you have a very big toast table, given you are using spinning disks, I think that increasing the block size will bring benefits. But will it worsen caching? I will have lesser slots in cache. Also will it affect required storage space? >> consecutive runs with SAME parameters do NOT hit the disk, only the first one does, consequent ones read only from buffer cache. > I m a bit confused.. every query you pasted contains 'read': >Buffers: shared hit=50 read=2378 > and 'read' means you are reading from disk (or OS cache). Or not? Yes, sorry, it was just my misunderstanding of what is "consecutive". To make it clear: I iterate over all data in table with one request and different parameters on each iteration (e.g. + 5000 both borders), in this case I get disk reads on each query run (much more reads on "slow" range). But if I request data from an area queried previously, it reads from cache and does not hit disk (both ranges). E.g. iterating over 1M of records with empty cache takes ~11 minutes in "fast" range and ~1 hour in "slow" range, while on second time it takes only ~2 minutes for both ranges (if I don't do drop_caches). Regards, Vlad
reference regarding write load during different stages of checkpoint
I'm hoping to find a document I once read about the write load. As best I can recall, it looked something like this: . at beginning of (spread) checkpoint, larger than average write load to pg_wal/, due to full_page_writes; . during most of checkpoint, decreasing WAL due to FPW, . towards end of checkpoint, increased writes to table data base/, due to fsync(); . assuming the next checkpoint doesn't start immediately, quiescent period, due to clean OS buffers; This isn't very important, but I hadn't seen that described before, and I think there was more detail than I can remember. I've been hoping for awhile to run across it and not able to find it. It probably dates back to 8.3/9.0 days and maybe disappeared. Does anyone know what I'm talking about or where I can find it? Thanks, Justin
