PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to optimize performance and speed up loading data from database?

I have a database table cars that has close to 500k records. I was facing issues with long loading times to load data from this table.

@cars = Car.order('created_at DESC').paginate(page: params[:page], per_page: 100)

It took anywhere between 30-40 seconds to load and display @cars in the browser. I started optimizing it, eg. eliminating fetching information from tables with relation (eg. instead of car.photos.count -> car.total_photos).

Currently, it takes about 5 seconds to load this data. Below is a screenshot from rake mini profiler:
enter image description here
What surprises me is that the query on the screenshot, SELECT COUNT(*) FORM cars, takes about 3.7 seconds. The line 258 references on the screenshot is from pagination:

<%= page_entries_info @cars %>

When I check the indices on the cars table (select * from pg_indexes where tablename = 'cars'), I see the following:

CREATE UNIQUE INDEX cars_pkey ON public.cars USING btree (id)
CREATE INDEX index_cars_on_car_type ON public.cars USING btree (car_type)
CREATE INDEX index_cars_on_created_at ON public.cars USING btree (created_at)

What are the further possibilities to optimize it?

Tags:

2

Answers


  1. 0

    If you paginate with OFFSET and fetch the rows page by page, that is bound to be very inefficient. In addition, if you insist in calculating the total result set count, that is going to be very slow.

    My advice:

    1. use another pagination method: ideally keyset pagination, but cursor-based pagination might work well too

    2. don’t calculate the total result set count, use the estimate from pg_class.reltuples

    See this article for an in-depth discussion.

    Login or Signup to reply.
  2. 0

    You can also use implement caching strategies to reduce the database load. Like this

    Rails.cache.fetch("cars_page_#{params[:page]}", expires_in: 1.hour) do
  Car.order('created_at DESC').paginate(page: params[:page], per_page: 100)
end
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search