go - Does gorm auto-index created_at and updated_at columns when gorm.Model is embedded? - Stack Overflow

admin管理员组

文章数量:1290421

I am using gorm as the ORM in my Go application. I am embedding gorm.Model in all my model structs. For some reason, I assumed that created_at and updated_at columns will be auto-indexed because date range filtering is one of the most common DB queries. But turns out thats not the case.

What is the recommended way to auto-index created_at and updated_at in all models?

Slow query analysis showed sequential scan on date range query:

+----------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                       |
|----------------------------------------------------------------------------------------------------------------------------------|
| Aggregate  (cost=969.33..969.34 rows=1 width=8) (actual time=2.675..2.676 rows=1 loops=1)                                        |
|   ->  Sort  (cost=969.31..969.31 rows=1 width=214) (actual time=2.672..2.672 rows=0 loops=1)                                     |
|         Sort Key: analyses.updated_at DESC                                                                               |
|         Sort Method: quicksort  Memory: 25kB                                                                                     |
|         ->  Seq Scan on analyses  (cost=0.00..969.30 rows=1 width=214) (actual time=2.666..2.666 rows=0 loops=1)         |
|               Filter: ((status = 3) AND (updated_at < (now())::date) AND (updated_at >= ((now() + '00:00:10'::interval))::date)) |
|               Rows Removed by Filter: 9012                                                                                       |
| Planning Time: 0.217 ms                                                                                                          |
| Execution Time: 2.713 ms 

The schema definition confirms there is no index for created_at and updated_at.

Indexes:
    "analyses_pkey" PRIMARY KEY, btree (id)
    "idx_ecosystem_name_version" UNIQUE, btree (ecosystem, name, version)
    "idx_analyses_external_id" UNIQUE, btree (external_id)
    "idx_analyses_deleted_at" btree (deleted_at)
    "idx_analyses_source" btree (source)

I am using gorm as the ORM in my Go application. I am embedding gorm.Model in all my model structs. For some reason, I assumed that created_at and updated_at columns will be auto-indexed because date range filtering is one of the most common DB queries. But turns out thats not the case.

What is the recommended way to auto-index created_at and updated_at in all models?

Slow query analysis showed sequential scan on date range query:

+----------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                       |
|----------------------------------------------------------------------------------------------------------------------------------|
| Aggregate  (cost=969.33..969.34 rows=1 width=8) (actual time=2.675..2.676 rows=1 loops=1)                                        |
|   ->  Sort  (cost=969.31..969.31 rows=1 width=214) (actual time=2.672..2.672 rows=0 loops=1)                                     |
|         Sort Key: analyses.updated_at DESC                                                                               |
|         Sort Method: quicksort  Memory: 25kB                                                                                     |
|         ->  Seq Scan on analyses  (cost=0.00..969.30 rows=1 width=214) (actual time=2.666..2.666 rows=0 loops=1)         |
|               Filter: ((status = 3) AND (updated_at < (now())::date) AND (updated_at >= ((now() + '00:00:10'::interval))::date)) |
|               Rows Removed by Filter: 9012                                                                                       |
| Planning Time: 0.217 ms                                                                                                          |
| Execution Time: 2.713 ms 

The schema definition confirms there is no index for created_at and updated_at.

Indexes:
    "analyses_pkey" PRIMARY KEY, btree (id)
    "idx_ecosystem_name_version" UNIQUE, btree (ecosystem, name, version)
    "idx_analyses_external_id" UNIQUE, btree (external_id)
    "idx_analyses_deleted_at" btree (deleted_at)
    "idx_analyses_source" btree (source)

• go
• orm
• go-gorm

Share Improve this question Follow asked Feb 20 at 17:07 abhisekabhisek 1111 bronze badge

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

You should be able too index your created_at and updated_at fields in your models using:

type YourModel struct {
    CreatedAt time.Time `gorm:"index"`
    UpdatedAt time.Time `gorm:"index"`
}

本文标签： goDoes gorm autoindex createdat and updatedat columns when gormModel is embeddedStack Overflow