Search Postgresql Archives

Re: Slow Query - PostgreSQL 9.2

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

 

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 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_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"()

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"()
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux