Does truncate a timestamp break the indexes?-CodePudding

I was wondering if my indexes are working well since I am using nodejs and the dates with microseconds are not allowed in this language. So in my query for some good comparison I am doing this kind of thing:

`WHERE (created_at::timestamp(0), uuid) < (${createdAt}::timestamp(0), ${uuid})`

Since I am using a the cast which truncate to seconds, I supposed that the indexes are break. Am I right ? The solution then would be to change the precision of the timestamps stored, or is there another solution to keep the old ones ?

CodePudding user response：

You could change the PostgreSQL data type to millisecond precision:

ALTER TABLE tab ALTER created_at TYPE timestamp(3) without time zone;

CodePudding user response：

By using the recommended EXPLAIN(ANALYZE, VERBOSE, BUFFERS).

I created a table named users with a constraint on the created_at

create table users (
   id uuid default uuid_generate_v4() not null 
      constraint users_pkey primary key,
   created_at timestamp default CURRENT_TIMESTAMP
);

create index users_created_at_idx on users (created_at);

The test:

EXPLAIN(ANALYZE, VERBOSE, BUFFERS)
SELECT id
FROM users
WHERE (created_at >= '2022-01-21 15:43:33.631779'); 

Index Scan using users_created_at_idx on public.users  (cost=0.14..4.16 rows=1 width=16) (actual time=0.010..0.018 rows=0 loops=1)
  Output: id
  Index Cond: (users.created_at >= '2022-01-21 15:43:33.631779'::timestamp without time zone)
  Buffers: shared hit=1
Planning Time: 0.074 ms
Execution Time: 0.058 ms


EXPLAIN(ANALYZE, VERBOSE, BUFFERS)
SELECT id
FROM users
WHERE (created_at::timestamp(0) >= '2022-01-21 15:43:33.631779'::timestamp(0));

Seq Scan on public.users  (cost=0.00..4.50 rows=33 width=16) (actual time=0.034..0.043 rows=0 loops=1)
  Output: id
  Filter: ((users.created_at)::timestamp(0) without time zone >= '2022-01-21 15:43:34'::timestamp(0) without time zone)
  Rows Removed by Filter: 100
  Buffers: shared hit=3
Planning Time: 0.073 ms
Execution Time: 0.089 ms

As we can see the index on the created_at column is not taken into account when we cast and truncate.