Gorm Gorm slow when querying 4000+ rows

Your Question

version: v1.22.4

I have a query which is not slow when I use explain analyze in pgsql:

pgsql=> explain analyze SELECT * FROM "employees" WHERE (organization_id = 'XYZ') AND is_duplicate = false;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Seq Scan on employees  (cost=0.00..1072.86 rows=4245 width=232) (actual time=0.845..5.401 rows=4283 loops=1)
   Filter: ((NOT is_duplicate) AND (organization_id = 'XYZ'::text))
   Rows Removed by Filter: 18109
 Planning Time: 1.005 ms
 Execution Time: 5.627 ms

I then try to include network latency as well:

pgsql=> \timing
Timing is on.
pgsql=> SELECT * FROM "employees" WHERE (organization_id = 'XYZ') AND is_duplicate = false;
Time: 1742.852 ms (00:01.743)

Which is not the best, but still ok considering I am running pgsql from China while the Postgres instance is in us-central on GCP.

However, when using GORM to query the data in a golang app, based on the GORM's logger output, it is extremely slow. The code runs in an GCP instance in the same network (us-central1 on GCP) as the Postgres instance, where network latency is minimized. My code looks like:

query := e.db.Orm.WithContext(ctx).Model(&employees).
                 Preload("A").
        Preload("A.X").
        Preload("B").
        Preload("B.Y").
        Preload("C").
        Where("organization_id = ?", organizationID).
        Where("is_duplicate = ?", false)

The employees table has 20 columns. Only has index on primary key. Almost all rows have is_duplicate = false.

The resulting pre-load query performance is fine:

  elapsed: 1283338  // 1.28ms
  message: "trace sql execution"   
  rowsAffected: 10   
  sql: "SELECT * FROM "C" WHERE "C"."id" IN ('1','2','3','4','5','6','7','8','9','10')"   
  tag: "employees"   
  timestamp: "2022-01-08T03:31:47.353114137Z" 

but querying the 4283 employees take almost 6s:

elapsed: 5934930249  // 5934.93ms
message: "trace sql execution"   
rowsAffected: 4283   
sql: "SELECT * FROM "employees" WHERE (organization_id = 'XYZ') AND is_duplicate = false"   
tag: "employees"   
timestamp: "2022-01-08T03:31:52.448252349Z" 

I suspect GORM introduced significant amount of latency in my cause. Where did I do wrong? Where is the latency coming from? Any suggestion to improve the performance with GROM?

--- update --- Looks like the Preload has something to do with this. I replace some preload with joins in my code and seeing some improvement:

query := e.db.Orm.WithContext(ctx).Model(&employees).
    Joins("A").
    // Preload("A.X"). looks like GORM doesn't support nested joins preloading
    Joins("B").
    // Preload("B.Y"). looks like GORM doesn't support nested joins preloading
    Joins("C").
    Where("organization_id = ?", organizationID).
    Where("is_duplicate = ?", false)

and that reduce quite a lot of latency. However, looks like GORM doesn't support nested joins preloading, so I can't simply change to Joins.

The document you expected this should be explained

Expected answer

Understand the source of the latency and get suggestions on how to reduce it.

Comment From: Heliner

What is the definition statement of the table of "employees" or the col num of the table? The number of columns may greatly affect the performance of orm

Comment From: jinzhu

GORM v1.23 have improved this a lot, please upgrade to the latest version, thank you.