pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Saulo Merlo <smerlo50@outlook.com>
To: bricklen <bricklen@gmail.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Slow Query - PostgreSQL 9.2
Date: Tue, 12 Jan 2016 21:05:23 +0000
Message-ID: <SNT147-W4755D5F086D7B03444F05D3CA0@phx.gbl> (raw)
In-Reply-To: <SNT147-W452B55977F011F28E5A744D3CA0@phx.gbl>
References: <SNT406-EAS2795711667C13F9A77BAEA2D3CA0@phx.gbl>
	<CAGrpgQ9iCbzV4onDiw74++sxD8MZ2jKw0qjAGSLrZOETOrRkVg@mail.gmail.com>
	<SNT147-W452B55977F011F28E5A744D3CA0@phx.gbl>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>

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

view thread (6+ messages)

Message-ID: <SNT147-W4755D5F086D7B03444F05D3CA0@phx.gbl>
Permalink:  ../SNT147-W4755D5F086D7B03444F05D3CA0@phx.gbl/
Also on:    postgresql.org/message-id/SNT147-W4755D5F086D7B03444F05D3CA0@phx.gbl

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: smerlo50@outlook.com, bricklen@gmail.com
  Subject: Re: Slow Query - PostgreSQL 9.2
  In-Reply-To: <SNT147-W4755D5F086D7B03444F05D3CA0@phx.gbl>
  References: <SNT406-EAS2795711667C13F9A77BAEA2D3CA0@phx.gbl> <CAGrpgQ9iCbzV4onDiw74++sxD8MZ2jKw0qjAGSLrZOETOrRkVg@mail.gmail.com> <SNT147-W452B55977F011F28E5A744D3CA0@phx.gbl> <SNT147-W4755D5F086D7B03444F05D3CA0@phx.gbl>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox