Обсуждение: Slow Query - PostgreSQL 9.2
I've been trying to make this Query faster, but I had no success.
Do you guys have an idea about what else I can do?
I'll post below what I have done.
Thank you.
This can either be nfs_file_path or nfs_migration_date (both new columns). Adding an index on either of these columns
andusing them in the clause should improve things greatly.
QUERY:
SELECT
main.inode_id AS file_id,
main.file_data AS main_binary,
main.node_full_path AS filename,
main.last_modified AS date_created,
medium.inode_id AS medium_id,
medium.file_data AS medium_binary,
thumbnail.inode_id AS thumbnail_id,
thumbnail.file_data AS thumbnail_binary
FROM
gorfs.nodes AS main
INNER JOIN
gorfs.inode_segments AS iseg ON iseg.st_ino = main.parent_inode_id
AND main.relative_path = 'main'
AND main.object_type = 'S_IFREG'
AND iseg.nfs_migration_date IS NULL
AND (main.last_modified <
(transaction_timestamp() AT TIME ZONE 'UTC' - '1 months' :: INTERVAL))
AND iseg.st_ino_target = main.inode_id
LEFT JOIN
gorfs.nodes AS medium
ON medium.parent_inode_id = main.parent_inode_id
AND medium.relative_path = 'medium'
AND medium.object_type = 'S_IFREG'
LEFT JOIN
gorfs.nodes AS thumbnail
ON thumbnail.parent_inode_id = main.parent_inode_id
AND thumbnail.relative_path = 'thumbnail'
AND thumbnail.object_type = 'S_IFREG'
LIMIT
100;
INDEX CREATED:
CREATE INDEX CONCURRENTLY ix_inode_segments_nfs_file_path on gorfs.inode_segments USING btree ("full_path");
full_path:
ALTER TABLE gorfs.inode_segments ADD COLUMN full_path "gorfs"."absolute_pathname";
EXPLAIN ANALYZE:
"Limit (cost=1935606.57..4178326.49 rows=1 width=170) (actual time=199195.079..315313.338 rows=100 loops=1)"
" -> Nested Loop Left Join (cost=1935606.57..4178326.49 rows=1 width=170) (actual time=199195.076..315313.089
rows=100loops=1)"
" -> Nested Loop Left Join (cost=1935087.58..4177095.71 rows=1 width=138) (actual time=199195.015..315156.343
rows=100loops=1)"
" -> Nested Loop (cost=1934568.58..4176379.93 rows=1 width=98) (actual time=199162.474..314565.271
rows=100loops=1)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") =
("sb"."bits")::"bit")"
" Rows Removed by Join Filter: 34533"
" -> Nested Loop (cost=1934049.58..4175860.39 rows=1 width=103) (actual
time=196125.245..314086.043rows=34633 loops=1)"
" -> Nested Loop (cost=1934049.58..4175847.02 rows=1 width=86) (actual
time=196125.213..305961.431rows=34634 loops=1)"
" -> Hash Join (cost=1934049.58..4175833.65 rows=1 width=94) (actual
time=196094.683..238436.508rows=34634 loops=1)"
" Hash Cond: ((("p"."st_ino")::bigint = ("iseg"."st_ino")::bigint) AND
(("p"."st_ino_target")::bigint= ("iseg"."st_ino_target")::bigint))"
" -> Seq Scan on "inode_segments" "p" (cost=0.00..2233425.84 rows=303935
width=78)(actual time=0.046..34047.515 rows=4466887 loops=1)"
" Filter: ((CASE WHEN (("st_ino_target")::bigint = 2) THEN NULL::character
varyingELSE ("segment_index")::character varying END)::"text" = 'main'::"text")"
" Rows Removed by Filter: 25643122"
" -> Hash (cost=1929490.56..1929490.56 rows=303935 width=16) (actual
time=195921.025..195921.025rows=40682288 loops=1)"
" Buckets: 32768 Batches: 128 (originally 1) Memory Usage: 16385kB"
" -> Seq Scan on "inode_segments" "iseg" (cost=0.00..1929490.56
rows=303935width=16) (actual time=0.002..112215.501 rows=60787096 loops=1)"
" Filter: ("nfs_migration_date" IS NULL)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.36 rows=1 width=8)
(actualtime=1.942..1.943 rows=1 loops=34634)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR
((B'00000000000000001111000000000000'::"bit"& ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.36 rows=1 width=29) (actual
time=0.226..0.228rows=1 loops=34634)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" Filter: (("st_mtime")::timestamp without time zone < ("timezone"('UTC'::"text",
"transaction_timestamp"())- '1 mon'::interval))"
" Rows Removed by Filter: 0"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.52 rows=1 width=72) (actual
time=0.004..0.009rows=1 loops=34633)"
" Filter: ("constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name")"
" Rows Removed by Filter: 22"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (actual
time=0.003..0.035rows=23 loops=1)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (actual
time=0.096..0.796rows=23 loops=1)"
" SubPlan 5"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (actual time=0.023..0.024
rows=1loops=23)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01
rows=1000width=32) (actual time=0.009..0.013 rows=3 loops=23)"
" -> Nested Loop (cost=519.00..715.77 rows=1 width=48) (actual time=5.864..5.904 rows=1 loops=100)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") =
("sb"."bits")::"bit")"
" -> Nested Loop (cost=0.00..196.22 rows=1 width=33) (actual time=5.374..5.400 rows=1 loops=100)"
" -> Nested Loop (cost=0.00..182.86 rows=1 width=16) (actual time=4.802..4.809 rows=1
loops=100)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.36 rows=1 width=8)
(actualtime=0.008..0.010 rows=1 loops=100)"
" Index Cond: (("st_ino")::bigint = ("i"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR
((B'00000000000000001111000000000000'::"bit"& ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "p"
(cost=0.00..169.50rows=1 width=16) (actual time=4.788..4.790 rows=1 loops=100)"
" Index Cond: (("st_ino")::bigint = ("i"."st_ino")::bigint)"
" Filter: ((CASE WHEN (("st_ino_target")::bigint = 2) THEN NULL::character varying
ELSE("segment_index")::character varying END)::"text" = 'thumbnail'::"text")"
" Rows Removed by Filter: 1"
" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.35 rows=1 width=21) (actual
time=0.589..0.591rows=1 loops=96)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.52 rows=1 width=72) (actual
time=0.007..0.019rows=1 loops=96)"
" Filter: ("constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name")"
" Rows Removed by Filter: 22"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (actual
time=0.005..0.037rows=23 loops=1)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (actual
time=0.100..0.788rows=23 loops=1)"
" SubPlan 11"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (actual time=0.023..0.024
rows=1loops=23)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01
rows=1000width=32) (actual time=0.008..0.012 rows=3 loops=23)"
" SubPlan 3"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (actual time=0.492..0.493 rows=1
loops=96)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs"
(cost=0.00..171.55rows=40 width=574) (actual time=0.472..0.474 rows=1 loops=96)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> Nested Loop (cost=519.00..715.77 rows=1 width=48) (actual time=0.034..0.034 rows=0 loops=100)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") =
("sb"."bits")::"bit")"
" -> Nested Loop (cost=0.00..196.22 rows=1 width=33) (actual time=0.032..0.032 rows=0 loops=100)"
" -> Nested Loop (cost=0.00..182.86 rows=1 width=16) (actual time=0.029..0.029 rows=0 loops=100)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.36 rows=1 width=8) (actual
time=0.008..0.009rows=1 loops=100)"
" Index Cond: (("st_ino")::bigint = ("i"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" &
("st_mode")::"bit")= B'00000000000000000100000000000000'::"bit"))"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "p" (cost=0.00..169.50
rows=1width=16) (actual time=0.013..0.013 rows=0 loops=100)"
" Index Cond: (("st_ino")::bigint = ("i"."st_ino")::bigint)"
" Filter: ((CASE WHEN (("st_ino_target")::bigint = 2) THEN NULL::character varying ELSE
("segment_index")::charactervarying END)::"text" = 'medium'::"text")"
" Rows Removed by Filter: 2"
" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.35 rows=1 width=21) (never
executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.52 rows=1 width=72) (never executed)"
" Filter: ("constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name")"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"
" SubPlan 8"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000
width=32)(never executed)"
" SubPlan 2"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55
rows=40width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" SubPlan 1"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (actual time=1.523..1.524 rows=1 loops=100)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40
width=574)(actual time=1.512..1.514 rows=1 loops=100)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
"Total runtime: 315725.301 ms"
nfs_file_path - COLUMN
ALTER TABLE gorfs.inode_segments ADD COLUMN nfs_file_path "text";
nfs_migration_date - COLUMN - HAVE TO CREATE AN INDEX TO IT
ALTER TABLE gorfs.inode_segments ADD COLUMN nfs_migration_date timestamp without time zone;
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'sprimary key to ensure uniqueness per relevant scope
segment_index "gorfs"."pathname_component" NOT NULL, -- See st_no's column description for further details. The
meaningof 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
directoryinode segments (objects in the directory)
segment_data "bytea", -- Actual data segment. Meaningful only for S_IFLNK and S_IFREG....
nfs_file_path "text",
nfs_migration_date timestamp without time zone,
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"::bigintOR "st_ino"::bigint = 2)
)
WITH (
OIDS=FALSE
);
Sent from my phone
On Tue, Jan 12, 2016 at 12:58 AM, Saulo Merlo <smerlo50@outlook.com> wrote:
I've been trying to make this Query faster, but I had no success.
Do you guys have an idea about what else I can do?
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.
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.
Try the following partial indexes (they may or may not already exist, the structure of the tables is incomplete)
create index concurrently inode_segments_st_ino_st_ino_target_pidx
on gorfs.inode_segments (st_ino desc, st_ino_target desc)
where nfs_migration_data is null;
-- if there is no index on gorfs.nodes.last_modified, test a partial index:
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';
vacuum analyze verbose gorfs.nodes;
vacuum analyze verbose gorfs.inode_segments;
EXPLAIN (analyze, buffers)
SELECT main.inode_id AS file_id,
main.file_data AS main_binary,
main.node_full_path AS filename,
main.last_modified AS date_created,
medium.inode_id AS medium_id,
medium.file_data AS medium_binary,
thumbnail.inode_id AS thumbnail_id,
thumbnail.file_data AS thumbnail_binary
FROM gorfs.nodes AS main
INNER JOIN gorfs.inode_segments AS iseg ON
(iseg.st_ino = main.parent_inode_id
AND iseg.st_ino_target = main.inode_id)
LEFT JOIN gorfs.nodes AS medium ON
(medium.parent_inode_id = main.parent_inode_id
AND medium.relative_path = 'medium'
AND medium.object_type = 'S_IFREG')
LEFT JOIN gorfs.nodes AS thumbnail ON
(thumbnail.parent_inode_id = main.parent_inode_id
AND thumbnail.relative_path = 'thumbnail'
AND thumbnail.object_type = 'S_IFREG')
WHERE main.relative_path = 'main'
AND main.object_type = 'S_IFREG'
AND iseg.nfs_migration_date IS NULL
AND (main.last_modified < (transaction_timestamp() AT TIME ZONE 'UTC' - '1 months' :: INTERVAL))
LIMIT 100;
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";
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
From: smerlo50@outlook.com
To: bricklen@gmail.com
CC: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Slow Query - PostgreSQL 9.2
Date: Tue, 12 Jan 2016 20:28:53 +0000
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";