Re: Slow Query - PostgreSQL 9.2

It would be helpful to supply the output of "\d gorfs.nodes" and "\d gorfs.inode_segments" so we can see the actual indexes and constraints that exist.
Also, pasting your EXPLAIN plan into http://explain.depesz.com/ and submitting the link in your post is usually easier to read than pasting it into an email.

Try the following partial indexes (they may or may not already exist, the structure of the tables is incomplete)

create index concurrently inode_segments_st_ino_st_ino_target_pidx
on gorfs.inode_segments (st_ino desc, st_ino_target desc)
where nfs_migration_data is null;

-- if there is no index on gorfs.nodes.last_modified, test a partial index:
create index concurrently nodes_last_modified_rel_path_obj_type_pidx
on gorfs.nodes (last_modified desc)
where relative_path = 'main'
and object_type = 'S_IFREG';

vacuum analyze verbose gorfs.nodes;
vacuum analyze verbose gorfs.inode_segments;


EXPLAIN (analyze, buffers)
SELECT main.inode_id AS file_id,
       main.file_data AS main_binary,
       main.node_full_path AS filename,
       main.last_modified AS date_created,
       medium.inode_id AS medium_id,
       medium.file_data AS medium_binary,
       thumbnail.inode_id AS thumbnail_id,
       thumbnail.file_data AS thumbnail_binary
FROM gorfs.nodes AS main
INNER JOIN gorfs.inode_segments AS iseg ON
        (iseg.st_ino = main.parent_inode_id
        AND iseg.st_ino_target = main.inode_id)
LEFT JOIN gorfs.nodes AS medium ON
        (medium.parent_inode_id = main.parent_inode_id
        AND medium.relative_path = 'medium'
        AND medium.object_type = 'S_IFREG')
LEFT JOIN gorfs.nodes AS thumbnail ON
        (thumbnail.parent_inode_id = main.parent_inode_id
        AND thumbnail.relative_path = 'thumbnail'
        AND thumbnail.object_type = 'S_IFREG')
WHERE main.relative_path = 'main'
AND main.object_type = 'S_IFREG'
AND iseg.nfs_migration_date IS NULL
AND (main.last_modified < (transaction_timestamp() AT TIME ZONE 'UTC' - '1 months' :: INTERVAL))
LIMIT 100;