Suboptimal generated count query

[markusheiden]

Spring Data uses count(id) for count queries. That is suboptimal if the ID is not part of the index that is used for the query.

I suggest using count(*) instead.

Example:

KeywordRepository

Flux<Keyword> findAllByLanguageAndTextLike(String language, String search, Pageable pageable);

uses

CREATE UNIQUE INDEX idx_keyword_language_text ON keyword (language, text);

SELECT keyword.id, ... FROM keyword WHERE keyword.language = ? AND (keyword.text LIKE ?) ORDER BY keyword.text ASC LIMIT 15

SELECT COUNT(keyword.id) FROM keyword WHERE keyword.language = ? AND (keyword.text LIKE ?)

The main query is mainly served by the index only (except for finally fetching the selected rows). The count query though cannot be served by the index only, because the keyword ID is not part of it. Thus that query is executed by a slower table scan. That requires the whole table additionally to be loaded instead of just the index.

We are using Spring Boot 3.1.4 with PostgreSQL 15.2.

Query plan of count(id):

Finalize Aggregate  (cost=6773239.94..6773239.95 rows=1 width=8)
  ->  Gather  (cost=6773239.73..6773239.94 rows=2 width=8)
        Workers Planned: 2
        ->  Partial Aggregate  (cost=6772239.73..6772239.74 rows=1 width=8)
              ->  Parallel Seq Scan on keyword  (cost=0.00..6688995.90 rows=33297530 width=8)
                    Filter: ((text ~~ '%'::text) AND (language = 'de'::bpchar))

Query plan of count(*):

Finalize Aggregate  (cost=2935976.84..2935976.85 rows=1 width=8)
  ->  Gather  (cost=2935976.62..2935976.83 rows=2 width=8)
        Workers Planned: 2
        ->  Partial Aggregate  (cost=2934976.62..2934976.63 rows=1 width=8)
              ->  Parallel Index Only Scan using idx_keyword_language_text on keyword  (cost=0.57..2851732.80 rows=33297530 width=0)
                    Index Cond: (language = 'de'::bpchar)
                    Filter: (text ~~ '%'::text)

[mp911de]

Moving this ticket into Relational as the R2DBC project was merged here. Thanks a lot for bringing this to our attention. We'll have a look and it makes clearly sense to use * as the database can optimize and we are not tied to any particular column for count queries.

[mp911de]

That's changed now.