6

My database version is postgresql 9.5.

create table if not exists request_log
(
    id               bigserial not null constraint app_requests_pkey  primary key,
    request_date     timestamp not null,
    ip               varchar(50),
    start_time       timestamp,
    application_name varchar(200),
    request_path     text,
    display_url      text,
    username         varchar(50)
);

I have a table that icludes incoming http request informations. The id column is primary key and index. The table has no relation.

So I have 72320081 rows in this table. And when I run the count query to get count of table, select count(id) from request_log; query takes 3-5 minutes.

The explain(analyze, buffers, format text) result for this request is:

Aggregate  (cost=3447214.71..3447214.72 rows=1 width=0) (actual time=135575.947..135575.947 rows=1 loops=1)
  Buffers: shared hit=96 read=2551303
  ->  Seq Scan on request_log  (cost=0.00..3268051.57 rows=71665257 width=0) (actual time=2.517..129032.408 rows=72320081 loops=1)
        Buffers: shared hit=96 read=2551303
Planning time: 0.067 ms
Execution time: 135575.988 ms

This is very bad performance for me. I could not get reports from table from web applications because of performance.

My server hardware sources are:

  • OS: Linux ubuntu server 16, On Vmware
  • 4 core cpu
  • Mem 6Gb
  • HDD 120 Gb

I run the queries at nights, that there are no users on database, but slow. How can solve this problem?

3
  • 1
    You query needs to read 19GB from the harddisk and does this in 135 seconds. That's a throughput of about 150MB per second which is pretty much what a spinning harddisk (7200 rpm) can deliver (and VMWare isn't making that faster). If you can't improve the hardware, you will need some other tricks (e.g. what Laurenz suggested). The number of blocks seems to be reasonable, but you might try to do a vacuum analyze full nevertheless (if you have enough free space) to see if that improves performance of the Seq Scan.
    – user1822
    Dec 11, 2019 at 8:26
  • @a_horse_with_no_name sorry how do you calculate to come to 19GB of data?
    – Pat
    Nov 29, 2022 at 12:24
  • 1
    @pat read=2551303 means reading 2551303 blocks of 8kb each. So 2551303 * 8192 bytes = 20900274176 bytes which is roughly 19GB
    – user1822
    Nov 29, 2022 at 12:28

3 Answers 3

5

Counting rows is slow, because all rows of the table have to be visited.
Counting id is even slower, because PostgreSQL first has to check if id is NULL or not (NULL values are not counted).

There are a few options to speed things up:

  • Use a more recent version of PostgreSQL.

    Then you can get parallel query, which will make the execution even more expensive, but faster.

  • Use the index on id and keep the table well vacuumed.

    Then you can get an index only scan.

  • Use an extra table with a counter that gets updated on every data modifying statement on the large table using a trigger.

Have a look at my blog post for an in-depth discussion.

2
  • Assuming that is a write only table could the serial sequence attached be a driver of how many rows it has? @Laurenz Albe
    – Imanol Y.
    Dec 12, 2019 at 17:25
  • 1
    It could be an indicator, but if some inserts get rolled back, the associated sequence numbers will be lost. I would trust the table statistics more. Dec 13, 2019 at 6:50
0

something else you can try is use MAX(id) as id field is a auto-incrementing integer with a range index

this assumes records are never deleted and there are no gaps in the id.

select Max(id) from request_log where id >70,000,000

additional note Index only scan work on 9.6 or later..

0

If you only need an estimated count instead of the actual count, you can use the estimate that PostgreSQL uses for query planning:

SELECT reltuples::bigint
FROM pg_catalog.pg_class
WHERE relname = 'mytable';

Reference: https://www.cybertec-postgresql.com/en/postgresql-count-made-fast/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.