Skip to content

Improve fingerprint performance pg_query

Max Orefice requested to merge morefice/improve-fingerprint-pg-query into master

Ref: https://gitlab.com/gitlab-com/gl-infra/dbre/-/issues/221

What does this MR do?

This MR improves the fingerprint calculation for pg_query by ~175x.

PgQuery.fingerprint seems to be more performant as it is implemented inside the native C library according to the benchmark below:

➜  gitlab git:(master) ✗ rails c
--------------------------------------------------------------------------------
 Ruby:         ruby 3.2.4 (2024-04-23 revision af471c0e01) [arm64-darwin23]
 GitLab:       17.5.0-pre (544e7702799) EE
 GitLab Shell: 14.39.0
 PostgreSQL:   14.9
--------------------------------------------------------------------------------
[2] pry(main)> query = "SELECT 1;"
[3] pry(main)> PgQuery.parse(query).fingerprint
=> "50fde20626009aba"
[4] pry(main)> PgQuery.fingerprint(query)
=> "50fde20626009aba"

------------------------------------------

require 'pg_query'
require 'benchmark/ips'

Benchmark.ips do |x|
  query = "SELECT 1;"

  x.report(:parse) { PgQuery.parse(query).fingerprint }
  x.report(:fingerprint) { PgQuery.fingerprint(query) }

  x.compare!
end

➜  gitlab git:(master) ✗ ruby benchmark.rb
Warming up --------------------------------------
               parse   332.000  i/100ms
         fingerprint    60.783k i/100ms
Calculating -------------------------------------
               parse      3.393k (± 0.5%) i/s -     17.264k in   5.088680s
         fingerprint    602.347k (± 1.2%) i/s -      3.039M in   5.046233s

Comparison:
         fingerprint:   602346.7 i/s
               parse:     3392.7 i/s - 177.54x  slower
Edited by Max Orefice

Merge request reports