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
andusing 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=100loops=1)" 
"        ->  Nested Loop Left Join  (cost=1935087.58..4177095.71 rows=1 width=138) (actual time=199195.015..315156.343
rows=100loops=1)" 
"              ->  Nested Loop  (cost=1934568.58..4176379.93 rows=1 width=98) (actual time=199162.474..314565.271
rows=100loops=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.043rows=34633 loops=1)" 
"                          ->  Nested Loop  (cost=1934049.58..4175847.02 rows=1 width=86) (actual
time=196125.213..305961.431rows=34634 loops=1)" 
"                                ->  Hash Join  (cost=1934049.58..4175833.65 rows=1 width=94) (actual
time=196094.683..238436.508rows=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
varyingELSE ("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.025rows=40682288 loops=1)" 
"                                            Buckets: 32768  Batches: 128 (originally 1)  Memory Usage: 16385kB"
"                                            ->  Seq Scan on "inode_segments" "iseg"  (cost=0.00..1929490.56
rows=303935width=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)
(actualtime=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.228rows=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.009rows=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.035rows=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.796rows=23 loops=1)" 
"                                  SubPlan 5"
"                                    ->  Aggregate  (cost=22.51..22.52 rows=1 width=32) (actual time=0.023..0.024
rows=1loops=23)" 
"                                          ->  Function Scan on "regexp_split_to_table" "digits"  (cost=0.01..10.01
rows=1000width=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)
(actualtime=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.50rows=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.591rows=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.019rows=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.037rows=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.788rows=23 loops=1)" 
"                                  SubPlan 11"
"                                    ->  Aggregate  (cost=22.51..22.52 rows=1 width=32) (actual time=0.023..0.024
rows=1loops=23)" 
"                                          ->  Function Scan on "regexp_split_to_table" "digits"  (cost=0.01..10.01
rows=1000width=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.55rows=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.009rows=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=1width=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")::charactervarying 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=40width=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'sprimary key to ensure uniqueness per relevant scope 
  segment_index "gorfs"."pathname_component" NOT NULL, -- See st_no's column description for further details. The
meaningof 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
directoryinode 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"::bigintOR "st_ino"::bigint = 2) 
)
WITH (
  OIDS=FALSE
);



Sent from my phone