Re: Slow Query - PostgreSQL 9.2

-- Table: gorfs.inode_segments

-- DROP TABLE gorfs.inode_segments;

CREATE TABLE gorfs.inode_segments

(

  st_ino "gorfs"."ino_t" NOT NULL, -- Inode number the segment belongs to. alongside segment_index, it forms the table's primary key to ensure uniqueness per relevant scope

  segment_index "gorfs"."pathname_component" NOT NULL, -- See st_no's column description for further details. The meaning of this column varies based on the host inode type:...

  st_ino_target "gorfs"."ino_t", -- Target inode number. Meaningful for directory inode segments (objects in the directory)

  full_path "gorfs"."absolute_pathname", -- Exploded absolute canonical path for quick lookups. Meaningful only for directory inode segments (objects in the directory)

  segment_data "bytea", -- Actual data segment. Meaningful only for S_IFLNK and S_IFREG....

  CONSTRAINT pk_inode_segments PRIMARY KEY ("st_ino", "segment_index"),

  CONSTRAINT fk_host_inode_must_exist FOREIGN KEY (st_ino)

      REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION,

  CONSTRAINT fk_target_inode_must_exist FOREIGN KEY (st_ino_target)

      REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION,

  CONSTRAINT uc_no_duplicate_full_paths UNIQUE ("full_path"),

  CONSTRAINT 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)

)

WITH (

  OIDS=FALSE

);

S_IFSOCK:   0:   no data to store, no records here

S_IFLNK:    1:   contains the link target (see columns comments for details).

S_IFREG:    0+:  actual data segments, up to 64MB each  (see columns comments for details)

S_IFBLK:    0:   no data to store, no records here

S_IFDIR:    0+:  one record per object name in the directory

S_IFCHR:    0:   no data to store, no records here

S_IFIFO:    0:   no data to store, no records here

';

-- Index: gorfs.ix_inode_segments_climb_tree

-- DROP INDEX gorfs.ix_inode_segments_climb_tree;

CREATE INDEX ix_inode_segments_climb_tree

  ON gorfs.inode_segments

  USING btree

  ("segment_index" COLLATE pg_catalog."default", "st_ino_target");

-- Index: gorfs.ix_inode_segments_filter_by_subtree

-- DROP INDEX gorfs.ix_inode_segments_filter_by_subtree;

CREATE INDEX ix_inode_segments_filter_by_subtree

  ON gorfs.inode_segments

  USING btree

  ("full_path" COLLATE pg_catalog."default" varchar_pattern_ops)

  WHERE "full_path" IS NOT NULL;

COMMENT ON INDEX gorfs.ix_inode_segments_filter_by_subtree

  IS 'Allows looking for left-anchored paths (either regex or LIKE).

WARNING: as of 9.2 the index is not used when the comparison term is a non deterministic function (STABLE or VOLATILE).

See http://www.postgresql.org/message-id/5451D6C4.7040308@vuole.me

';

-- Index: gorfs.ix_inode_segments_full_path_resolution

-- DROP INDEX gorfs.ix_inode_segments_full_path_resolution;

CREATE INDEX ix_inode_segments_full_path_resolution

  ON gorfs.inode_segments

  USING btree

  ("st_ino", "full_path" COLLATE pg_catalog."default");

-- Index: gorfs.ix_inode_segments_gsdi_pk

-- DROP INDEX gorfs.ix_inode_segments_gsdi_pk;

CREATE INDEX ix_inode_segments_gsdi_pk

  ON gorfs.inode_segments

  USING btree

  (("st_ino"::"text") COLLATE pg_catalog."default", ("segment_index"::"text") COLLATE pg_catalog."default");

-- Index: gorfs.ix_inode_segments_ja_files_lookup

-- DROP INDEX gorfs.ix_inode_segments_ja_files_lookup;

CREATE INDEX ix_inode_segments_ja_files_lookup

  ON gorfs.inode_segments

  USING btree

  ((

CASE

    WHEN "full_path"::"text" ~ '/[^/]*\.[^/]*$'::"text" THEN "upper"("regexp_replace"("full_path"::"text", '.*\.'::"text", ''::"text", 'g'::"text"))

    ELSE NULL::"text"

END) COLLATE pg_catalog."default")

  WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text");

-- Index: gorfs.ix_inode_segments_notes_clientids

-- DROP INDEX gorfs.ix_inode_segments_notes_clientids;

CREATE INDEX ix_inode_segments_notes_clientids

  ON gorfs.inode_segments

  USING btree

  (("split_part"("full_path"::"text", '/'::"text", 4)::integer))

  WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text");

-- Index: gorfs.ix_inode_segments_notes_fileids

-- DROP INDEX gorfs.ix_inode_segments_notes_fileids;

CREATE INDEX ix_inode_segments_notes_fileids

  ON gorfs.inode_segments

  USING btree

  (("split_part"("full_path"::"text", '/'::"text", 8)::integer))

  WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text");

-- Index: gorfs.ix_inode_segments_notes_noteids

-- DROP INDEX gorfs.ix_inode_segments_notes_noteids;

CREATE INDEX ix_inode_segments_notes_noteids

  ON gorfs.inode_segments

  USING btree

  ((NULLIF("split_part"("full_path"::"text", '/'::"text", 6), 'unassigned'::"text")::integer))

  WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text");

-- Index: gorfs.ix_inode_segments_segment_indexes

-- DROP INDEX gorfs.ix_inode_segments_segment_indexes;

CREATE INDEX ix_inode_segments_segment_indexes

  ON gorfs.inode_segments

  USING btree

  ("segment_index" COLLATE pg_catalog."default");

-- Index: gorfs.ix_inode_segments_st_ino_targets

-- DROP INDEX gorfs.ix_inode_segments_st_ino_targets;

CREATE INDEX ix_inode_segments_st_ino_targets

  ON gorfs.inode_segments

  USING btree

  ("st_ino_target");

-- Index: gorfs.ix_inode_segments_st_inos

-- DROP INDEX gorfs.ix_inode_segments_st_inos;

CREATE INDEX ix_inode_segments_st_inos

  ON gorfs.inode_segments

  USING btree

  ("st_ino");

-- Trigger: a_iud_update_inode on gorfs.inode_segments

-- DROP TRIGGER a_iud_update_inode ON gorfs.inode_segments;

CREATE TRIGGER a_iud_update_inode

  AFTER INSERT OR UPDATE OR DELETE

  ON gorfs.inode_segments

  FOR EACH ROW

  EXECUTE PROCEDURE gorfs.tf_inode_segments_update_inodes();

COMMENT ON TRIGGER a_iud_update_inode ON gorfs.inode_segments IS 'See invoked function';

-- Trigger: a_u_update_children on gorfs.inode_segments

-- DROP TRIGGER a_u_update_children ON gorfs.inode_segments;

CREATE TRIGGER a_u_update_children

  AFTER UPDATE

  ON gorfs.inode_segments

  FOR EACH ROW

  EXECUTE PROCEDURE gorfs.tf_inode_segments_update_children();

-- Trigger: b_iu_calculate_columns on gorfs.inode_segments

-- DROP TRIGGER b_iu_calculate_columns ON gorfs.inode_segments;

CREATE TRIGGER b_iu_calculate_columns

  BEFORE INSERT OR UPDATE

  ON gorfs.inode_segments

  FOR EACH ROW

  EXECUTE PROCEDURE gorfs.tf_inode_segments_calculate_columns();

COMMENT ON TRIGGER b_iu_calculate_columns ON gorfs.inode_segments IS 'See invoked function';

-- Trigger: ct_valid_data_layouts_only on gorfs.inode_segments

-- DROP TRIGGER ct_valid_data_layouts_only ON gorfs.inode_segments;

CREATE CONSTRAINT TRIGGER ct_valid_data_layouts_only

  AFTER INSERT

  ON gorfs.inode_segments

  FOR EACH ROW

  EXECUTE PROCEDURE gorfs.tf_inode_segments_valid_data_layouts_only();

COMMENT ON TRIGGER ct_valid_data_layouts_only ON gorfs.inode_segments IS 'See invoked function';

-- View: gorfs.nodes

-- DROP VIEW gorfs.nodes;

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

-- Trigger: i_iud_action_changes on gorfs.nodes

-- DROP TRIGGER i_iud_action_changes ON gorfs.nodes;

CREATE TRIGGER i_iud_action_changes

  INSTEAD OF INSERT OR UPDATE OR DELETE

  ON gorfs.nodes

  FOR EACH ROW

  EXECUTE PROCEDURE gorfs.tf_nodes_action_changes();