Thank you!
"Limit (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.053..0.053 rows=0 loops=1)"" -> Nested Loop Left Join (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.050..0.050 rows=0 loops=1)"" -> Nested Loop Left Join (cost=1038.00..2526.07 rows=1 width=154) (actual time=0.047..0.047 rows=0 loops=1)"" -> Nested Loop (cost=519.01..824.17 rows=1 width=114) (actual time=0.045..0.045 rows=0 loops=1)"" -> Nested Loop (cost=519.01..818.20 rows=1 width=114) (actual time=0.043..0.043 rows=0 loops=1)"" -> Nested Loop (cost=519.00..808.69 rows=1 width=106) (actual time=0.041..0.041 rows=0 loops=1)"" -> Nested Loop (cost=519.00..793.43 rows=1 width=106) (actual time=0.040..0.040 rows=0 loops=1)"" -> Nested Loop (cost=519.00..778.17 rows=1 width=98) (actual time=0.038..0.038 rows=0 loops=1)"" -> Nested Loop (cost=519.00..762.91 rows=1 width=98) (actual time=0.036..0.036 rows=0 loops=1)"" -> Nested Loop (cost=519.00..747.64 rows=1 width=98) (actual time=0.034..0.034 rows=0 loops=1)"" Join Filter: (("t"."st_ino")::bigint = ("fd"."st_ino_target")::bigint)"" -> Nested Loop (cost=519.00..732.38 rows=1 width=90) (actual time=0.031..0.031 rows=0 loops=1)"" -> Nested Loop (cost=519.00..718.94 rows=1 width=98) (actual time=0.029..0.029 rows=0 loops=1)"" Join Filter: (CASE WHEN ("sb"."constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name") THEN (SubPlan 4) ELSE NULL::"bytea" END IS NOT NULL)"" -> Nested Loop (cost=519.00..531.96 rows=1 width=20) (actual time=0.027..0.027 rows=0 loops=1)"" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"" -> Index Scan using "ix_inode_segments_nfs" on "inodes" "t" (cost=0.01..11.76 rows=1 width=29) (actual time=0.026..0.026 rows=0 loops=1)"" Index Cond: (("st_ctime")::timestamp without time zone < ("timezone"('UTC'::"text", "transaction_timestamp"()) - '2 years'::interval))"" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"" 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 6"" -> 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)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.30 rows=1 width=78) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("t"."st_ino")::bigint)"" SubPlan 4"" -> 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=40 width=574) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"" 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 "ix_inode_segments_st_ino_targets" on "inode_segments" "fd" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("p"."st_ino_target")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "mv" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino")::bigint)"" Filter: (("segment_index")::"text" = 'main.with_name'::"text")"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fi" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("mv"."st_ino")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fn" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fi"."st_ino")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "ni" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fn"."st_ino")::bigint)"" -> Index Scan using "ja_notes_pkey" on "ja_notes" "n" (cost=0.00..9.50 rows=1 width=16) (never executed)"" Index Cond: ("id" = ("ni"."segment_index")::integer)"" -> Index Only Scan using "ix_jobs_top_by_client" on "ja_jobs" "j" (cost=0.00..5.96 rows=1 width=16) (never executed)"" Index Cond: ("id" = "n"."jobid")"" Heap Fetches: 0"" -> Nested Loop (cost=519.00..1701.89 rows=1 width=48) (never executed)"" Join Filter: (("sb"."bits")::"bit" = (B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit"))"" -> Nested Loop (cost=519.00..1688.45 rows=1 width=88) (never executed)"" -> Nested Loop (cost=0.00..1168.36 rows=1 width=24) (never executed)"" -> Nested Loop (cost=0.00..1154.93 rows=1 width=32) (never executed)"" -> Nested Loop (cost=0.00..1139.67 rows=1 width=16) (never executed)"" -> Nested Loop (cost=0.00..968.55 rows=1 width=16) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..170.61 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94 rows=1 width=16) (never executed)"" Index Cond: ((("st_ino")::bigint = ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" = 'medium.with_name'::"text"))"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("mv"."st_ino_target")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"" 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 9"" -> 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=40 width=574) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.42 rows=1 width=21) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> Nested Loop (cost=519.00..1702.00 rows=1 width=48) (never executed)"" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"" -> Nested Loop (cost=0.00..1181.79 rows=1 width=33) (never executed)"" -> Nested Loop (cost=0.00..1168.36 rows=1 width=24) (never executed)"" -> Nested Loop (cost=0.00..1154.93 rows=1 width=32) (never executed)"" -> Nested Loop (cost=0.00..1139.67 rows=1 width=16) (never executed)"" -> Nested Loop (cost=0.00..968.55 rows=1 width=16) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..170.61 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94 rows=1 width=16) (never executed)"" Index Cond: ((("st_ino")::bigint = ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" = 'thumbnail.with_name'::"text"))"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("mv"."st_ino_target")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"" 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.42 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.63 rows=23 width=72) (never executed)"" 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 12"" -> 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 3"" -> 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=40 width=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) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)""Total runtime: 1.740 ms" > From: clavadetsc...@swisspug.org > To: smerl...@outlook.com; vitaly.buro...@gmail.com > CC: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2 > Date: Mon, 11 Jan 2016 08:33:41 +0100 > > Hello > > > -----Original Message----- > > From: pgsql-general-ow...@postgresql.org > > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Saulo Merlo > > Sent: Montag, 11. Januar 2016 08:12 > > To: Vitaly Burovoy <vitaly.buro...@gmail.com> > > Cc: pgsql-general@postgresql.org > > Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2 > > > > gorgs.inode_segments: > > > > > > -- 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 > > ); > > There is no field st_ctime. > > > > > 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.7040...@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'; > > > > > > > > gorfs.noes: > > > > > > -- 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(); > > The value of st_ctime comes from table gorfs.inodes. So build the index on > that. > > > > > > > > > > > > Date: Sun, 10 Jan 2016 23:04:20 -0800 > > > Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2 > > > From: vitaly.buro...@gmail.com > > > To: smerl...@outlook.com > > > CC: pgsql-general@postgresql.org > > > > > > On 1/10/16, Saulo Merlo <smerl...@outlook.com> wrote: > > > > CREATE INDEX CONCURRENTLY index_name ON gorfs.inode_segments(st_ctime); > > > > ERROR: column "st_ctime" does not exist > > > > Look the error I've got > > > > > > > > Lucas > > > > > > > >> Date: Sun, 10 Jan 2016 22:43:21 -0800 > > > >> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2 > > > >> From: vitaly.buro...@gmail.com > > > >> To: smerl...@outlook.com > > > >> CC: pgsql-general@postgresql.org > > > >> > > > >> On 1/10/16, Saulo Merlo <smerl...@outlook.com> wrote: > > > >> > Hi Vitaly, > > > >> > > > > >> > Yep... gorfs.nodes is a view. > > > >> > And the schema is: gorfs.inode_segments > > > >> > So... CREATE INDEX index_name ON gorfs.inode_segments(st_ctime) > > > >> > Is that correct? It would be "st_ctime"? > > > >> If "inodes" is an alias for "gorfs.inode_segments" in your view, yes, > > > >> the above DDL is OK. According to EXPLAIN's "Filter" row the column > > > >> involving in comparison is st_ctime. > > > >> > > > >> Hint: you can create the index without blocking table using "CREATE > > > >> INDEX CONCURRENTLY": > > > >> http://www.postgresql.org/docs/9.2/static/sql-createindex.html > > > >> > > > >> > I've rewriten the query as well. Thank you for that! > > > >> > > > > >> > Thank you > > > >> > Lucas > > > >> > > > >> >> Date: Sun, 10 Jan 2016 21:23:01 -0800 > > > >> >> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2 > > > >> >> From: vitaly.buro...@gmail.com > > > >> >> To: smerl...@outlook.com > > > >> >> CC: pgsql-general@postgresql.org > > > >> >> > > > >> >> On 1/10/16, Vitaly Burovoy <vitaly.buro...@gmail.com> wrote: > > > >> >> > On 1/10/16, Saulo Merlo <smerl...@outlook.com> wrote: > > > >> >> >> I've got a slow query.. I'd like to make it faster.. Make add an > > > >> >> >> index? > > > >> >> >> Query: > > > >> >> >> SELECT > > > >> >> >> <<overquoting>> > > > >> >> >> FROM gorfs.nodes AS f > > > >> >> >> <<overquoting>> > > > >> >> >> WHERE f.file_data IS NOT NULL > > > >> >> >> AND ((transaction_timestamp() AT TIME ZONE 'UTC') > > > > >> >> >> (f.last_changed > > > >> >> >> + > > > >> >> >> '24 > > > >> >> >> months' :: INTERVAL)) LIMIT 100; > > > >> >> > > > > >> >> >> <<overquoting>> > > > >> >> >> "Total runtime: 94989.208 ms"What could I do to make it faster? > > > >> >> >> Thank > > > >> >> >> you. > > > >> >> > > > > >> >> > At least you can add an index: > > > >> >> > CREATE INDEX ON gorfs.nodes(last_changed) > > > >> >> > > > > >> >> > and rewrite part of WHERE clause to: > > > >> >> > (f.last_changed < (transaction_timestamp() AT TIME ZONE 'UTC' - > > > >> >> > '24 > > > >> >> > months'::INTERVAL)) > > > >> >> > > > > >> >> > It allows to decrease the slowest part of your query (sequence > > > >> >> > scanning of a table, all 13.5M rows): > > > >> >> >> -> Seq Scan on "inodes" "t" (cost=0.00..1411147.24 rows=13416537 > > > >> >> >> width=29) (actual time=94987.224..94987.224 rows=0 loops=1) > > > >> >> >> Filter: ("timezone"('UTC'::"text", "transaction_timestamp"()) > > > >> >> >> > > > > >> >> >> (("st_ctime")::timestamp without time zone + '2 > > > >> >> >> years'::interval)) > > > >> >> > > > > >> >> > compare that time to the one in the topmost row of EXPLAIN: > > > >> >> >> Limit (cost=1556.99..1336437.30 rows=100 width=186) (actual > > > >> >> >> time=94987.261..94987.261 rows=0 loops=1) > > > >> >> > > > >> >> Hmm. It seems that gorfs.nodes is a view. > > > >> >> So creating index should be something like (I have no idea that > > > >> >> schema > > > >> >> name for it): > > > >> >> CREATE INDEX ON _schema_name_.inodes(st_ctime) > > > > > > Please, post a definition of a table and a view (and all intermediate > > > views if any). > > > > > > Via psql it can be done via: > > > \d gorfs.inode_segments > > > \d+ gorfs.nodes > > > > > > -- > > > Best regards, > > > Vitaly Burovoy > > > > > > > > > -- > > > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > > > To make changes to your subscription: > > > http://www.postgresql.org/mailpref/pgsql-general > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general