Optimization: Use PgFunc::starts_with() when on Postgres

[emmiegit]

Motivation and Proposal

A change was made to sea-query adding PgFunc::starts_with() to support PostgreSQL's starts_with function. (Issue: SeaQL/sea-query#520, PR: SeaQL/sea-query#529)

Within Sea-ORM, for the ColumnTrait::starts_with() method, a branch could be added to check for Postgres, and if so, have it use starts_with(column, 'prefix') instead of LIKE 'prefix%' for an improvement in query performance. This would be transparent to the caller, so they could switch to a different DB backend with no change in code here.

Current Workarounds

Just using starts_with() and taking the performance hit.

[tyt2y3]

Can you quote some numbers on the potential performance gain? AFAIK the query plan will be exactly the same, so it's just the difference in pre-processing.

[nickb937]

I'd read similarly that starts_with was supposed to be faster but I just tried this on a table with 21223 rows. I found that using starts_with(column_name, '123') returned a result in 8ms and using LIKE '123%' returned a result in 5ms