trjhtr

I have a table:

games (
 appid integer PRIMARY KEY, 
 name text, 
 rating real, 
 votes integer, 
 score real, 
 windows boolean, 
 mac boolean, 
 linux boolean, 
 vr boolean, 
 release date, 
 price real, 
 tags text)

and a view on that table:

top_games AS 
 SELECT * 
 FROM games 
 ORDER BY score DESC;

I also have indexes on the score, release, price, and tags columns.

The table is updated rarely (once a day), so I hope having this many indexes is fine.

What I'm mainly looking to optimize are my select statements. The [ WHERE expressions] part is created from user input, and is the same for each query. It feels like I wouldn't need to create the same query three times over, but I also can't seem to figure out a faster way to combine them into one.

SELECT COUNT(*) FROM games[ WHERE expressions];

SELECT row_number FROM (SELECT name, row_number() OVER() FROM top_games[ WHERE expressions]) AS win WHERE name ILIKE '%name%';

SELECT * FROM top_games[ WHERE expressions] LIMIT 25 OFFSET offset;

Any other optimizations that you could suggest are welcome too - I'm fairly new to writing SQL code, and there's probably lots to be done. Maybe some way to also index the name column so that it would actually be used?