leaflet.pub
a tool for shared writing and social publishing
awarm.space
1-- Add missing indexes on document foreign keys used by get_profile_posts
2CREATE INDEX CONCURRENTLY IF NOT EXISTS comments_on_documents_document_idx
3 ON public.comments_on_documents (document);
4
5CREATE INDEX CONCURRENTLY IF NOT EXISTS document_mentions_in_bsky_document_idx
6 ON public.document_mentions_in_bsky (document);
7
8CREATE INDEX CONCURRENTLY IF NOT EXISTS documents_in_publications_document_idx
9 ON public.documents_in_publications (document);
10
11-- Expression index to look up documents by DID without adding a column
12-- at://did:plc:xxx/collection/rkey -> split_part gives did:plc:xxx
13CREATE INDEX CONCURRENTLY IF NOT EXISTS documents_identity_did_sort_idx
14 ON public.documents (split_part(uri, '/', 3), sort_date DESC, uri DESC);
15
16-- Rewrite get_profile_posts to use the expression index
17CREATE OR REPLACE FUNCTION get_profile_posts(
18 p_did text,
19 p_cursor_sort_date timestamptz DEFAULT NULL,
20 p_cursor_uri text DEFAULT NULL,
21 p_limit int DEFAULT 20
22)
23RETURNS TABLE (
24 uri text,
25 data jsonb,
26 sort_date timestamptz,
27 comments_count bigint,
28 mentions_count bigint,
29 recommends_count bigint,
30 publication_uri text,
31 publication_record jsonb,
32 publication_name text
33)
34LANGUAGE sql STABLE
35AS $$
36 SELECT
37 d.uri,
38 d.data,
39 d.sort_date,
40 (SELECT count(*) FROM comments_on_documents c WHERE c.document = d.uri),
41 (SELECT count(*) FROM document_mentions_in_bsky m WHERE m.document = d.uri),
42 (SELECT count(*) FROM recommends_on_documents r WHERE r.document = d.uri),
43 pub.uri,
44 pub.record,
45 pub.name
46 FROM documents d
47 LEFT JOIN LATERAL (
48 SELECT p.uri, p.record, p.name
49 FROM documents_in_publications dip
50 JOIN publications p ON p.uri = dip.publication
51 WHERE dip.document = d.uri
52 LIMIT 1
53 ) pub ON true
54 WHERE split_part(d.uri, '/', 3) = p_did
55 AND (
56 p_cursor_sort_date IS NULL
57 OR d.sort_date < p_cursor_sort_date
58 OR (d.sort_date = p_cursor_sort_date AND d.uri < p_cursor_uri)
59 )
60 ORDER BY d.sort_date DESC, d.uri DESC
61 LIMIT p_limit;
62$$;