postgres_row count 성능 개선 방법

-- 일반
explain analyze select count(*) as exact_count from "Bid" b;

-- 빠른 것
explain analyze select reltuples from pg_catalog.pg_class c where c.relname = 'Bid';

postgres 메타 데이터로 보이는 것에서 reltuples를 바로 가져오면 된다.

 

일반 쿼리로 실행했을 경우

QUERY PLAN                                                                                                                               |
-----------------------------------------------------------------------------------------------------------------------------------------+
Finalize Aggregate  (cost=18754.93..18754.94 rows=1 width=8) (actual time=144.193..145.507 rows=1 loops=1)                               |
  ->  Gather  (cost=18754.71..18754.92 rows=2 width=8) (actual time=144.182..145.500 rows=3 loops=1)                                     |
        Workers Planned: 2                                                                                                               |
        Workers Launched: 2                                                                                                              |
        ->  Partial Aggregate  (cost=17754.71..17754.72 rows=1 width=8) (actual time=136.608..136.609 rows=1 loops=3)                    |
              ->  Parallel Seq Scan on "Bid" b  (cost=0.00..16484.77 rows=507977 width=0) (actual time=0.010..88.742 rows=406382 loops=3)|
Planning Time: 0.049 ms                                                                                                                  |
Execution Time: 145.540 ms                                                                                                               |

 

빠른 쿼리로 실행했을 경우

QUERY PLAN                                                                                                                           |
-------------------------------------------------------------------------------------------------------------------------------------+
Index Scan using pg_class_relname_nsp_index on pg_class c  (cost=0.27..8.29 rows=1 width=4) (actual time=0.014..0.015 rows=1 loops=1)|
  Index Cond: (relname = 'Bid'::name)                                                                                                |
Planning Time: 0.060 ms                                                                                                              |
Execution Time: 0.028 ms                                                                                                             |

 

출처

https://stackoverflow.com/questions/7943233/fast-way-to-discover-the-row-count-of-a-table-in-postgresql