A possible solution to the object ordering problem described here https://gist.github.com/drsnyder/9277054.
-- One solution to the problem. Cache the ordering in an array. | |
-- Pros: | |
-- It's fast enough (~90ms) even with filtering | |
-- Updates are atomic. You are only updating one row in one table. | |
-- If the ordering is predictable (e.g. create time) then the | |
-- maintenance is fast and easy because you just append to the array. | |
-- Cons: | |
-- It requires maintenance. It's not a view and it's not a relation | |
-- in the formal sense (is that right?). CREATE TYPE object_container_order_type AS ( | |
-- Atypical access patterns. You have to unnest() and dereference. | |
CREATE TYPE object_container_order_type AS ( | |
object_id INTEGER, | |
category_id INTEGER | |
); | |
CREATE TABLE object_container_order ( | |
container_id INTEGER REFERENCES containers (id), | |
type VARCHAR(32), | |
order_objects object_container_order_type[], | |
UNIQUE (container_id, type) | |
); | |
-- Create and cache the ordering. | |
WITH object_positions AS ( | |
SELECT object_container_map.container_id, objects.id, containers.category_id, row_number() OVER (ORDER BY objects.id DESC) AS index | |
FROM objects | |
JOIN object_container_map ON ( | |
objects.id = object_container_map.object_id | |
AND object_container_map.is_default = false | |
) | |
JOIN object_container_map default_map ON ( | |
objects.id = default_map.object_id | |
AND default_map.is_default = true | |
) | |
JOIN containers ON ( | |
containers.id = default_map.container_id | |
) | |
WHERE objects.status = 1 AND containers.status = 1 AND object_container_map.container_id = 500001 | |
AND containers.category_id IN (2,3,4,5,6) | |
) | |
INSERT INTO object_container_order (container_id, type, order_objects) | |
SELECT 500001, 'id_desc', ARRAY( | |
SELECT (id, category_id)::object_container_order_type | |
FROM object_positions | |
ORDER BY index ASC | |
); | |
-- Same results as below but runs in ~90ms. | |
WITH container_500001 AS ( | |
SELECT (UNNEST(order_objects)).* | |
FROM object_container_order | |
WHERE container_id = 500001 AND type = 'id_desc' | |
), with_ordinal AS ( | |
SELECT object_id, category_id, row_number() OVER () AS index | |
FROM container_500001 | |
WHERE category_id IN (2,3,4,5,6) | |
) | |
SELECT row_number() OVER (), 500001, object_id, category_id, index | |
FROM with_ordinal | |
WHERE category_id IN (2,3,4,5,6) | |
AND index BETWEEN 50000 - 6 AND 50000 + 6 | |
ORDER BY index ASC; | |
-- Reference for the original query we are trying to optimize. | |
-- WITH object_positions AS ( | |
-- SELECT object_container_map.container_id, objects.id, containers.category_id, row_number() OVER (ORDER BY objects.id DESC) AS index | |
-- FROM objects | |
-- JOIN object_container_map ON ( | |
-- objects.id = object_container_map.object_id | |
-- AND object_container_map.is_default = false | |
-- ) | |
-- JOIN object_container_map default_map ON ( | |
-- objects.id = default_map.object_id | |
-- AND default_map.is_default = true | |
-- ) | |
-- JOIN containers ON ( | |
-- containers.id = default_map.container_id | |
-- ) | |
-- WHERE objects.status = 1 AND containers.status = 1 AND object_container_map.container_id = 500001 | |
-- AND containers.category_id IN (2,3,4,5,6) | |
-- | |
-- ) | |
-- SELECT row_number() OVER (), container_id, id AS object_id, category_id, index | |
-- FROM object_positions | |
-- WHERE index BETWEEN 50000 - 6 AND 50000 + 6 | |
-- ORDER BY index ASC; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment