Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
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