count(*) queries are too slow even with an index only scan

Question

We currently have a PostgreSQL table that stores audit logs similar to the following:

id	portal_id	created_at	action	caused_by_id	caused_by_type
1	1	2019-09-19 09:39:48.827924	user-logged-in	1	User
1	1	2019-09-19 09:39:48.827924	send-email	1	System

The table has another 8 columns and the interface that we have allows the user to specify any combination of filters, i.e., created_at with action, action only, action with caused_by_id, etc

The problem that we have is with count(*) or count(1). We have around 8M rows and growing and it currently needs around 1 second and 900ms to respond on a 16vCPU server with 128 GB ram and around 6000 IOPS.

All other queries are fine since we have pagination in place that the user is not able to paginate more than 200 records.

We got a slow query and we optimized it to the level that EXPLAIN (ANALYZE, BUFFERS) shows that an INDEX ONLY SCAN has been used.

Please keep in mind that:

• Every time that a user searches with a filter we should have in place the appropriate btree index to achieve an index only scan other wise the query is beyond slow. Creating all combinations as index is only impossible due to the fact of column ordering while creating the index.
• Our index might end up having exactly the same size as the main table.

Questions:

• Is there a way to optimize the aggregation COUNT query to give us the results faster or we should move to a different infrastructure?
• How do big companies like Salesforce / Freshdesk give you results even on million of records in sub-second time while applying any kind of filters? Are they using a different kind of DB?

Additional details:

PostgreSQL Version: 13.7
Server: 16vCPU - 128 GB RAM - 6400 IOPS
Platform: Microsoft Azure Flexible Server

Table audit

create table audit (
id             bigint,
portal_id      integer,
action         varchar(255),
caused_by_id   integer,
caused_by_type varchar(255),
created_at     timestamp
);

Indexes:

We tried multiple combinations. A sample will be the following:

create index audit_caused_by_id_portal_id
on audit_2 (caused_by_id, portal_id);

Query:

select count(1)
 from (select "portal_id"
  from "crm"."audit"
  where (("portal_id" = 1) and (not (lower("action") = 'send-email')))
    and (not ("caused_by_id" in (1)))) x2;

EXPLAIN (ANALYZE, BUFFERS) result here.

Answer 1

To answer the PostgreSQL side of things:

If you count a lot of rows, that is always slow.

To potentially get an index-only scan (which will also not be lightening fast), you'd have to create an index specifically tailored to that individual query:

CREATE INDEX ON crm.audit (portal_id, lower(action), caused_by_id);

Then you'd have to VACUUM the table.

The true solution is elsewhere: don't run that query. Don't show a total count of query results. Nobody needs that.

"Other systems" don't do that either. Do you believe that a web search engine's hit count is an exact number? If you need to show a number, show the estimated result count that you can get by using the EXPLAIN statement in PostgreSQL.

Answer 2

Are they using a different kind of DB?

No, yes, maybe - it depends on what you mean by this question. Big companies do use PostgreSQL, but not all do. When they don't, they likely still use the same type of database system, a Relational Database Management System (RDBMS). Some use a columnar database system in conjunction or as a subset to their RDBMS, for improved aggregation performance.

How do big companies like Salesforce / Freshdesk give you results even on million of records in sub-second time while applying any kind of filters?

Three ways they can accomplish this:

1. Pre-aggregate the data (e.g. data warehouse implementation) at a rate that's acceptable to the business. Depending on the business and use case, sometimes hourly or even daily is acceptable. For a metric that's already fast enough to calculate in under 2 seconds, in your use case, you could theoretically cache that aggregated result to a table potentially every minute. (Though I'd recommend proper full system testing before releasing such a change to production.)

2. Return an estimated aggregation. Some database systems offer ways to obtain an estimated aggregation for improved performance. For example, APPROX_COUNT_DISTINCT is a common function implemented in multiple database systems (SQL Server, Oracle SQL, Snowflake, Google BigQuery, etc) that is generally faster at returning an estimated distinct count.

There are other functions and methodologies for accomplishing estimated aggregations across each specific database system as well. This is an acceptable solution depending on the business and use case. For example, when you do a Google Search on the term dancing cat meme you'll notice Google gives some metrics at the top of the page: About 18,300,000 results (0.45 seconds). So even some of the largest companies, using estimated metrics, still take half a second to calculate.

3. Try columnar storage which is typically more performant for calculating aggregations. Columnar storage implements column-level compression, which is typically more efficiently compressed than traditional row storage in an RDBMS (since generally there's more commonality of the values within a column, as opposed to across a row). Different database systems offer different features and solutions to leverage columnar storage. SQL Server has a feature known as columnstore indexing, which is a simple columnar based index created against a rowstore table. Snowflake is a database system that is completely columnar. PostgreSQL has extensions people have created to add columnar storage to it. For the right use cases, columnar storage can roughly improve aggregation performance by a factor of 10x to 100x or more.

Answer 3

Using "count(1)" implies that you want to test for existance. So use the "exists" function which will stop scanning on the first match.