0

I have created a user table with 10M rows (fake data generated by Python) in PostgreSQL 16. The table columns are: email (varchar 100), password(bytea from sha512), created_at(timestamp without timezone).

the created_at field has no timezone since the application only uses UTC and all the conversions to locale are done on the user's machine based on their preferences.

I've always struggled with creating indexes on timestamp data and somehow managed to create an index that made the query much worse!

the following query takes 7.5 seconds:

select date_part('year', created_at) as "year", count(*) as registered_users
from users u
group by "year"
order by "year"

It does a sequential scan, sort, and finally aggregates. The complete query plan is:

[
  {
    "Plan": {
      "Node Type": "Aggregate",
      "Strategy": "Sorted",
      "Partial Mode": "Simple",
      "Parallel Aware": false,
      "Async Capable": false,
      "Actual Rows": 24,
      "Actual Loops": 1,
      "Group Key": ["(date_part('year'::text, created_at))"],
      "Plans": [
        {
          "Node Type": "Sort",
          "Parent Relationship": "Outer",
          "Parallel Aware": false,
          "Async Capable": false,
          "Actual Rows": 10000000,
          "Actual Loops": 1,
          "Sort Key": ["(date_part('year'::text, created_at))"],
          "Sort Method": "external merge",
          "Sort Space Used": 117312,
          "Sort Space Type": "Disk",
          "Plans": [
            {
              "Node Type": "Seq Scan",
              "Parent Relationship": "Outer",
              "Parallel Aware": false,
              "Async Capable": false,
              "Relation Name": "users",
              "Alias": "u",
              "Actual Rows": 10000000,
              "Actual Loops": 1
            }
          ]
        }
      ]
    },
    "Triggers": [
    ]
  }
]

I then created an index on the year of the timestamp as:

create index year_idx on users((date_trunc('year',created_at)))

but running a very similar query now takes 36 seconds!

select date_trunc('year',created_at) as "year", count(*) as registered_users
from users u
group by "year"
order by "year"

The query plan does show that the index is being used but I'm not sure why using the index is slower than a full scan. The execution plan is:

[
  {
    "Plan": {
      "Node Type": "Aggregate",
      "Strategy": "Sorted",
      "Partial Mode": "Simple",
      "Parallel Aware": false,
      "Async Capable": false,
      "Actual Rows": 24,
      "Actual Loops": 1,
      "Group Key": ["date_trunc('year'::text, created_at)"],
      "Plans": [
        {
          "Node Type": "Index Scan",
          "Parent Relationship": "Outer",
          "Parallel Aware": false,
          "Async Capable": false,
          "Scan Direction": "Forward",
          "Index Name": "year_idx",
          "Relation Name": "users",
          "Alias": "u",
          "Actual Rows": 10000000,
          "Actual Loops": 1
        }
      ]
    },
    "Triggers": [
    ]
  }
]

I also tried creating an index just on the timestamp alone (which is pretty useless since the timestamp is just the number of microseconds after a given date and is almost guaranteed to be unique) which didn't help.

Despite the index being used, the query takes the same 6 to 7 seconds to run. How can I create efficient indexes on timestamp data for a given granularity (day, month, year) so this kind of query can be performed quickly?

I have seen databases that store a date, followed by a timestamp, and have an index on the date column but that seems incredibly inefficient to me (I haven't run queries against such DBs so it could still be taking longer to use the indexed date column).

6
  • 1
    This query doesn't search for anything, it takes all records including everything. Counting all records in a table, isn't the best part of PostgreSQL and we avoid this (for large tables) by maintaining an aggregate table. A query like this will be processed in half a millisecond, even for billions of records in the main table: the query doesn't even touch this large table. cybertec-postgresql.com/en/postgresql-count-made-fast Commented Dec 25, 2023 at 21:30
  • I appreciate the comment and will read the article, but I still don't understand why scanning the index is roughly 5x slower than scanning the table. Do other types of index (e.g. BRIN) help?
    – OM222O
    Commented Dec 25, 2023 at 21:45
  • I hacked together my version of a query language some time ago where a B Tree was made over unique values in a column, then each unique value pointed to a list of row numbers containing that value; this allowed extremely fast count (since each index had a row count by default (length of the row number list)). Can a similar type of index be created in SQL as well?
    – OM222O
    Commented Dec 25, 2023 at 21:51
  • A sequential read is processed sequentially and an index read will be random. In most cases random processing is (much) slower. Commented Dec 25, 2023 at 21:51
  • 2
    Your plans are missing the most useful parts of them, and also formatted in way hard for people to read. Do EXPLAIN (ANALYZE, BUFFERS) without turning costs off, turning timing off, and without using json format. Also do a VACUUM ANALYZE first.
    – jjanes
    Commented Dec 26, 2023 at 18:10

1 Answer 1

0

Test data:

CREATE UNLOGGED TABLE foo( id INT NOT NULL, ts TIMESTAMP WITHOUT TIME ZONE,
email TEXT, password TEXT );
INSERT INTO foo SELECT n, 
  '1990-01-01'::TIMESTAMP WITHOUT TIME ZONE + ('1 minute'::INTERVAL)*n, 
  '012345678901234567890123456789012345678901234567890123456789', 
  '012345678901234567890123456789012345678901234567890123456789' 
  FROM generate_series(1,10000000) n;
VACUUM ANALYZE foo;
SELECT min(ts), max(ts) FROM foo;
         min         |         max
---------------------+---------------------
 1990-01-01 00:01:00 | 2009-01-05 10:40:00

No index:

 EXPLAIN ANALYZE SELECT date_trunc('year',ts), count(*) FROM foo GROUP BY 1;
 HashAggregate  (cost=900266.14..1103391.18 rows=10000002 width=16) (actual time=2072.484..2076.617 rows=20 loops=1)
   Group Key: date_trunc('year'::text, ts)
   Planned Partitions: 8  Batches: 1  Memory Usage: 49177kB
   ->  Seq Scan on foo  (cost=0.00..337766.03 rows=10000002 width=8) (actual time=70.569..1371.841 rows=10000000 loops=1)
 Execution Time: 2096.341 ms

With index:

CREATE INDEX ON foo(ts);
EXPLAIN ANALYZE ...same query...
 HashAggregate  (cost=847188.44..1050313.44 rows=10000000 width=16) (actual time=2021.583..2025.632 rows=20 loops=1)
   Group Key: date_trunc('year'::text, ts)
   Planned Partitions: 8  Batches: 1  Memory Usage: 49177kB
   ->  Index Only Scan using foo_ts_idx on foo  (cost=0.43..284688.43 rows=10000000 width=8) (actual time=22.729..1333.129 rows=10000000 loops=1)
         Heap Fetches: 0
 Execution Time: 2043.889 ms

Same timing, as most of the execution time is spent in function date_trunc(). In both cases, it does not understand that date_trunc('year') will dramatically lower the cardinality, resulting in a much smaller number of rows. This is shown in the row count estimates: in both queries it thinks the query will return 10M rows, while it returns only 20 rows. The same occurs with date_part() and extract(). This miscalculation means it does not use a parallel query, which would speed things up a bit in this case.

I have no idea why it takes 30s in your case, maybe IO issues, but to debug that you'd need EXPLAIN ANALYZE with more options as suggested in the comments.

What is the fastest way to do this count?

 EXPLAIN ANALYZE SELECT count(*) FROM foo;
 Finalize Aggregate  (cost=212771.98..212771.99 rows=1 width=8) (actual time=378.475..382.798 rows=1 loops=1)
   ->  Gather  (cost=212771.77..212771.98 rows=2 width=8) (actual time=378.411..382.791 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=211771.77..211771.78 rows=1 width=8) (actual time=360.901..360.902 rows=1 loops=3)
               ->  Parallel Index Only Scan using foo_ts_idx on foo  (cost=0.43..201355.10 rows=4166667 width=0) (actual time=0.052..255.101 rows=3333333 loops=3)
                     Heap Fetches: 0
 Execution Time: 384.242 ms

So any query that counts the whole table can't go any faster than that.

Now let's create a function index. A side effect is to get accurate row estimates, which triggers parallelization. It's twice as fast, which means it's still slow.

CREATE INDEX foo_ts_year_idx ON foo( date_trunc('year',ts) );
ANALYZE foo;
EXPLAIN ANALYZE SELECT date_trunc('year',ts), count(*) FROM foo GROUP BY 1;
 Finalize GroupAggregate  (cost=233605.84..233610.95 rows=20 width=16) (actual time=1012.358..1016.666 rows=20 loops=1)
   Group Key: (date_trunc('year'::text, ts))
   ->  Gather Merge  (cost=233605.84..233610.50 rows=40 width=16) (actual time=1012.351..1016.654 rows=60 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=232605.81..232605.86 rows=20 width=16) (actual time=851.658..851.659 rows=20 loops=3)
               Sort Key: (date_trunc('year'::text, ts))
               Sort Method: quicksort  Memory: 25kB
               Worker 0:  Sort Method: quicksort  Memory: 25kB
               Worker 1:  Sort Method: quicksort  Memory: 25kB
               ->  Partial HashAggregate  (cost=232605.13..232605.38 rows=20 width=16) (actual time=851.574..851.577 rows=20 loops=3)
                     Group Key: date_trunc('year'::text, ts)
                     Batches: 1  Memory Usage: 24kB
                     Worker 0:  Batches: 1  Memory Usage: 24kB
                     Worker 1:  Batches: 1  Memory Usage: 24kB
                     ->  Parallel Index Only Scan using foo_ts_idx on foo  (cost=0.43..211771.79 rows=4166668 width=8) (actual time=4.720..561.431 rows=3333333 loops=3)
                           Heap Fetches: 0
 Execution Time: 1019.985 ms

Forgetting for a second that the way to optimize this would really be to use a materialized view with per-day counts instead of just brutally counting everything... let's try to make it as fast as count(*).

The planner doesn't know that "date_trunc('year',ts) = X" is equivalent to a range request on the year, but we do.

WITH years AS MATERIALIZED (SELECT 1900+y AS year,
'1900-01-01'::DATE + ('1 YEAR'::INTERVAL)*y AS start,
'1901-01-01'::DATE + ('1 YEAR'::INTERVAL)*y AS stop
FROM generate_series( 0,200 ) y)
SELECT years.year, (SELECT count(*) FROM foo WHERE ts >= start AND ts < stop) cnt FROM years;

This takes about 700ms, not much faster than the previous one, but it does use less cpu.

Replying to your comment:

I hacked together my version of a query language some time ago where a B Tree was made over unique values in a column, then each unique value pointed to a list of row numbers containing that value; this allowed extremely fast count (since each index had a row count by default (length of the row number list))

Any update to the indexed column needs to update the count, which requires an exclusive lock. This would create contention and reduce performance on concurrent updates. That's the kind of stuff OLTP databases avoid to do. There's also the problem of transactions: if two transactions want to update the same count, they have to lock the count for the whole transaction in case one of them is rolled back, or open another can of worms like undo logs to decrement it at rollback, but then the undo logic has to be pretty smart... best to leave crash recovery unmentioned...

If you really need that count (most of the times you don't actually need it) the simplest is to cache it.

If you really need it in real time and exact, then since it's a created_at column and these tend to be set with now() at insertion and never updated, then you can cache it up to today by running a count every day at 00:00:00 and caching the results, then only actually count the rows from today at 00:00:00 to now (with an index on created_at).

Another option is a materialized view, for example a per-day summary table with columns "date" and "count". Then triggers in the users table, to update the count field in the materialized view for the corresponding date. This has the same concurrency issues as mentioned, so the summary table should not be "too summarized" (like, per year) to avoid having all the triggers attempting to update the same count at the same time. Although in the case of a "created_at" column, every insert in the users table will always hit the last row in the summary table, so you will have no concurrency on inserts anyway.

If what you're really doing is data warehousing/OLAP stuff then you can also try a database that's specialized in this stuff, like clickhouse. It's a different compromise, for example there are usually no updates, so it's not usable as an OLTP database.

4
  • I was considering using Redis as an LFU cache for Postgres (I know postgres offers its own cache but it seems awkward to work with) where the application checks Redis for a hash of its inputs, if it's found there then returns the result, otherwise, it queries the actual DB and caches the result (cache is invalidated on insert, delete or update). As long as Redis and the backend are on the same physical server, this shouldn't add any latency issues, and since the application is much more read-heavy, I'm not too worried about concurrent modifications causing issues. Would this be viable?
    – OM222O
    Commented Dec 28, 2023 at 11:52
  • Seems like a lot of work just for 1 query. Why do you need that count in real time?
    – bobflux
    Commented Dec 28, 2023 at 12:43
  • It's not this exact query I'm worried about, just the fact that Postgres is behaving weirdly is not something I expected (count should have been fairly fast with an index). I came across this when doing a bunch of performance comparisons on different databases (MySQL, Postgres, Sybase, etc.) to choose the fastest one for the application. I have seen Redis suggested as a cache multiple times so I think it is a viable approach.
    – OM222O
    Commented Dec 28, 2023 at 12:52
  • This count is fairly fast, about 1s. It's about normal for counting 10M rows in OLTP database. If you have long complicated queries that can be cached, then it is indeed a good idea to do so... it depends on the application too, maybe you'd be better off caching API calls instead of queries, who knows
    – bobflux
    Commented Dec 28, 2023 at 14:02

Your Answer

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

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