Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
-- Another solution using a rollup table.
CREATE TABLE object_container_order_rollup (
container_id INTEGER REFERENCES containers(id),
category_id INTEGER REFERENCES categories(id),
object_id INTEGER REFERENCES objects(id),
type VARCHAR(32) NOT NULL,
index INTEGER NOT NULL,
PRIMARY KEY (container_id, type, category_id, object_id)
);
CREATE INDEX object_container_order_rollup_idx ON object_container_order_rollup(index);
-- CREATE INDEX foo ON object_container_order_rollup(container_id, type, category_id);
WITH object_positions AS (
SELECT object_container_map.container_id, objects.id, containers.category_id, row_number() OVER (PARTITION BY object_container_map.container_id 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 > 500000
ORDER BY objects.id DESC
)
INSERT INTO object_container_order_rollup (container_id, category_id, object_id, type, index)
SELECT container_id, category_id, id, 'id_desc', index
FROM object_positions;
EXPLAIN (ANALYZE, BUFFERS)
WITH container_500001 AS (
SELECT container_id, category_id, object_id, row_number() OVER () AS index
FROM object_container_order_rollup
WHERE container_id = 500001 AND type = 'id_desc' AND
category_id IN (2,3,4,5,6)
)
SELECT index, container_id, object_id, category_id
FROM container_500001
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