Postgres Pro
Downloads
Home   >   mailing lists

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

From Fred Habash
Subject Re: Select count(*) on a 2B Rows Tables Takes ~20 Hours
Date
Msg-id CADpeV5zJNzy10g_-NZ6Y_ppJy_CoJ0o2X=mapDjrpUmJojd0hg@mail.gmail.com
Whole thread Raw
In response to Re: Select count(*) on a 2B Rows Tables Takes ~20 Hours  (Andres Freund <andres@anarazel.de>)
Responses Re: Select count(*) on a 2B Rows Tables Takes ~20 Hours
List pgsql-performance
Tree view
Buffers: shared hit=72620045 read=45,297,330
I/O Timings: read=57,489,958.088
Execution time: 61,141,110.516 ms  

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? 

Thanks 

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)
Time: 61141132.309 ms
refpep=>
refpep=>
refpep=>
Screen session test_pg on ip-10-241-48-178 (system load: 0.00 0.00 0.00)                                                                                                             Sun 16.09.2018 14:52       
Screen sess 


pgsql-performance by date:

Previous
From: Fred Habash
Date:
Subject: Re: How Do You Associate a Query With its Invoking Procedure?
Next
From: Laurenz Albe
Date:
Subject: Re: Select count(*) on a 2B Rows Tables Takes ~20 Hours