CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
Jorge Arimitsu
Jorge Arimitsu

Reputation: 233

Performance issue with the Active Record type map query after upgrading to rails 7

TL;DR

The intent of this commit on Active Record was to make the initial type map query less expensive but after upgrading from rails 6.1.7 to 7.0.4 this query is taking way longer than its previous version and I can't figure out why.

Edit

As jjanes suggested in the answer below, upgrading to PostgreSQL 14 solved the issue.

After the upgrade the execution time decreased from 9656.222 ms to 60.618 ms

After an upgrade to rails 7.0.4 (was using 6.1.7) we faced some performance issues with our Postgresql instance on Amazon RDS. After the deploy the CPU usage reached 100% and the instance went down.

enter image description here

Looking at the db logs I saw a lot of occurencies of the type map query, which is triggered when a new connection to the DB is created.

enter image description here

Our log_min_duration_statement is set to 10000, so it will log any statement that takes longer than 10 seconds.

Before the rails upgrade I saw the type map query few times in a day whith an average duration of 11 seconds. After the upgrade I saw it constatly whith the duration varying between 13 seconds and 90 seconds.

I put both queries in a gist with the result of EXPLAIN (ANALYZE, BUFFERS):

I noticed this commit changes the type map query to be less expensive but it did the opposite in our application.

The problem seems to be the statment generated by the method query_conditions_for_known_type_types

Currently we're using:

Any ideas on why we're seeing this performance issue in this query?

Upvotes: 3

Views: 780

Answers (1)

jjanes
jjanes

Reputation: 44227

The IN-list against a giant list of constants did not get optimized to a hash table lookup until PostgreSQL v14. So it is not surprising that this query sucks in v13.

I don't know if they didn't test this ROR change against older versions of PostgreSQL, or didn't test with a pg_type table as large as yours or a IN-list as long as yours, or what. I don't know much about ROR, I'm just looking at the PostgreSQL side of things

My original simulations didn't reproduce the problem because I assumed the size of the pg_type table was about the same as the size of the IN-list, but obviously that is a bad assumption, as shown by the Rows Removed by Filter: lines.

Maybe the easiest solution, assuming you can't/don't want to reverse this ROR change, would be to migrate to PostgreSQL v14.

Upvotes: 2

Related Questions