Re: Slow Query - PostgreSQL 9.2
От | Saulo Merlo |
---|---|
Тема | Re: Slow Query - PostgreSQL 9.2 |
Дата | |
Msg-id | SNT147-W63D511F770E4E2D92B1CC4D3CA0@phx.gbl обсуждение исходный текст |
Ответ на | Re: Slow Query - PostgreSQL 9.2 (Saulo Merlo <smerlo50@outlook.com>) |
Список | pgsql-general |
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:
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 ASSELECT "p"."full_path" AS "node_full_path", "h"."st_ino" AS "parent_inode_id","t"."st_ino" AS "inode_id",CASEWHEN "p"."st_ino_target"::bigint = 2 THEN NULL::character varyingELSE "p"."segment_index"::character varyingEND 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",CASEWHEN "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",CASEWHEN "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"::bigintJOIN "gorfs"."inodes" "t" ON "t"."st_ino"::bigint = "p"."st_ino_target"::bigintJOIN "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 nullsegment_index | "gorfs"."pathname_component" | not nullst_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" (( CASEWHEN "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_segments_valid_data_layouts_only"()
gorfs.nodes:
DBNAME=# \d gorfs.nodes teste5.txtView "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"()
В списке pgsql-general по дате отправления:
Предыдущее
От: Andreas Joseph KroghДата:
Сообщение: Moving a large DB (> 500GB) to another DB with different locale