Golang慢扫描（）多行

Question

I am running a query in Golang where I select multiple rows from my Postgresql Database.

I am using the following imports for my query

"database/sql"
"github.com/lib/pq"

I have narrowed down to my loop for scanning the results into my struct.

// Returns about 400 rows
rows, err = db.Query('SELECT * FROM infrastructure')
if err != nil {
    return nil, err
}

var arrOfInfra []model.Infrastructure
for rows.Next() {
    obj, ptrs := model.InfrastructureInit()
    rows.Scan(ptrs...)
    arrOfInfra = append(arrOfInfra, *obj)
}
rows.Close()

The above code takes about 8 seconds to run, and while the query is fast, the loop in rows.Next() takes the entire 8 seconds over to complete.

Any ideas? Am I doing something wrong, or is there a better way?

My configuration for my database

// host, port, dbname, user, password masked for obvious reasons
db, err := sql.Open("postgres", "host=... port=... dbname=... user=... password=... sslmode=require")
if err != nil {
    panic(err)
}

// I have tried using the default, or setting to high number (100), but it doesn't seem to help with my situation
db.SetMaxIdleConns(1)
db.SetMaxOpenConns(1)

---

UPDATE 1:

I placed print statements in the for loop. Below is my updated snippet

for rows.Next() {
    obj, ptrs := model.InfrastructureInit()
    rows.Scan(ptrs...)
    arrOfInfra = append(arrOfInfra, *obj)
    fmt.Println("Len: " + fmt.Sprint(len(arrOfInfra)))
    fmt.Println(obj)
}

I noticed that in this loop, it will actually pause half-way, and continue after a short break. It looks like this:

Len: 221
Len: 222
Len: 223
Len: 224
<a short pause about 1 second, then prints Len: 225 and continues>
Len: 226
Len: 227
...
..
.

and it will happen again later on at another row count, and again after a few hundred records.

---

UPDATE 2:

Below is a snippet of my InfrastructureInit() method

func InfrastructureInit() (*Infrastructure, []interface{}) {
    irf := new(Infrastructure)
    var ptrs []interface{}
    ptrs = append(ptrs,
        &irf.Base.ID,
        &irf.Base.CreatedAt,
        &irf.Base.UpdatedAt,
        &irf.ListingID,
        &irf.AddressID,
        &irf.Type,
        &irf.Name,
        &irf.Description,
        &irf.Details,
        &irf.TravellingFor,
    )
    return irf, ptrs
}

I am not exactly sure what is causing this slowness, but I currently placed a quick patch on my server to using a redis database and precache my infrastructures, saving it as a string. It seems to be okay for now, but I now have to maintain both redis and my postgres.

I am still puzzled over this weird behavior, but I'm not exactly how rows.Next() work - does it make a query to the database everytime I call rows.Next()?

Answer 1

How do you think about just do like this?

defer rows.Close()

var arrOfInfra []*Infrastructure
for rows.Next() {
    irf := &Infrastructure{}

    err = rows.Scan(
        &irf.Base.ID,
        &irf.Base.CreatedAt,
        &irf.Base.UpdatedAt,
        &irf.ListingID,
        &irf.AddressID,
        &irf.Type,
        &irf.Name,
        &irf.Description,
        &irf.Details,
        &irf.TravellingFor,
    ) 

    if err == nil {
        arrOfInfra = append(arrOfInfra, irf)
    }
}

Hope this help.