Re: Slow Query - PostgreSQL 9.2

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

DBNAME=# \d gorfs.nodes teste5.txt

                       View "gorfs.nodes"

       Column       |              Type              | Modifiers

--------------------+--------------------------------+-----------

 node_full_path     | "gorfs"."absolute_pathname"    |

 parent_inode_id    | "gorfs"."ino_t"                |

 inode_id           | "gorfs"."ino_t"                |

 relative_path      | character varying              |

 raw_mode           | bigint                         |

 object_type        | "gorfs"."mode_t_constant_name" |

 setuid             | boolean                        |

 setgid             | boolean                        |

 sticky             | boolean                        |

 permissions        | bit(9)                         |

 links_count        | "gorfs"."nlink_t"              |

 owner_uid          | "gorfs"."uid_t"                |

 owner_gid          | "gorfs"."gid_t"                |

 data_length        | "gorfs"."off_t"                |

 last_accessed      | "gorfs"."time_t"               |

 last_modified      | "gorfs"."time_t"               |

 last_changed       | "gorfs"."time_t"               |

 checksum_md5       | "md5_hash"                     |

 media_type         | "text"                         |

 target             | "text"                         |

 file_data          | "bytea"                        |

 is_external        | boolean                        |

 data_length_target | "gorfs"."off_t"                |

Triggers:

    "i_iud_action_changes" INSTEAD OF INSERT OR DELETE OR UPDATE ON "gorfs"."nodes" FOR EACH ROW EXECUTE PROCEDURE

"gorfs"."tf_nodes_action_changes"()

                 Table "gorfs.inode_segments"

       Column       |             Type             | Modifiers

--------------------+------------------------------+-----------

 st_ino             | "gorfs"."ino_t"              | not null

 segment_index      | "gorfs"."pathname_component" | not null

 st_ino_target      | "gorfs"."ino_t"              |

 full_path          | "gorfs"."absolute_pathname"  |

 segment_data       | "bytea"                      |

 nfs_migration_date | timestamp with time zone     |

 nfs_file_path      | "text"                       |

Indexes:

    "pk_inode_segments" PRIMARY KEY, "btree" ("st_ino", "segment_index")

    "uc_no_duplicate_full_paths" UNIQUE CONSTRAINT, "btree" ("full_path")

    "inode_segments_st_ino_target_pidx" "btree" ("st_ino" DESC, "st_ino_target" DESC) WHERE "nfs_migration_date" IS NULL

    "ix_inode_segments_climb_tree" "btree" ("segment_index", "st_ino_target")

    "ix_inode_segments_filter_by_subtree" "btree" ("full_path" "varchar_pattern_ops") WHERE "full_path" IS NOT NULL

    "ix_inode_segments_full_path_resolution" "btree" ("st_ino", "full_path")

    "ix_inode_segments_gsdi_pk" "btree" (("st_ino"::"text"), ("segment_index"::"text"))

    "ix_inode_segments_ja_files_lookup" "btree" ((

CASE

    WHEN "full_path"::"text" ~ '/[^/]*\.[^/]*$'::"text" THEN "upper"("regexp_replace"("full_path"::"text", '.*\.'::"text", ''::"text", 'g'::"text"))

    ELSE NULL::"text"

END)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text")

    "ix_inode_segments_nfs_file_path" "btree" ("full_path")

    "ix_inode_segments_nfs_migration_date" "btree" ("nfs_migration_date") WHERE "nfs_migration_date" IS NULL

    "ix_inode_segments_nfs_st_ino" "btree" ("st_ino")

    "ix_inode_segments_notes_clientids" "btree" (("split_part"("full_path"::"text", '/'::"text", 4)::integer)) WHERE "gorfs"."is_kaminski_note_path"("full_path"

::"text")

    "ix_inode_segments_notes_fileids" "btree" (("split_part"("full_path"::"text", '/'::"text", 8)::integer)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::

"text")

    "ix_inode_segments_notes_noteids" "btree" ((NULLIF("split_part"("full_path"::"text", '/'::"text", 6), 'unassigned'::"text")::integer)) WHERE "gorfs"."is_kam

inski_note_path"("full_path"::"text")

    "ix_inode_segments_segment_indexes" "btree" ("segment_index")

    "ix_inode_segments_st_ino_targets" "btree" ("st_ino_target")

    "ix_inode_segments_st_inos" "btree" ("st_ino")

Check constraints:

    "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)

Foreign-key constraints:

    "fk_host_inode_must_exist" FOREIGN KEY ("st_ino") REFERENCES "gorfs"."inodes"("st_ino")

    "fk_target_inode_must_exist" FOREIGN KEY ("st_ino_target") REFERENCES "gorfs"."inodes"("st_ino")

Triggers:

    "a_iud_update_inode" AFTER INSERT OR DELETE OR UPDATE ON "gorfs"."inode_segments" FOR EACH ROW EXECUTE PROCEDURE "gorfs"."tf_inode_segments_update_inodes"()

    "a_u_update_children" AFTER UPDATE ON "gorfs"."inode_segments" FOR EACH ROW EXECUTE PROCEDURE "gorfs"."tf_inode_segments_update_children"()

    "b_iu_calculate_columns" BEFORE INSERT OR UPDATE ON "gorfs"."inode_segments" FOR EACH ROW EXECUTE PROCEDURE "gorfs"."tf_inode_segments_calculate_columns"()

    "ct_valid_data_layouts_only" AFTER INSERT ON "gorfs"."inode_segments" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "gorfs"."tf_inode_se

gments_valid_data_layouts_only"()

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.

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

CREATE OR REPLACE VIEW gorfs.nodes AS 

 SELECT "p"."full_path" AS "node_full_path", "h"."st_ino" AS "parent_inode_id", 

    "t"."st_ino" AS "inode_id", 

        CASE

            WHEN "p"."st_ino_target"::bigint = 2 THEN NULL::character varying

            ELSE "p"."segment_index"::character varying

        END AS "relative_path", 

    "t"."st_mode"::bigint AS "raw_mode", "f"."constant_name" AS "object_type", 

    ("gorfs"."mode_t_bits"('S_ISUID'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode"::"bit")::integer <> 0 AS "setuid", 

    ("gorfs"."mode_t_bits"('S_ISGID'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode"::"bit")::integer <> 0 AS "setgid", 

    ("gorfs"."mode_t_bits"('S_ISVTX'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode"::"bit")::integer <> 0 AS "sticky", 

    "right"("concat"((("gorfs"."mode_t_bits"('S_IRWXU'::"gorfs"."mode_t_constant_name")::"bit" | "gorfs"."mode_t_bits"('S_IRWXG'::"gorfs"."mode_t_constant_name")::"bit") | "gorfs"."mode_t_bits"('S_IRWXO'::"gorfs"."mode_t_constant_name")::"bit") & "t"."st_mode"::"bit"), 9)::bit(9) AS "permissions", 

    "t"."st_nlink" AS "links_count", "t"."st_uid" AS "owner_uid", 

    "t"."st_gid" AS "owner_gid", "t"."st_size" AS "data_length", 

    "t"."st_atime" AS "last_accessed", "t"."st_mtime" AS "last_modified", 

    "t"."st_ctime" AS "last_changed", "t"."checksum_md5", 

    ("mst"."media_type" || '/'::"text") || "mst"."subtype_string"::"text" AS "media_type", 

        CASE

            WHEN "f"."constant_name" = 'S_IFLNK'::"gorfs"."mode_t_constant_name" THEN ( SELECT "convert_from"("ls"."segment_data", 'UTF8'::"name") AS "convert_from"

               FROM "gorfs"."inode_segments" "ls"

              WHERE "ls"."st_ino"::bigint = "p"."st_ino_target"::bigint)

            ELSE NULL::"text"

        END AS "target", 

        CASE

            WHEN "f"."constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name" THEN ( SELECT "string_agg"("fs"."segment_data", ''::"bytea" ORDER BY "fs"."segment_index") AS "string_agg"

               FROM "gorfs"."inode_segments" "fs"

              WHERE "fs"."st_ino"::bigint = "p"."st_ino_target"::bigint)

            ELSE NULL::"bytea"

        END AS "file_data", 

    "t"."external_size" IS NOT NULL AS "is_external", 

    "t"."external_size" AS "data_length_target"

   FROM "gorfs"."inode_segments" "p"

   JOIN "gorfs"."dir_inodes" "h" ON "h"."st_ino"::bigint = "p"."st_ino"::bigint

   JOIN "gorfs"."inodes" "t" ON "t"."st_ino"::bigint = "p"."st_ino_target"::bigint

   JOIN "gorfs"."mode_t_flags"() "f"("constant_name", "description", "bits", "bits_octal", "bits_hex", "bits_decimal") ON "f"."bits"::"bit" = ("gorfs"."mode_t_bits"('S_IFMT'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode"::"bit")

   LEFT JOIN "media_subtypes" "mst" ON "mst"."media_subtype_id" = "t"."media_subtype_id";