Performance issue with PostgreSQL after upgrading to rails 7.0.4

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.

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.

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

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 with an average duration of 11 seconds. After the upgrade I saw it constantly with 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:

  • rails 7.0.4 (upgrading form 6.1.7)

  • ruby 3.1.2

  • PostgreSQL 13.7 on Amazon RDS

  • multitenant database where each tenant has its own schema in a single database. (around 2000 schemas)

  • ros-apartment gem

  • good_job with 20 threads and external execution mode

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

3 Likes

Wow — 10,000 things in an IN clause. That’s gonna leave a mark!

Maybe try this kind of format which JOINs on a VALUES clause and see if it’s faster. In theory it should be night-and-day better.

(Unable to paste the whole thing here because it’s > 32000 characters … have replied out on the Rails 7 Github link you provided with the full query in all its glory…)

SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
	FROM pg_type AS t
	LEFT JOIN (SELECT rngtypid, rngsubtype FROM pg_range AS r1
      JOIN (VALUES (16),(17),(18),(19),(20),(21),(23),(25),(26),(114),(142),(600),(601),(602),(603),(604),(628),(700),(701),(718),(790),(829),(869),(650),(1042),(1043),(1082),(1083),(1114),(1184),(1186),(1560),(1562),(1700),(2950),(3614),(3802),(8454524),(8454532),(8454550),(8454564),(21687326),(8454602),(8454610),(8454628),(8454642),(21682156),(8464954),(8454680),(8454688),(8454706),(8454720),(21684526),(19100713),(8454758),(8454766),(8454784),(8454798),(21683276),(8464968),(8464978),(8454836),(8454844),(8454862),(8454876),(21681786),(21678766),(8454958),(19211951),(8454972),(8454982),(21688916),(75500986),(75500994),(75501006),(8468528),(18835662),(18835670),(18835688),(18835702),(21674846),(8455148),(8455156),(8455174),(8455188),(21681566),(8455226),(8455234),(8455252),(8455266),(21689546),(8455304),(8455312),(8455330),(8455344),(21683396),(8455374),(19112166),(8455388),(8455398),(21681656),(8455434),(8455442),(8455460),(8455474),(21686696),(8465324),(8465332),(75239420),(8455512),(8455520),(75239428),(75239440),(8455538),(8455552),(21682886),(8465350),(8455642),(8455650),(75239454),(8455668),(8455682),(75239472),(21674876),(8465364),(8455720),(8455728),(8455746),(8455760),(21680806),(21679636),(8455798),(8455806),(8455824),(8455838),(21679726),(21682706),(8455876),(8455884),(8455902),(8455916),(21677586),(8465532),(8465540),(8455954),(8455962),(8455980),(8455994),(21684006),(8456032),(8456040),(8456058),(8456072),(21679496),(8465558),(8456110),(8456118),(8456136),(8456150),(21675616),(8465572),(8456344),(8456352),(8456370),(8456384),(21674316),(21689066),(8456414),(19107699),(8456428),(8456438),(21680646),(17030744),(17030752),(17030770),(17030784),(21674656),(8465688),(8465696),(8456466),(19112978),(8456480),(8456490),(21681676),(8465714),

... MASSIVE AMOUNTS OF STUFF ...

(24078254),(24078288),(24078290),(24078292),(24078294),(24496343),(24496345),(24496347),(8535634),(8535644),(8535646),(8535648),(8535676),(8535678),(17128374),(8535682),(8551486),(8551488),(8551490),(8539794),(8539796),(8539830),(8539834),(8539836),(8541554),(8541556),(8541558),(8541580),(8541582),(8541584),(8541618),(8541620),(8541622),(8541624),(8549552),(8549554),(8549556),(8532854),(8532876),(8532878),(8532880),(8532914),(8532916),(8532918),(8532920),(24078225),(24078227),(24078229),(24496368),(24496370),(24496372),(24496406),(24496408),(24496410),(24496412),(8535632),(17128123),(8551524),(8551526),(8551528),(8551530),(8543626),(8543628),(8543670),(17074227),(8543672),(8543682),(8543684),(8543686),(8543714),(8543716),(17074476),(8543720),(8544148),(8544170),(8544172),(8544174),(8544208),(8544210),(8544212),(8544214),(8543662),(8543666),(8543668),(8551464),(8551992),(8551996),(8551998),(8549526),(8549528),(8549530),(8551460),(8551462),(8560826),(8560830),(8560832),(8565856),(8565858),(8565860),(8565882),(8565884),(8565886),(8565920),(8565922),(8565924),(8565926),(8568370),(18947729),(8568372),(8568382),(8568384),(8568386),(8568414),(8568416),(18947944),(8568420),(17159434),(17159468),(17159472),(17159474),(17159432),(8573244),(8573248),(8573250),(8573208),(8573210),(8575676),(8575678),(8575680),(8575702),(8575704),(8575706),(8575740),(8575742),(8575744),(8575746),(43450068),(43450070),(43450104),(43450108),(43450110),(45129607),(45129609),(45129611),(45129632),(45129634),(45129636),(45129670),(45129672),(45129674),(45129676),(43453723),(43453725),(43453727),(43453748),(43453750),(43453752),(43453786),(43453788),(43453790),(43453792),(48839342),(48839346),(48839348),(3904),(3906),(3908),(3910),(3912),(3926)
) AS r2(oid) ON r1.rngtypid = r2.oid) AS r ON t.oid = r.rngtypid
1 Like

There’s some more discussion on the PR: postgres: Make the initial type map query less expensive by fsateler · Pull Request #40876 · rails/rails · GitHub

1 Like

Thanks folks! As suggested by an user on stack overflow, upgrading to PostgreSQL 14 did the trick: postgresql - Performance issue with the Active Record type map query after upgrading to rails 7 - Stack Overflow

After the upgrade the execution time of this query decreased from 9656.222 ms to 60.618 ms

1 Like