Speed up a rails database query using postgresql

Question

I am trying to speed up a Rails query that is consuming a lot of time I reload the page.

I have this in the rails view:

<% if current_account.subscribed? and current_account.subscription.plan.id == 2 %>  
  <div class="flex items-center bg-blue-100 rounded-lg px-2 mb-2 shadow-lg data-controller="tooltip" data-tippy-content="Clases reservadas este mes" data-tippy-delay="0" data-tippy-arrow="true" data-tippy-size="regular" data-tippy-animation="fade">
    <div class="mr-2 flex-shrink-0 text-3xl md:text-2xl">
      📆
    </div>
    <div>
      <p class="text-3xl md:text-2xl font-bold">
        <%= @lessons_basic_count %>
      </p>
    </div>
  </div>

And this is the query I run on the controller:

@lessons_basic_count = @mylessons_lessons.where(datetime: Time.at((current_account.subscription.latest_payment.created if current_account.subscription.latest_payment).to_i)..(Date.current + 30.days)).size

It's a query that looks on the table mylessons which contains lessons a user has booked in the actual period of their subscription.

Thanks.

What is the SQL that is generated? If you go to the PostgreSQL console and enter in EXPLAIN <SQL here> what does it show? Maybe you need an index for a column or set of columns on this table. — cschroed
– cschroed, Commented Jun 28, 2021 at 13:51
EXPLAIN for: SELECT "lessons".* FROM "lessons" INNER JOIN "mylessons" ON "lessons"."id" = "mylessons"."lesson_id" WHERE "mylessons"."user_id" = $1 AND "lessons"."datetime" BETWEEN $2 AND $3 [["user_id", 18], ["datetime", "2021-06-28 12:50:20"], ["datetime", "2021-07-28"]] QUERY PLANThis is the SQL query without the .size at the end — Borja Soler
– Borja Soler, Commented Jun 28, 2021 at 14:08
How did you define latest_payment? The name suggests that a subscription has_many :payments. If you determine the latest payment by loading all payments instead of using a query things could be slowed down considerably. — 3limin4t0r
– 3limin4t0r, Commented Jun 29, 2021 at 9:17

cschroed · Accepted Answer · 2021-06-29 12:05:44Z

2

The info that comes after QUERY PLAN in the EXPLAIN output is what matters. Could you include that output in your question?

It looks like you might want a dual-key on your mylessons table for user_id and datetime. You would add that in a migration with something like:

add_index :mylessons, [:user_id, :datetime]

You also want an index on mylessons.lesson_id. That could be added with something like:

add_index :mylessons, :lesson_id

edited Jun 29, 2021 at 12:05

answered Jun 28, 2021 at 14:32

cschroed

6,9346 gold badges46 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Borja Soler Over a year ago

After QUERY PLAN I get this

Hash Join (cost=46.76..127.95 rows=8 width=506) Hash Cond: (mylessons.lesson_id = lessons.id) -> Seq Scan on mylessons (cost=0.00..81.13 rows=109 width=4) Filter: (user_id = 955) -> Hash (cost=46.54..46.54 rows=65 width=506) -> Seq Scan on lessons (cost=0.00..46.54 rows=65 width=506) Filter: ((datetime >= '2021-06-19 09:26:54'::timestamp without time zone) AND (datetime <= '2021-07-28 00:00:00'::timestamp without time zone)) (7 rows)

cschroed Over a year ago

It looks like you also need an index on mylessons.lesson_id. Your explain plan isn't using any index conditions. When it says Seq Scan that means it has to scan the entire table. Your query is scanning both your mylessons table and your lessons table.

Collectives™ on Stack Overflow

Speed up a rails database query using postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related