Re: Slow Query - PostgreSQL 9.2 - Mailing list pgsql-admin
From | Saulo Merlo |
---|---|
Subject | Re: Slow Query - PostgreSQL 9.2 |
Date | |
Msg-id | SNT147-W452B55977F011F28E5A744D3CA0@phx.gbl Whole thread Raw |
In response to | Re: Slow Query - PostgreSQL 9.2 (bricklen <bricklen@gmail.com>) |
Responses |
Re: Slow Query - PostgreSQL 9.2
|
List | pgsql-admin |
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;
It seems on the EXPLAIN ANALYZE that it wasn't used!
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"()
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_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 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";
pgsql-admin by date: