Slow Query - PostgreSQL 9.2

I've been trying to make this Query faster, but I had no success.

Do you guys have an idea about what else I can do?
I'll post below what I have done.

Thank you.

This can either be nfs_file_path or nfs_migration_date (both new columns). Adding an index on either of these columns and using them in the clause should improve things greatly.

QUERY:

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 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))
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'
LIMIT
100;

INDEX CREATED:
CREATE INDEX CONCURRENTLY ix_inode_segments_nfs_file_path on gorfs.inode_segments USING btree ("full_path");

full_path:
ALTER TABLE gorfs.inode_segments ADD COLUMN full_path "gorfs"."absolute_pathname";

EXPLAIN ANALYZE:

"Limit (cost=1935606.57..4178326.49 rows=1 width=170) (actual time=199195.079..315313.338 rows=100 loops=1)"
" -> Nested Loop Left Join (cost=1935606.57..4178326.49 rows=1 width=170) (actual time=199195.076..315313.089 rows=100 loops=1)"
" -> Nested Loop Left Join (cost=1935087.58..4177095.71 rows=1 width=138) (actual time=199195.015..315156.343 rows=100 loops=1)"
" -> Nested Loop (cost=1934568.58..4176379.93 rows=1 width=98) (actual time=199162.474..314565.271 rows=100 loops=1)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"
" Rows Removed by Join Filter: 34533"
" -> Nested Loop (cost=1934049.58..4175860.39 rows=1 width=103) (actual time=196125.245..314086.043 rows=34633 loops=1)"
" -> Nested Loop (cost=1934049.58..4175847.02 rows=1 width=86) (actual time=196125.213..305961.431 rows=34634 loops=1)"
" -> Hash Join (cost=1934049.58..4175833.65 rows=1 width=94) (actual time=196094.683..238436.508 rows=34634 loops=1)"
" Hash Cond: ((("p"."st_ino")::bigint = ("iseg"."st_ino")::bigint) AND (("p"."st_ino_target")::bigint = ("iseg"."st_ino_target")::bigint))"
" -> Seq Scan on "inode_segments" "p" (cost=0.00..2233425.84 rows=303935 width=78) (actual time=0.046..34047.515 rows=4466887 loops=1)"
" Filter: ((CASE WHEN (("st_ino_target")::bigint = 2) THEN NULL::character varying ELSE ("segment_index")::character varying END)::"text" = 'main'::"text")"
" Rows Removed by Filter: 25643122"
" -> Hash (cost=1929490.56..1929490.56 rows=303935 width=16) (actual time=195921.025..195921.025 rows=40682288 loops=1)"
" Buckets: 32768 Batches: 128 (originally 1) Memory Usage: 16385kB"
" -> Seq Scan on "inode_segments" "iseg" (cost=0.00..1929490.56 rows=303935 width=16) (actual time=0.002..112215.501 rows=60787096 loops=1)"
" Filter: ("nfs_migration_date" IS NULL)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.36 rows=1 width=8) (actual time=1.942..1.943 rows=1 loops=34634)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.36 rows=1 width=29) (actual time=0.226..0.228 rows=1 loops=34634)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" Filter: (("st_mtime")::timestamp without time zone < ("timezone"('UTC'::"text", "transaction_timestamp"()) - '1 mon'::interval))"
" Rows Removed by Filter: 0"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.52 rows=1 width=72) (actual time=0.004..0.009 rows=1 loops=34633)"
" Filter: ("constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name")"
" Rows Removed by Filter: 22"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (actual time=0.003..0.035 rows=23 loops=1)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (actual time=0.096..0.796 rows=23 loops=1)"
" SubPlan 5"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (actual time=0.023..0.024 rows=1 loops=23)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (actual time=0.009..0.013 rows=3 loops=23)"
" -> Nested Loop (cost=519.00..715.77 rows=1 width=48) (actual time=5.864..5.904 rows=1 loops=100)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"
" -> Nested Loop (cost=0.00..196.22 rows=1 width=33) (actual time=5.374..5.400 rows=1 loops=100)"
" -> Nested Loop (cost=0.00..182.86 rows=1 width=16) (actual time=4.802..4.809 rows=1 loops=100)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.36 rows=1 width=8) (actual time=0.008..0.010 rows=1 loops=100)"
" Index Cond: (("st_ino")::bigint = ("i"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "p" (cost=0.00..169.50 rows=1 width=16) (actual time=4.788..4.790 rows=1 loops=100)"
" Index Cond: (("st_ino")::bigint = ("i"."st_ino")::bigint)"
" Filter: ((CASE WHEN (("st_ino_target")::bigint = 2) THEN NULL::character varying ELSE ("segment_index")::character varying END)::"text" = 'thumbnail'::"text")"
" Rows Removed by Filter: 1"
" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.35 rows=1 width=21) (actual time=0.589..0.591 rows=1 loops=96)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.52 rows=1 width=72) (actual time=0.007..0.019 rows=1 loops=96)"
" Filter: ("constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name")"
" Rows Removed by Filter: 22"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (actual time=0.005..0.037 rows=23 loops=1)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (actual time=0.100..0.788 rows=23 loops=1)"
" SubPlan 11"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (actual time=0.023..0.024 rows=1 loops=23)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (actual time=0.008..0.012 rows=3 loops=23)"
" SubPlan 3"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (actual time=0.492..0.493 rows=1 loops=96)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (actual time=0.472..0.474 rows=1 loops=96)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> Nested Loop (cost=519.00..715.77 rows=1 width=48) (actual time=0.034..0.034 rows=0 loops=100)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"
" -> Nested Loop (cost=0.00..196.22 rows=1 width=33) (actual time=0.032..0.032 rows=0 loops=100)"
" -> Nested Loop (cost=0.00..182.86 rows=1 width=16) (actual time=0.029..0.029 rows=0 loops=100)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.36 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=100)"
" Index Cond: (("st_ino")::bigint = ("i"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "p" (cost=0.00..169.50 rows=1 width=16) (actual time=0.013..0.013 rows=0 loops=100)"
" Index Cond: (("st_ino")::bigint = ("i"."st_ino")::bigint)"
" Filter: ((CASE WHEN (("st_ino_target")::bigint = 2) THEN NULL::character varying ELSE ("segment_index")::character varying END)::"text" = 'medium'::"text")"
" Rows Removed by Filter: 2"
" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.35 rows=1 width=21) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.52 rows=1 width=72) (never executed)"
" Filter: ("constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name")"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"
" SubPlan 8"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"
" SubPlan 2"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" SubPlan 1"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (actual time=1.523..1.524 rows=1 loops=100)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (actual time=1.512..1.514 rows=1 loops=100)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
"Total runtime: 315725.301 ms"

nfs_file_path - COLUMN
ALTER TABLE gorfs.inode_segments ADD COLUMN nfs_file_path "text";

nfs_migration_date - COLUMN - HAVE TO CREATE AN INDEX TO IT
ALTER TABLE gorfs.inode_segments ADD COLUMN nfs_migration_date timestamp without time zone;

TABLE gorfs.inode_segments:
CREATE TABLE gorfs.inode_segments
(
st_ino "gorfs"."ino_t" NOT NULL, -- Inode number the segment belongs to. alongside segment_index, it forms the table's primary key to ensure uniqueness per relevant scope
segment_index "gorfs"."pathname_component" NOT NULL, -- See st_no's column description for further details. The meaning of this column varies based on the host inode type:...
st_ino_target "gorfs"."ino_t", -- Target inode number. Meaningful for directory inode segments (objects in the directory)
full_path "gorfs"."absolute_pathname", -- Exploded absolute canonical path for quick lookups. Meaningful only for directory inode segments (objects in the directory)
segment_data "bytea", -- Actual data segment. Meaningful only for S_IFLNK and S_IFREG....
nfs_file_path "text",
nfs_migration_date timestamp without time zone,
CONSTRAINT pk_inode_segments PRIMARY KEY ("st_ino", "segment_index"),
CONSTRAINT fk_host_inode_must_exist FOREIGN KEY (st_ino)
REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_target_inode_must_exist FOREIGN KEY (st_ino_target)
REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT uc_no_duplicate_full_paths UNIQUE ("full_path"),
CONSTRAINT cc_only_root_can_be_its_own_parent CHECK ("st_ino_target" IS NULL OR "st_ino"::bigint <> "st_ino_target"::bigint OR "st_ino"::bigint = 2)
)
WITH (
OIDS=FALSE
);

Sent from my phone