analyze a query postgres

Question

I have a query below, may i please know what needs to be done to reduce execution time from 18sec

the query

SELECT DISTINCT ON (player_name) server_name, player_anme, logged_at, joined_at 
FROM player_cache 
WHERE server_name ILIKE '{query[0]}' 
AND logged_at > (now() - interval '{history} hour') 
ORDER BY player_name,logged_at desc;

The execution plan

Unique (cost=14326.21..14326.25 rows=7 width=82) (actual time=18907.017..18907.049 rows=32 loops=1)
  -> Sort (cost=14326.21..14326.23 rows=7 width=82) (actual time=18907.015..18907.023 rows=81 loops=1)
    Sort Key: player_name, logged_at DESC
    Sort Method: quicksort Memory: 36kB
  -> Index Scan using idx_logged_at on player_cache (cost=0.56..14326.11 rows=7 width=82) (actual time=5.605..18906.463 rows=81 loops=1)
    Index Cond: (logged_at > (now() - '30:00:00'::interval))
    Filter: ((server_name)::text ~~* 'Server-1'::text)
    Rows Removed by Filter: 79825
Planning Time: 0.665 ms
Execution Time: 18907.108 ms

Edit the question, if you got something to add. Don't use comments for that. Especially, if it's code you want to add, that's totally unreadable in comments. — sticky bit
– sticky bit, Commented Aug 22, 2021 at 16:48
An index on your server_name column could help. Also it seems that you don't have any pattern matching in your expression for ILIKE. In this answer you'll find some possibilities, if you really need ILIKE. — Steeeve
– Steeeve, Commented Aug 22, 2021 at 17:01
What is EXPLAIN (ANALYZE, BUFFERS) for the query? Turn track_io_timing on before running that if you can. — jjanes
– jjanes, Commented Aug 22, 2021 at 21:01

Laurenz Albe · Accepted Answer · 2021-08-23 02:57:12Z

2

You can create a trigram index to speed up the ILIKE condition:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX ON player_cache USING gin (server_name gin_trgm_ops);

answered Aug 23, 2021 at 2:57

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

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

Collectives™ on Stack Overflow

analyze a query postgres

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