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. On Sat, Sep 22, 2018 at 12:32 PM Vladimir Ryabtsev <greatvo...@gmail.com> wrote: > > 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 > >
