Duplicate indexing in Rails Database Using PSQL

Question

In my Rails project I want to speed up database queries by giving them proper indexing.

I have a Message model with columns created_at, status_updated_at, message_type, direction, status, among others.

In some places I need to run a query like Query 1:

Message.where(created_at: @start_date..@end_date)
                      .where(message_type: @message_types)
                      .where(direction: 'outgoing')
                      .where(status: Message::STATUS_DELIVERED)
                      .where('status_updated_at - created_at >= ?', "90 seconds")
                      .where('status_updated_at - created_at < ?', "180 seconds")

In some other places I only need to query by only 1 of the columns, for example, simply Message.where(direction: 'outgoing') (call this Query 2).

For the long query, Query 1, I created this migration in order to speed it up:

    add_index :messages, [:direction, :status, :created_at, :status_updated_at], name: 'dashboard_delivery_time_index'

For the second query, Query 2, in addition to the indexing added above, I created this migration:

add_index :messages, :direction, name: 'index_messages_on_direction'

Will the second migration be redundant and unnecessary because the first migration already includes :direction?

The code is running fine. But just wondering if the second migration is necessary.

Thanks.

Jeremy · Accepted Answer · 2019-05-14 15:49:47Z

1

Since direction is the first column in the first index, you probably do not need an additional index on direction. If it were not the first column, you would need it. That's not to say that postgres wouldn't use it and it might even help a little because the index will be smaller than the first one, but the gains should be slight.

Is postgres using status_updated_at and created_at from your index? I would be somewhat surprised if it is. It may be better to index direction, status, and status_updated_at - created_at.

answered May 14, 2019 at 15:49

Jeremy

6,77322 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

Duplicate indexing in Rails Database Using PSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related