Re: Slow Query - PostgreSQL 9.2

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

From: smerlo50(at)outlook(dot)com
To: bricklen(at)gmail(dot)com
CC: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] Slow Query - PostgreSQL 9.2
Date: Tue, 12 Jan 2016 20:28:53 +0000

Hi Bricklen.. Thank you for your help... Thank you so much...
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.
Remember that gorfs.nodes is a view.
I've created the INDEX you told me to: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;
Link: http://explain.depesz.com/s/Swu
It seems on the EXPLAIN ANALYZE that it wasn't used!
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"()
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"()

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.
Ok. I'll do it.

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