Fred Habash wrote: > If I'm reading this correctly, it took 57M ms out of an elapsed time of 61M ms to read 45M pages from the filesystem? > If the average service time per sarr is < 5 ms, Is this a case of bloated index where re-indexing is warranted? > > explain (analyze,buffers,timing,verbose,costs) > select count(*) from jim.pitations ; > QUERY PLAN > ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > Aggregate (cost=72893810.73..72893810.74 rows=1 width=8) (actual time=61141110.437..61141110.437 rows=1 loops=1) > Output: count(*) > Buffers: shared hit=72620045 read=45297330 > I/O Timings: read=57489958.088 > -> Index Only Scan using pit_indx_fk03 on jim.pitations (cost=0.58..67227187.37 rows=2266649344 width=0) (actual time=42.327..60950272.189 rows=2269623575 loops=1) > Output: vsr_number > Heap Fetches: 499950392 > Buffers: shared hit=72620045 read=45297330 > I/O Timings: read=57489958.088 > Planning time: 14.014 ms > Execution time: 61,141,110.516 ms > (11 rows) 2269623575 / (45297330 + 72620045) ~ 20, so you have an average 20 items per block. That is few, and the index seems indeed bloated. Looking at the read times, you average out at about 1 ms per block read from I/O, but with that many blocks that's of course still a long time. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
