arjdbc + postgres = slow?

adapter.rb

    alias_chained_method :columns, :query_cache, :pg_columns

    def pg_columns(table_name, name=nil)
      val = nil
      Benchmark.bm do |r|
        r.report do
          schema_name = @config[:schema_search_path]
          if table_name =~ /\./
            parts = table_name.split(/\./)
            table_name = parts.pop
            schema_name = parts.join(".")
          end
          schema_list = if schema_name.nil?
            []
          else
            schema_name.split(/\s*,\s*/)
          end
          while schema_list.size > 1
            s = schema_list.shift
            begin
              val = @connection.columns_internal(table_name, name, s)
            rescue ActiveRecord::JDBCError=>ignored_for_next_schema
            end
          end
          if val.nil?
            s = schema_list.shift
            val= @connection.columns_internal(table_name, name, s)
          end
        end
      end
      return val
    end

---------

$ rails c
Using ActiveModel validations.
Loading development environment (Rails 3.0.9)
/Users/jcalvert/.rvm/rubies/jruby-1.6.5/lib/ruby/1.9/irb/input-method.rb:108 warning: Ignoring internal encoding UTF-8: it is identical to external encoding UTF-8
/Users/jcalvert/.rvm/rubies/jruby-1.6.5/lib/ruby/1.9/irb/input-method.rb:109 warning: Ignoring internal encoding UTF-8: it is identical to external encoding UTF-8
jruby-1.6.5 :001 > u = User.last
      user     system      total        real
  3.794000   0.000000   3.794000 (  3.794000)
 => #<User id: 22536649, email: "[email protected]", ...

original adapter.rb

def pg_columns(table_name, name=nil)
      schema_name = @config[:schema_search_path]
      if table_name =~ /\./
        parts = table_name.split(/\./)
        table_name = parts.pop
        schema_name = parts.join(".")
      end
      schema_list = if schema_name.nil?
          []
        else
          schema_name.split(/\s*,\s*/)
        end
      while schema_list.size > 1
        s = schema_list.shift
        begin
          return @connection.columns_internal(table_name, name, s)
        rescue ActiveRecord::JDBCError=>ignored_for_next_schema
        end
      end
      s = schema_list.shift
      return @connection.columns_internal(table_name, name, s)
    end

postgresql_adapter.rb

 def columns(table_name, name = nil)
        # Limit, precision, and scale are all handled by the superclass.
        val = nil
        Benchmark.bm do |r|
          r.report do
            val = column_definitions(table_name).collect do |name, type, default, notnull|
              PostgreSQLColumn.new(name, default, type, notnull == 'f')
            end
          end
        end
        return val
      end
----
jcalvert$ rails c
Using ActiveModel validations.
Loading development environment (Rails 3.0.9)
ruby-1.9.2-p290 :001 > u = User.last
      user     system      total        real
  0.000000   0.000000   0.000000 (  0.052154)
 => #<User id: 22536649, email: "[email protected]", ....

Hi, I came across this after I noticed many many calls to 'pg_columns' showing up in a Java profiler while running some activerecord apps.

It seems you are showing that postgres is slower in jruby than ruby 1.9? Did you make any headway on this? An issue in arjdbc perhaps? Has this been fixed in arjdbc? I glanced over your arjdbc fork, but I'll have to take a closer look tomorrow. Just wondered if there's a quick answer, thanks.

Looked a bit more and did some quick benchmarks with one database query from my app

jruby w/ arjdcb 1.2.1:                 18.5 ms/query
ruby1.9 with pg:                        4.5 ms/query
jruby w/ arjdbc git master 2012-06-13:  7.8 ms/query

So, if you had any hand in getting this fixed in arjdbc, thanks!

Actually I did submit a code patch to arjdbc over this. The culprit is actually the Postgres JDBC driver, which ultimately tries to call the database metadata method...which if you have say, a few thousand tables will absolutely crush your startup time.

…

On Tue, Jun 12, 2012 at 6:00 PM, pmahoney < ***@***.*** > wrote: Hi, I came across this after I noticed many many calls to 'pg_columns' showing up in a Java profiler while running some activerecord apps. It seems you are showing that postgres is slower in jruby than ruby 1.9? Did you make any headway on this? An issue in arjdbc perhaps? Has this been fixed in arjdbc? I glanced over your arjdbc fork, but I'll have to take a closer look tomorrow. Just wondered if there's a quick answer, thanks. --- Reply to this email directly or view it on GitHub: https://gist.github.com/1483515