select * from users where age * age > 2500;
create index on users ((age*age));

select * from users where upper(displayname) = 'FRANZ';
create index on users (upper(displayname));

select id from posts where closeddate is not null;
create index on posts (id) where closeddate is not null;

SELECT id FROM posts WHERE title LIKE 'pi%';
create index on posts (title text_pattern_ops);

SELECT displayname AS name, location FROM users WHERE location LIKE 'Reims%';
create index on users (location text_pattern_ops);

select * from posthistory where extract('year' from creationdate) = 2010;
create index on posthistory(extract('year' from creationdate));

select * from comments where jsonfield is not null;
create index on comments (id) WHERE jsonfield is not null;

SELECT COUNT(ph.posthistorytypeid) AS nb, pht.name AS name FROM posthistory ph JOIN posthistorytypes pht ON (ph.posthistorytypeid = pht.id) WHERE ph.creationdate < '2011-01-01' GROUP BY 2;
create index on posthistory (creationdate,posthistorytypeid);

SELECT u.displayname AS name , max(c.creationdate) AS date_last_comment FROM comments c JOIN users u ON (c.userid = u.id) WHERE userid = :user_id GROUP BY 1;
create index on comments (userid);

SELECT * FROM comments WHERE creationdate BETWEEN '2021-01-01' AND '2021-02-01';
REINDEX INDEX comments_creationdate_idx; => invalid
-- create index CONCURRENTLY on comments (creationdate); => ctrl+c pour invalid

SELECT * FROM interaction WHERE interac = 22;
reindex index interaction_interac_idx1; => bloat index

select distinct location, age, displayname from users where creationdate > '2021-01-01' ORDER BY 1,2 ;
work_mem = 10MB

select * from users u join votes v on (u.id = v.userid) and v.votetypeid = 5 join votes v2 on (u.id = v2.userid) and v2.votetypeid = 6 join votes v3 on (u.id = v3.userid) and v3.votetypeid = 8 join votes v4 on (u.id = v4.userid) and v4.votetypeid = 10 join votes v5 on (u.id = v5.userid) and v5.votetypeid = 4;
join_collapse_limit = 8;
create index on votes (votetypeid,userid);