Re: Select count(*) on a 2B Rows Tables Takes ~20 Hours

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