> 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