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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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





[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux