Re: Select count(*) on a 2B Rows Tables Takes ~20 Hours - Mailing list pgsql-performance

From Laurenz Albe
Subject Re: Select count(*) on a 2B Rows Tables Takes ~20 Hours
Date
Msg-id 53497a4c61a606e0b144b9f9c1426536e4e1f358.camel@cybertec.at
Whole thread Raw
In response to Re: Select count(*) on a 2B Rows Tables Takes ~20 Hours  (Fred Habash <fmhabash@gmail.com>)
Responses Re: Select count(*) on a 2B Rows Tables Takes ~20 Hours  ("Schneider, Jeremy" <schnjere@amazon.com>)
List pgsql-performance
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.189rows=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



pgsql-performance by date:

Previous
From: Fred Habash
Date:
Subject: Re: Select count(*) on a 2B Rows Tables Takes ~20 Hours
Next
From: Nicolas Paris
Date:
Subject: LEFT JOIN LATERAL optimisation at plan time