PostgreSQL: Solving in-efficiency problems

It is faster to make a link query to a table in a database than to use in, how do I avoid using in?

PostgreSQL provides the function of regexp_split_to_table, which we can use to avoid problems with in

How to use:

Table A , table a has field Id

Using in query: SELECT * from A where ID in (xxx,xxxx,xxxx)

Replacement: Select A.* from (select Regexp_split_to_table (' xxxx,xxx ', ', ') as IDs) as TT join A on Tt.ids=a.id

Split the way arbitrarily, the above substitution method is ","

Additional:

1. Creating table: Create TABLE GG ("id" varchar); create INDEX index_gg_id on GG ("id"); CREATE TABLE Ggu ("Id" varchar (36))

2. Generate Data (Golang code)

func createdata(eng *xorm.Engine) {
    c := make(chan int, 20) for i := 0; i < 20; i++ {
        eng.Exec(`insert into gg("Id") values(‘` + strings.UUID() + `‘)`)
        c <- 1 }

    i := 0 for { select { case <-c:
            i++ default:
            eng.Exec(`insert into gg("Id") values(‘` + strings.UUID() + `‘)`)
            c <- 1 } if i%10000 == 0 {
            fmt.Println(time.Now())
        } if i > 1000000 { break }
    }
}

Use the UUID as the ID

3. Enquiry

func insearchspend(eng *xorm.Engine) {
    ids := []string{
        "752e92bc-9ac0-48e1-8dcc-e162acadfe15",
        "ce16c8c9-4e24-4063-98b3-9b40ad312c2f",
        "603bc795-92b1-4111-a7d5-3ece6861eb54",
    }
    result := make([]*gg, 0)

    //ids = ids[0:2]
    tm := time.Now()
    err := eng.Table("gg").In(`"Id"`, ids).Find(&result)
    if err != nil {
        fmt.Println(err.Error())
    }
    fmt.Println(time.Now().UnixNano() - tm.UnixNano())
    tm = time.Now()
    err = eng.Sql(`SELECT "Id" FROM (SELECT regexp_split_to_table(?,‘,‘) AS tt) AS ids JOIN gg ON ids.tt=gg."Id"`, sysstring.Join(ids, ",")).Find(&result)
    if err != nil {
        fmt.Println(err.Error())
    }
    fmt.Println(time.Now().UnixNano() - tm.UnixNano())
    tm = time.Now()
}

Test the performance gap more than 10 times times, test data volume 1 million + (above is in, below is RegExp unit: nanoseconds)

PostgreSQL: Solving in-efficiency problems