Re: Slow Query - PostgreSQL 9.2

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.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_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.

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

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux