A possible solution to the object ordering problem described here https://gist.github.com/drsnyder/9277054.

array-unest-solution.sql

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