Last active
August 29, 2015 13:56
-
-
Save drsnyder/9277366 to your computer and use it in GitHub Desktop.
A possible solution to the object ordering problem described here https://gist.github.com/drsnyder/9277054.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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