1

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?

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. – a_horse_with_no_name Dec 11 '19 at 8:26
1

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 '19 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. – Laurenz Albe Dec 13 '19 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..

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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