Pg: what driver is pg using? it is terribly slow.

[focusonline]

go-pg is very powerful ORM toolkit of golang, but I found it is very slow when querying data in postgresql 11.2 on windows 10, same operation executed by golang mysql driver is faster 10 times than pg, for example, get 2 records, in mysql , about 20ms, but in pg, 300ms!! I compiled the source code on linux, same result!! what is happening?
When it comes to java jdbc driver , it is normal, only 10ms or less on windows.
Not only go-pg, I tried github.com/lib/pq, a mature almost official driver, same bad performance like go-pg, even worse.
I am really frustrated, and confused about my choice, maybe I selected wrong database with golang? or golang is bad choice for postgresql database?
I am thinking seriously to transite my project to mysql ...it is so hard.

[vmihailenco]

This has nothing to do with go-pg or lib/pq - your query is slow. Use EXPLAIN ANALYZE to understand why it is slow.

[focusonline]

I swear it has nothing to do with bad query, no huge data, no table full-scan without index. There are only 2 records in the table totally, just get them all, in mysql, same table structure, same query, same golang version, same windows 10, only 30ms , but in lib/pq and go-pg, longer than 300ms... all same except different drivers. it shouldn't be so slow, I really wonder what problem it encounters.
I do like go-pg and use it for long time, if i have to change the database, it will be a tragedy.
however, go-pg is very good ORM framework, thank you guys for it.

[sgon00]

@focusonline I just start learning go-pg today. I don't find it's slow. If you find it's slow, you should share your code, data, and console output to make it more clear. Just show how to create that working environment line by line, so other people can try it. Just saying it's slow and longer than 300ms, you won't get any help.

[vmihailenco]

go-pg and lib/pq have nothing in common. So if both are slow and you are sure that query is fast then my only guess is that network/connection is slow. Anyway as @sgon00 said you will not get much help this way - you need to pinpoint what is slow and why. Usually it is the query.

[focusonline]

guys, more details:

OS: win10

hardware: AMD Ryzen 2600, 16G

postgresql: PostgreSQL 11.2 on x86_64-pc-mingw64, compiled by gcc.exe (Rev5, Built by MSYS2 project) 4.9.2, 64-bit
t_org: id(bigserial, primary key), org_name(varchar(300))
mysql: 10.1.38-MariaDB
t_org: id(bigint(64), primary key), org_name(longtext)

simple query: SELECT id, org_name FROM t_org WHERE id=1

postgresql connectin->host=127.0.0.1 port=5432 user=postgres password=XXX dbname=postgres sslmode=disable

testing code:

import (
    "github.com/go-pg/pg"
    _ "github.com/lib/pq"
    _ "github.com/go-sql-driver/mysql"
)
func testLibPQ(){
    t1 := time.Now().UnixNano()
    psqlInfo := "host=127.0.0.1 port=5432 user=postgres "+
    "password=xxxx dbname=postgres sslmode=disable"
    db, err := sql.Open("postgres", psqlInfo)
    t2 := time.Now().UnixNano()
    utils.Logger.Debug("lib/pq open conn:", t2 - t1)
    if err != nil {
        panic(err)
    }
    defer db.Close()
    sql := "SELECT id, org_name FROM t_org WHERE id=1"
    rows,err:=db.Query(sql)
    t3 := time.Now().UnixNano()
    utils.Logger.Debug("lib/pq query:", t3 - t2)
    if err!= nil{
        fmt.Println(err)
    }
    defer rows.Close()
}

func testGoPG(){
    t1 := time.Now().UnixNano()
    db := utils.NewPgConn().Open().(*pg.DB) // here is getting a go-pg db wrapper
    t2 := time.Now().UnixNano()
    utils.Logger.Debug("gopg open conn:", t2 - t1)
    defer db.Close()
    org := new(model.Org)
    org.ID = 1
    db.Model(org).Select()
    t3 := time.Now().UnixNano()
    utils.Logger.Debug("gopg query:", t3 - t2)
}

func testMysql() {
    t1 := time.Now().UnixNano()
    const (
        USERNAME = "root"
        PASSWORD = ""
                NETWORK  = "tcp"
        SERVER   = "127.0.0.1"
                PORT     = 3306
                DATABASE = "test"
    )
    dsn := fmt.Sprintf("%s:%s@%s(%s:%d)/%s",USERNAME,PASSWORD,NETWORK,SERVER,PORT,DATABASE)
    db,err := sql.Open("mysql",dsn)
    t2 := time.Now().UnixNano()
    utils.Logger.Debug("mysql open conn:", t2 - t1)
    if err != nil{
            fmt.Printf("Open mysql failed,err:%v\n",err)
            return
    }
    sql := "SELECT id, org_name FROM t_org WHERE id=1"
    rows,err:=db.Query(sql)
    t3 := time.Now().UnixNano()
    utils.Logger.Debug("mysql query:", t3 - t2)
    if err!= nil{
        fmt.Println(err)
    }
    defer rows.Close()
}

testing output:

[DBUG] 2019-08-03 10:10:14 lib/pq open conn:0
[DBUG] 2019-08-03 10:10:14 lib/pq query:250055500
[DBUG] 2019-08-03 10:10:14 gopg open conn:0
[DBUG] 2019-08-03 10:10:15 gopg query:241054300
[DBUG] 2019-08-03 10:10:15 mysql open conn:0
[DBUG] 2019-08-03 10:10:15 mysql query:1000200

just like above, mysql is faster 10 times even 20 times than PG, disaster.
but when using JDBC to execute same query on pg, it is very fast like mysql.
I am confused, any idea is appreciated. thanks.

[sgon00]

@focusonline
Hi, first, I am new to go-pg. I just started to learn it yesterday.
I think you'd better format your comment in Markdown syntax, otherwise it is kinda not readable.
Anyway, I don't have mysql db so I didn't try mysql.

My postgresql version:

testdb1=> SELECT version();
                                                     version
---------------------------------------------------------------------------------
 PostgreSQL 11.3 on x86_64-apple-darwin17.7.0, compiled by Apple LLVM version 10.0.0 (clang-1000.11.45.5), 64-bit
(1 row)

The following is a working runnable code that I ran in my laptop:

package main

import (
    "fmt"
    "time"

    "github.com/go-pg/pg/v9"
    "github.com/go-pg/pg/v9/orm"
)

// User ...
type User struct {
    ID     int64
    Name   string
    Emails []string
}

func (u *User) String() string {
    return fmt.Sprintf("User<%d %s %v>", u.ID, u.Name, u.Emails)
}

func main() {
    db := pg.Connect(&pg.Options{
        User:     "user1",
        Password: "user1",
        Database: "testdb1",
        Addr:     "localhost:5432",
    })
    defer db.Close()

    db.DropTable((*User)(nil), &orm.DropTableOptions{
        IfExists: true,
    })
    db.CreateTable((*User)(nil), &orm.CreateTableOptions{})

    user1 := &User{
        Name:   "admin",
        Emails: []string{"admin1@admin", "admin2@admin"},
    }
    db.Insert(user1)

    db.Insert(&User{
        Name:   "root",
        Emails: []string{"root1@root", "root2@root"},
    })

    user := &User{ID: user1.ID}
    t1 := time.Now().UnixNano()
    db.Select(user)
    t2 := time.Now().UnixNano()

    fmt.Println(user)
    fmt.Println(t2 - t1)
}

The output:

$ go build && ./tmp
User<1 admin [admin1@admin admin2@admin]>
535000

[focusonline]

I think I found the root cause,
It is for WIN 10, an unstable toy.
Postgresql 11.2 does not work well on it
I installed docker on manjaro linux , pull and run a postgresql 11.4 image,
reset the connection to this db,
after this. it works fine, crazy fast, much better than mariaDB running on WIN 10.
especially go-pg, better than lib/pq!!
WIN 10 is rubbish really and hostile to non-microsoft developer.
I should have changed my stage from window to linux or macos really.
I do like go-pg and devote all my golang project to it!
thank you guys for big help!