rollup.sql

-- 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;