lemmy/migrations/2023-12-22-040137_make-mixed-sorting-directions-work-with-tuple-comparison/up.sql
dullbananas d8f9e8a64c
Post view: move cursor pagination to separate library, add backward pagination to PostQuery (#4320)
* stuff

* stuff

* crates.io

* Update up.sql

* Rerun federation tests

* Update post_view.rs

* Update post_view.rs

* Update up.sql

* Update utils.rs

* Fix precision loss

* Update up.sql

* Update down.sql

* remove unwrap

* Update post_view.rs

---------

Co-authored-by: Dessalines <dessalines@users.noreply.github.com>
2024-01-24 10:50:11 -05:00

18 lines
864 B
PL/PgSQL

CREATE FUNCTION reverse_timestamp_sort (t timestamp with time zone)
RETURNS bigint
AS $$
BEGIN
RETURN (-1000000 * EXTRACT(EPOCH FROM t))::bigint;
END;
$$
LANGUAGE plpgsql
IMMUTABLE PARALLEL SAFE;
CREATE INDEX idx_post_aggregates_community_published_asc ON public.post_aggregates USING btree (community_id, featured_local DESC, reverse_timestamp_sort (published) DESC);
CREATE INDEX idx_post_aggregates_featured_community_published_asc ON public.post_aggregates USING btree (community_id, featured_community DESC, reverse_timestamp_sort (published) DESC);
CREATE INDEX idx_post_aggregates_featured_local_published_asc ON public.post_aggregates USING btree (featured_local DESC, reverse_timestamp_sort (published) DESC);
CREATE INDEX idx_post_aggregates_published_asc ON public.post_aggregates USING btree (reverse_timestamp_sort (published) DESC);