UPDATED:
Index created: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;
NEW EXPLAIN ANALYZE:http://explain.depesz.com/s/Swu
I also am able to create a temporary table to store migrations, which may be
the best option (no longer need to join new columns in query)
If you could help with that as well..Thank you
gorfs.nodes is a view:
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";
gorfs.inode_segments:
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_kaminski_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_segments_valid_data_layouts_only"()
gorfs.nodes:
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"()
