/api/asset/time-bucket reqest takes too long

[tranquil acorn]

I've created a new album of ~600 photos from my recent holiday. I takes about 4-5s to show first assets of this particular album. Much larger albums take <500ms.
How to debug the query?
Thanks in advance.

[trim oar]

:wave: Hey @tranquil acorn,

Thanks for reaching out to us. Please follow the recommended actions below; this will help us be more effective in our support effort and leave more time for building Immich :immich:.

References

• Container Logs: docker compose logs docs
• Container Status: docker compose ps docs
• Reverse Proxy: https://immich.app/docs/administration/reverse-proxy

Checklist

1. :ballot_box_with_check: I have verified I'm on the latest release(note that mobile app releases may take some time).
2. :ballot_box_with_check: I have read applicable release notes.
3. :blue_square: I have reviewed the FAQs for known issues.
4. :blue_square: I have reviewed Github for known issues.
5. :blue_square: I have tried accessing Immich via local ip (without a custom reverse proxy).
6. :blue_square: I have uploaded the relevant logs, docker compose, and .env files using the buttons below or the /upload command.
7. :blue_square: I have tried an incognito window, disabled extensions, cleared mobile app cache, logged out and back in, different browsers, etc. as applicable

(an item can be marked as "complete" by reacting with the appropriate number)

If this ticket can be closed you can use the /close command, and re-open it later if needed.

[tranquil acorn]

I've found that it's a problem with the SQL query of function getTimeBucket, it takes more than 4s to return results.

query explain shows...

checked another little bigger album where the loading is instant...

seems like some stacked assets issue, however there are no stacked assets in any album

could @primal cradle check it? thanks

[vivid bobcat]

@mystic ferry Is this another one of those reverse index cases?

[tranquil acorn]

with timings...

[primal cradle]

Looks like it

Maybe #7918 solved it

[trim oar]

[Pull Request] perf(server): optimize getByIds query (#7918)

[vivid bobcat]

Are you down to try running the main branch @tranquil acorn ?

[primal cradle]

Alex is too slow to release new versions /s

[tranquil acorn]

I can try

yes, it solves it, now it's instant, thanks! looking forward for a new release