Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

[Doctrine][Messenger] Add composite index on queue_name #62053

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
wants to merge 1 commit into from
Closed

[Doctrine][Messenger] Add composite index on queue_name #62053

wants to merge 1 commit into from

Conversation

joel1di1
Copy link

Q A
Branch? 7.4
Bug fix? no
New feature? no
Deprecations? no
Issues
License MIT

This PR optimizes the Doctrine Messenger transport by replacing the single-column index on queue_name with a composite index covering queue_name, available_at, and delivered_at.

This improves query performance when fetching messages from specific queues, as the database can use a single covering index instead of multiple separate indexes for the common query pattern that filters by queue name and checks availability/delivery status.

The previous index is removed because it's covered by the new one.

On our production, even if individual calls are fast, lots of workers are querying quite often, so this small update reduced the total load.

Here are the explain plans before and after for the usual query (tested on 200 000 rows)

SELECT `m` . * FROM `messenger_messages` `m` WHERE ( `m` . `queue_name` = ? ) AND ( `m` . `delivered_at` IS NULL OR `m` . `delivered_at` < ? ) AND ( `m` . `available_at` <= ? ) ORDER BY `available_at` ASC LIMIT ? FOR UPDATE SKIP LOCKED
  • Explain plan BEFORE*
-> Limit: 1 row(s)  (cost=1.02 rows=1) (actual time=0.337..0.337 rows=1 loops=1)
    -> Sort row IDs: m.available_at, limit input to 1 row(s) per chunk  (cost=1.02 rows=1) (actual time=0.335..0.335 rows=1 loops=1)
        -> Filter: (((m.delivered_at is null) or (m.delivered_at < TIMESTAMP'2025-10-08 23:51:07')) and (m.available_at <= TIMESTAMP'2025-10-09 00:51:07'))  (cost=1.02 rows=1) (actual time=0.146..0.157 rows=1 loops=1)
            -> Index lookup on m using IDX_75EA56E0FB7336F0 (queue_name='owner_ghosting_detection')  (cost=1.02 rows=1) (actual time=0.14..0.151 rows=1 loops=1)
  • Explain plan AFTER*
-> Limit: 1 row(s)  (cost=0.6 rows=1) (actual time=0.163..0.163 rows=1 loops=1)
    -> Index range scan on m using messenger_delayed_messages_queue_name_IDX over (queue_name = 'owner_ghosting_detection' AND available_at <= '2025-10-09 00:51:07' AND delivered_at < '2025-10-08 23:51:07'), with index condition: ((m.queue_name = 'owner_ghosting_detection') and ((m.delivered_at is null) or (m.delivered_at < TIMESTAMP'2025-10-08 23:51:07')) and (m.available_at <= TIMESTAMP'2025-10-09 00:51:07'))  (cost=0.6 rows=1) (actual time=0.143..0.143 rows=1 loops=1)

@carsonbot carsonbot added this to the 7.4 milestone Oct 13, 2025
@carsonbot
Copy link

Hey!

I see that this is your first PR. That is great! Welcome!

Symfony has a contribution guide which I suggest you to read.

In short:

  • Always add tests
  • Keep backward compatibility (see https://symfony.com/bc).
  • Bug fixes must be submitted against the lowest maintained branch where they apply (see https://symfony.com/releases)
  • Features and deprecations must be submitted against the 7.4 branch.

Review the GitHub status checks of your pull request and try to solve the reported issues. If some tests are failing, try to see if they are failing because of this change.

When two Symfony core team members approve this change, it will be merged and you will become an official Symfony contributor!
If this PR is merged in a lower version branch, it will be merged up to all maintained branches within a few days.

I am going to sit back now and wait for the reviews.

Cheers!

Carsonbot

@carsonbot carsonbot changed the title [Messenger][Doctrine] Add composite index on queue_name [Doctrine][Messenger] Add composite index on queue_name Oct 13, 2025
@MatTheCat
Copy link
Contributor

This PR seems to overlap with #61963

@nicolas-grekas
Copy link
Member

Closing as duplicate of #61963 indeed. Reviews welcome there! 🙏

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

No reviews

Assignees

No one assigned

Labels

Doctrine Messenger Status: Needs Review

Projects

None yet

Milestone

7.4

Development

Successfully merging this pull request may close these issues.

4 participants

@joel1di1 @carsonbot @MatTheCat @nicolas-grekas
Morty Proxy This is a proxified and sanitized view of the page, visit original site.