Hi,
Assuming DB is quiescent.
And if you run?
select count(*) from articles where article_id between %s and %s
ie without reading json, is your buffers hit count increasing?
20 000 8K blocks *2 is 500MB , should be in RAM after the first run.
Fast:
read=710 I/O Timings: read=852.547 ==> 1.3 ms /IO
800 IO/s some memory, sequential reads or a good raid layout.
Slow:
read=5244 I/O Timings: read=24507.621 ==> 4.7 ms /IO
200 IO/s more HD reads? more seeks? slower HD zones ?
Maybe you can play with PG cache size.
> I think reindex will improve the heap access..and maybe the index access too. I don't see why it would be bloated without UPDATE/DELETE, but you could check to see if its size changes significantly after reindex.
I tried REINDEX, and size of PK index changed from 2579 to 1548 MB.
But test don't show any significant improvement from what it was. May be read speed for the "big" range became just slightly faster in average.
Vlad