Timeline for Retrieve recent Matches per Player efficiently
Current License: CC BY-SA 4.0
13 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Oct 28, 2024 at 13:01 | comment | added | Dennis | @ErwinBrandstetter For this query I just need one of them to be in the match. It is used so you can follow some players and have a page where you see their recent games. Yes I see index-only scans already before vacuum. But after vacuum they are much faster and the number of heap fetches went down a lot. | |
| Oct 28, 2024 at 12:22 | comment | added | Erwin Brandstetter | Your current query with WHERE p.profile_id IN (1136191, 19455781) finds any matches for the given players. Didn't you want matches that all of the given players played together? That would be a different query, key word "relational division". See: stackoverflow.com/a/7774879/939860. Also, I hope you see index-only scans after VACUUM? That's the whole point of INCLUDE (match_id). | |
| Oct 28, 2024 at 12:20 | history | edited | Erwin Brandstetter | edited tags | |
| Oct 28, 2024 at 12:16 | history | edited | Dennis | CC BY-SA 4.0 | Everything is fine now after running `VACUUM ANALYZE public.player;` |
| Oct 28, 2024 at 11:01 | vote | accept | Dennis | ||
| Oct 28, 2024 at 11:01 | comment | added | Dennis | @ErwinBrandstetter I am using PostgreSQL 14.13. I need all fields of the match and player for display in UI. There can't be duplicate (match_id, profile_id) in table player | |
| Oct 28, 2024 at 11:01 | history | edited | Dennis | CC BY-SA 4.0 | updated according to Erwin Brandstetter`s answer |
| Oct 28, 2024 at 10:43 | history | edited | Dennis | CC BY-SA 4.0 | added 20 characters in body |
| Oct 27, 2024 at 19:03 | answer | added | Erwin Brandstetter | timeline score: 1 | |
| Oct 27, 2024 at 18:04 | comment | added | Erwin Brandstetter | Your queries select all columns with SELECT *. Which columns do you actually need? Postgres version? SELECT version(); Can there be multiple entries for the same (match_id, profile_id) in table player? (If so, why?) | |
| Oct 27, 2024 at 14:33 | answer | added | alexius | timeline score: 1 | |
| S Oct 27, 2024 at 14:08 | review | First questions | |||
| Oct 27, 2024 at 19:08 | |||||
| S Oct 27, 2024 at 14:08 | history | asked | Dennis | CC BY-SA 4.0 |