Home > mailing lists

Re: Slow Query - PostgreSQL 9.2 - Mailing list pgsql-admin

From	Saulo Merlo
Subject	Re: Slow Query - PostgreSQL 9.2
Date	January 13, 2016 00:05:31
Msg-id	SNT147-W4755D5F086D7B03444F05D3CA0@phx.gbl Whole thread Raw
In response to	Re: Slow Query - PostgreSQL 9.2 (Saulo Merlo <smerlo50@outlook.com>)
List	pgsql-admin

Tree view

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;

Link: http://explain.depesz.com/s/Swu

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

pgsql-admin by date:

From: Saulo Merlo
Date: 12 January 2016, 23:29:01
Subject: Re: Slow Query - PostgreSQL 9.2

From: Saulo Merlo
Date: 13 January 2016, 05:30:04
Subject: Query - Create PostgreSQL

Re: Slow Query - PostgreSQL 9.2 - Mailing list pgsql-admin

Previous

Next