Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aJ68C-0003Jg-KF for pgsql-admin@arkaria.postgresql.org; Tue, 12 Jan 2016 21:05:32 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aJ68C-0005kP-3U for pgsql-admin@arkaria.postgresql.org; Tue, 12 Jan 2016 21:05:32 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aJ68B-0005jy-CE for pgsql-admin@postgresql.org; Tue, 12 Jan 2016 21:05:31 +0000 Received: from snt004-omc3s43.hotmail.com ([65.54.51.80]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aJ685-0001hr-Vy for pgsql-admin@postgresql.org; Tue, 12 Jan 2016 21:05:30 +0000 Received: from SNT147-W4 ([65.55.90.137]) by SNT004-OMC3S43.hotmail.com over TLS secured channel with Microsoft SMTPSVC(7.5.7601.23008); Tue, 12 Jan 2016 13:05:23 -0800 X-TMN: [iTiiMAVoVnhQDIAlE1I7Z9MrMKEJ0fGs] X-Originating-Email: [smerlo50@outlook.com] Message-ID: Content-Type: multipart/alternative; boundary="_00c77afc-fea6-48b4-9da1-6333348d4bcf_" From: Saulo Merlo To: bricklen CC: "pgsql-admin@postgresql.org" Subject: Re: Slow Query - PostgreSQL 9.2 Date: Tue, 12 Jan 2016 21:05:23 +0000 Importance: Normal In-Reply-To: References: , , MIME-Version: 1.0 X-OriginalArrivalTime: 12 Jan 2016 21:05:23.0385 (UTC) FILETIME=[F3E8AA90:01D14D7C] X-Pg-Spam-Score: -2.4 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --_00c77afc-fea6-48b4-9da1-6333348d4bcf_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I also am able to create a temporary table to store migrations=2C 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=2C 12 Jan 2016 20:28:53 +0000 =0A= =0A= =0A= 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_segme= nts_st_ino_target_pidx on gorfs.inode_segments (st_ino desc=2C st_ino_targe= t desc) where nfs_migration_date is null=3B Link: http://explain.depesz.com/s/Swu It seems on the EXPLAIN ANALYZE that it wasn't used! gorfs.nodes: DBNAME=3D# \d gorfs.nodes teste5.txt View "gorfs.node= s" Column | Type | Modifiers---------= -----------+--------------------------------+----------- node_full_path = | "gorfs"."absolute_pathname" | parent_inode_id | "gorfs"."ino_t" = | inode_id | "gorfs"."ino_t" | relati= ve_path | character varying | raw_mode | bigint= | object_type | "gorfs"."mode_t_constant_na= me" | setuid | boolean | setgid = | boolean | sticky | boolean = | permissions | bit(9) | lin= ks_count | "gorfs"."nlink_t" | owner_uid | "go= rfs"."uid_t" | owner_gid | "gorfs"."gid_t" = | data_length | "gorfs"."off_t" | last_accesse= d | "gorfs"."time_t" | last_modified | "gorfs"."tim= e_t" | last_changed | "gorfs"."time_t" | = checksum_md5 | "md5_hash" | media_type | = "text" | target | "text" = | file_data | "bytea" | is_extern= al | boolean | data_length_target | "gorfs"."= off_t" |Triggers: "i_iud_action_changes" INSTEAD OF INSER= T OR DELETE OR UPDATE ON "gorfs"."nodes" FOR EACH ROW EXECUTE PROCEDURE"gor= fs"."tf_nodes_action_changes"() gorfs.inode_segments: Table "gorfs.inode_segments" Column | = Type | Modifiers--------------------+----------------------= --------+----------- st_ino | "gorfs"."ino_t" | no= t null segment_index | "gorfs"."pathname_component" | not null st_ino_= target | "gorfs"."ino_t" | full_path | "gorfs"."= absolute_pathname" | segment_data | "bytea" | n= fs_migration_date | timestamp with time zone | nfs_file_path | "te= xt" |Indexes: "pk_inode_segments" PRIMARY KEY=2C "= btree" ("st_ino"=2C "segment_index") "uc_no_duplicate_full_paths" UNIQUE= CONSTRAINT=2C "btree" ("full_path") "inode_segments_st_ino_target_pidx"= "btree" ("st_ino" DESC=2C "st_ino_target" DESC) WHERE "nfs_migration_date"= IS NULL "ix_inode_segments_climb_tree" "btree" ("segment_index"=2C "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"=2C "full_path") "ix_inode_segmen= ts_gsdi_pk" "btree" (("st_ino"::"text")=2C ("segment_index"::"text")) "i= x_inode_segments_ja_files_lookup" "btree" ((CASE WHEN "full_path"::"text= " ~ '/[^/]*\.[^/]*$'::"text" THEN "upper"("regexp_replace"("full_path"::"te= xt"=2C '.*\.'::"text"=2C ''::"text"=2C 'g'::"text")) ELSE NULL::"text"EN= D)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text") "ix_inode= _segments_nfs_file_path" "btree" ("full_path") "ix_inode_segments_nfs_mi= gration_date" "btree" ("nfs_migration_date") WHERE "nfs_migration_date" IS = NULL "ix_inode_segments_nfs_st_ino" "btree" ("st_ino") "ix_inode_segm= ents_notes_clientids" "btree" (("split_part"("full_path"::"text"=2C '/'::"t= ext"=2C 4)::integer)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::"t= ext") "ix_inode_segments_notes_fileids" "btree" (("split_part"("full_pat= h"::"text"=2C '/'::"text"=2C 8)::integer)) WHERE "gorfs"."is_kaminski_note_= path"("full_path"::"text") "ix_inode_segments_notes_noteids" "btree" ((N= ULLIF("split_part"("full_path"::"text"=2C '/'::"text"=2C 6)=2C 'unassigned'= ::"text")::integer)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::"te= xt") "ix_inode_segments_segment_indexes" "btree" ("segment_index") "i= x_inode_segments_st_ino_targets" "btree" ("st_ino_target") "ix_inode_seg= ments_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 =3D 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") R= EFERENCES "gorfs"."inodes"("st_ino")Triggers: "a_iud_update_inode" AFTER= INSERT OR DELETE OR UPDATE ON "gorfs"."inode_segments" FOR EACH ROW EXECUT= E PROCEDURE "gorfs"."tf_inode_segments_update_inodes"() "a_u_update_chil= dren" AFTER UPDATE ON "gorfs"."inode_segments" FOR EACH ROW EXECUTE PROCEDU= RE "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 INIT= IALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "gorfs"."tf_inode_segments_v= alid_data_layouts_only"() Also=2C pasting your EXPLAIN plan into http://explain.depesz.com/ and submi= tting 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 =3D 'main' and object_type =3D 'S_IFREG'=3B gorfs.nodes is a view: CREATE OR REPLACE VIEW gorfs.nodes AS SELECT "p"."full_path" AS "node_full= _path"=2C "h"."st_ino" AS "parent_inode_id"=2C "t"."st_ino" AS "inode_i= d"=2C CASE WHEN "p"."st_ino_target"::bigint =3D 2 THEN N= ULL::character varying ELSE "p"."segment_index"::character varyi= ng END AS "relative_path"=2C "t"."st_mode"::bigint AS "raw_mode"= =2C "f"."constant_name" AS "object_type"=2C ("gorfs"."mode_t_bits"('S_I= SUID'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode"::"bit")::inte= ger <> 0 AS "setuid"=2C ("gorfs"."mode_t_bits"('S_ISGID'::"gorfs"."mode= _t_constant_name")::"bit" & "t"."st_mode"::"bit")::integer <> 0 AS "setgid"= =2C ("gorfs"."mode_t_bits"('S_ISVTX'::"gorfs"."mode_t_constant_name")::= "bit" & "t"."st_mode"::"bit")::integer <> 0 AS "sticky"=2C "right"("con= cat"((("gorfs"."mode_t_bits"('S_IRWXU'::"gorfs"."mode_t_constant_name")::"b= it" | "gorfs"."mode_t_bits"('S_IRWXG'::"gorfs"."mode_t_constant_name")::"bi= t") | "gorfs"."mode_t_bits"('S_IRWXO'::"gorfs"."mode_t_constant_name")::"bi= t") & "t"."st_mode"::"bit")=2C 9)::bit(9) AS "permissions"=2C "t"."st_n= link" AS "links_count"=2C "t"."st_uid" AS "owner_uid"=2C "t"."st_gid" A= S "owner_gid"=2C "t"."st_size" AS "data_length"=2C "t"."st_atime" AS "l= ast_accessed"=2C "t"."st_mtime" AS "last_modified"=2C "t"."st_ctime" AS= "last_changed"=2C "t"."checksum_md5"=2C ("mst"."media_type" || '/'::"t= ext") || "mst"."subtype_string"::"text" AS "media_type"=2C CASE = WHEN "f"."constant_name" =3D 'S_IFLNK'::"gorfs"."mode_t_constant_na= me" THEN ( SELECT "convert_from"("ls"."segment_data"=2C 'UTF8'::"name") AS = "convert_from" FROM "gorfs"."inode_segments" "ls" = WHERE "ls"."st_ino"::bigint =3D "p"."st_ino_target"::bigint) E= LSE NULL::"text" END AS "target"=2C CASE WHEN "f"= ."constant_name" =3D 'S_IFREG'::"gorfs"."mode_t_constant_name" THEN ( SELEC= T "string_agg"("fs"."segment_data"=2C ''::"bytea" ORDER BY "fs"."segment_in= dex") AS "string_agg" FROM "gorfs"."inode_segments" "fs" = WHERE "fs"."st_ino"::bigint =3D "p"."st_ino_target"::bigint) = ELSE NULL::"bytea" END AS "file_data"=2C "t"."external_siz= e" IS NOT NULL AS "is_external"=2C "t"."external_size" AS "data_length_= target" FROM "gorfs"."inode_segments" "p" JOIN "gorfs"."dir_inodes" "h"= ON "h"."st_ino"::bigint =3D "p"."st_ino"::bigint JOIN "gorfs"."inodes" "= t" ON "t"."st_ino"::bigint =3D "p"."st_ino_target"::bigint JOIN "gorfs"."= mode_t_flags"() "f"("constant_name"=2C "description"=2C "bits"=2C "bits_oct= al"=2C "bits_hex"=2C "bits_decimal") ON "f"."bits"::"bit" =3D ("gorfs"."mod= e_t_bits"('S_IFMT'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode":= :"bit") LEFT JOIN "media_subtypes" "mst" ON "mst"."media_subtype_id" =3D = "t"."media_subtype_id"=3B = --_00c77afc-fea6-48b4-9da1-6333348d4bcf_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I also am able to create a temporary table = to store migrations=2C which may be the best option (no longer need to join= new columns in query)

If you could help with that as well..=
Tha= nk you



From: sm= erlo50@outlook.com
To: bricklen@gmail.com
CC: pgsql-admin@postgresql.= org
Subject: Re: [ADMIN] Slow Query - PostgreSQL 9.2
Date: Tue=2C 12 = Jan 2016 20:28:53 +0000

=0A= =0A= =0A=
Hi Bricklen.. Tha= nk you for your help... Thank you so much...

=
It would be h= elpful to supply the output of "\d gorfs.nodes" and "\d gorfs.inode_segment= s" 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=2C st_ino_target desc) whe= re nfs_migration_date is null=3B


It seems =3B on the EXPLAIN ANALYZE t= hat it wasn't used!

gorfs.nodes:

<= blockquote style=3D"border:none=3Bpadding:0px=3B">DBNAME=3D# \d gorfs.nodes teste5.txt
 =3B  =3B  =3B  =3B  =3B  =3B  =3B  = =3B  =3B  =3B  =3B  =3BView "gorfs.nodes"
 =3B  =3B  =3B  =3BColumn  =3B  =3B &= nbsp=3B |  =3B  =3B  =3B  =3B  =3B  =3B  =3BTyp= e  =3B  =3B  =3B  =3B  =3B  =3B  =3B| Modifiers=
--------------------+----------------------= ----------+-----------
 =3Bnode_full_pa= th  =3B  =3B | "gorfs"."absolute_pathname"  =3B  =3B|
 =3Bparent_inode_id  =3B  =3B| "gorfs= "."ino_t"  =3B  =3B  =3B  =3B  =3B  =3B  =3B &n= bsp=3B|
 =3Binode_id  =3B  =3B=  =3B  =3B  =3B | "gorfs"."ino_t"  =3B  =3B  =3B &n= bsp=3B  =3B  =3B  =3B  =3B|
=  =3Brelative_path  =3B  =3B  =3B| character varying  = =3B  =3B  =3B  =3B  =3B  =3B  =3B|
 =3Braw_mode  =3B  =3B  =3B  =3B  = =3B | bigint  =3B  =3B  =3B  =3B  =3B  =3B  =3B=  =3B  =3B  =3B  =3B  =3B |
 =3Bobject_type  =3B  =3B  =3B  =3B| "gorfs"."mode= _t_constant_name" |
 =3Bsetuid  =3B =  =3B  =3B  =3B  =3B  =3B | boolean  =3B  =3B &n= bsp=3B  =3B  =3B  =3B  =3B  =3B  =3B  =3B  = =3B  =3B|
 =3Bsetgid  =3B  = =3B  =3B  =3B  =3B  =3B | boolean  =3B  =3B  = =3B  =3B  =3B  =3B  =3B  =3B  =3B  =3B  =3B=  =3B|
 =3Bsticky  =3B  =3B =  =3B  =3B  =3B  =3B | boolean  =3B  =3B  =3B &n= bsp=3B  =3B  =3B  =3B  =3B  =3B  =3B  =3B  = =3B|
=  =3Bpermissions  =3B  =3B &= nbsp=3B  =3B| bit(9)  =3B  =3B  =3B  =3B  =3B  = =3B  =3B  =3B  =3B  =3B  =3B  =3B |
 =3Blinks_count  =3B  =3B  =3B  =3B| "g= orfs"."nlink_t"  =3B  =3B  =3B  =3B  =3B  =3B  = =3B|
=  =3Bowner_uid  =3B  =3B &nb= sp=3B  =3B  =3B| "gorfs"."uid_t"  =3B  =3B  =3B  = =3B  =3B  =3B  =3B  =3B|
&nb= sp=3Bowner_gid  =3B  =3B  =3B  =3B  =3B| "gorfs"."gid_t= "  =3B  =3B  =3B  =3B  =3B  =3B  =3B  =3B|<= /span>
 =3Bdata_length  =3B  =3B  = =3B  =3B| "gorfs"."off_t"  =3B  =3B  =3B  =3B  =3B =  =3B  =3B  =3B|
 =3Blast_acc= essed  =3B  =3B  =3B| "gorfs"."time_t"  =3B  =3B  = =3B  =3B  =3B  =3B  =3B |
&n= bsp=3Blast_modified  =3B  =3B  =3B| "gorfs"."time_t"  =3B &= nbsp=3B  =3B  =3B  =3B  =3B  =3B |
<= blockquote style=3D"border:none=3Bpadding:0px=3B"> =3Blast_changed  =3B  =3B  =3B | "gorfs"."time_= t"  =3B  =3B  =3B  =3B  =3B  =3B  =3B |<= /blockquote>
 =3Bchecksum_md5  =3B  =3B  =3B | = "md5_hash"  =3B  =3B  =3B  =3B  =3B  =3B  =3B &= nbsp=3B  =3B  =3B |
 =3Bmedia_ty= pe  =3B  =3B  =3B  =3B | "text"  =3B  =3B  =3B =  =3B  =3B  =3B  =3B  =3B  =3B  =3B  =3B &nb= sp=3B |
 =3Btarget  =3B  =3B &= nbsp=3B  =3B  =3B  =3B | "text"  =3B  =3B  =3B &nbs= p=3B  =3B  =3B  =3B  =3B  =3B  =3B  =3B  = =3B |
 =3Bfile_data  =3B  =3B &n= bsp=3B  =3B  =3B| "bytea"  =3B  =3B  =3B  =3B  = =3B  =3B  =3B  =3B  =3B  =3B  =3B  =3B|<= /blockquote>
 =3Bis_external  =3B  =3B  =3B &nb= sp=3B| boolean  =3B  =3B  =3B  =3B  =3B  =3B  = =3B  =3B  =3B  =3B  =3B  =3B|
 =3Bdata_length_target | "gorfs"."off_t"  =3B  =3B  = =3B  =3B  =3B  =3B  =3B  =3B|
Triggers:
 =3B  =3B "i_iud_act= ion_changes" INSTEAD OF INSERT OR DELETE OR UPDATE ON "gorfs"."nodes" FOR E= ACH ROW EXECUTE PROCEDURE
"gorfs"."tf_nodes_= action_changes"()

gorfs.inode_segments:

 =3B  =3B  =3B  =3B  =3B  =3B  =3B &= nbsp=3B  =3BTable "gorfs.inode_segments"
 =3B  =3B  =3B  =3BColumn  =3B=  =3B  =3B |  =3B  =3B  =3B  =3B  =3B  =3B = Type  =3B  =3B  =3B  =3B  =3B  =3B | Modifiers
--------------------+---= ---------------------------+-----------
 =3Bst_ino  =3B  =3B  =3B  =3B  = =3B  =3B | "gorfs"."ino_t"  =3B  =3B  =3B  =3B  =3B=  =3B  =3B| not null
 =3Bsegment_index  =3B  =3B  =3B| "gorfs"."pathname_= component" | not null
=  =3Bst_ino_target  =3B  =3B  =3B| "gorfs"."ino_t"  =3B =  =3B  =3B  =3B  =3B  =3B  =3B|
 =3Bfull_path  =3B  =3B  = =3B  =3B  =3B| "gorfs"."absolute_pathname"  =3B|
 =3Bsegment_data  =3B  = =3B  =3B | "bytea"  =3B  =3B  =3B  =3B  =3B  = =3B  =3B  =3B  =3B  =3B  =3B|
 =3Bnfs_migration_date | timestamp with tim= e zone  =3B  =3B |
 =3Bnfs_file_path  =3B  =3B  =3B| "text"  =3B &nbs= p=3B  =3B  =3B  =3B  =3B  =3B  =3B  =3B  = =3B  =3B |
Indexes= :
 =3B  =3B "p= k_inode_segments" PRIMARY KEY=2C "btree" ("st_ino"=2C "segment_index")
 =3B  =3B "uc_no= _duplicate_full_paths" UNIQUE CONSTRAINT=2C "btree" ("full_path")
 =3B  =3B "inode_segmen= ts_st_ino_target_pidx" "btree" ("st_ino" DESC=2C "st_ino_target" DESC) WHER= E "nfs_migration_date" IS NULL
 =3B  =3B "ix_inode_segments_climb_tree" "btree" ("segment= _index"=2C "st_ino_target")
 =3B  =3B "ix_inode_segments_filter_by_subtree" "btree" ("ful= l_path" "varchar_pattern_ops") WHERE "full_path" IS NOT NULL
 =3B  =3B "ix_inode_segments= _full_path_resolution" "btree" ("st_ino"=2C "full_path")
=
<= span style=3D"font-size:12.8px=3B"> =3B  =3B "ix_inode_segments_gsd= i_pk" "btree" (("st_ino"::"text")=2C ("segment_index"::"text"))
 =3B  =3B "ix_inode_segme= nts_ja_files_lookup" "btree" ((
CASE
 = =3B  =3B WHEN "full_path"::"text" ~ '/[^/]*\.[^/]*$'::"text" THEN "uppe= r"("regexp_replace"("full_path"::"text"=2C '.*\.'::"text"=2C ''::"text"=2C = 'g'::"text"))
 =3B=  =3B ELSE NULL::"text"
END)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text")
 =3B  =3B "ix_in= ode_segments_nfs_file_path" "btree" ("full_path")
 =3B  =3B "ix_inode_segments_nfs_migrat= ion_date" "btree" ("nfs_migration_date") WHERE "nfs_migration_date" IS NULL=
 =3B  =3B "ix= _inode_segments_nfs_st_ino" "btree" ("st_ino")
 =3B  =3B "ix_inode_segments_notes_clienti= ds" "btree" (("split_part"("full_path"::"text"=2C '/'::"text"=2C 4)::intege= r)) WHERE "gorfs"."is_kaminski_note_path"("full_path"
::"text")
 =3B  =3B "ix_inode_segments_notes_fileids" "bt= ree" (("split_part"("full_path"::"text"=2C '/'::"text"=2C 8)::integer)) WHE= RE "gorfs"."is_kaminski_note_path"("full_path"::
"text")
 =3B  =3B "ix_inode_segments_notes_noteids" "btree" ((= NULLIF("split_part"("full_path"::"text"=2C '/'::"text"=2C 6)=2C 'unassigned= '::"text")::integer)) WHERE "gorfs"."is_kam
=
inski_note_path"("full_path"::"text")
 =3B  =3B "ix_inode_segments_= segment_indexes" "btree" ("segment_index")
<= blockquote style=3D"border:none=3Bpadding:0px=3B">
 =3B  =3B "ix_inode_segments_st_ino_targets" "= btree" ("st_ino_target")
 =3B  =3B "ix_inode_segments_st_inos" "btree" ("st_ino")
Check constraints:
<= div>
 =3B  =3B "cc_only_roo= t_can_be_its_own_parent" CHECK ("st_ino_target" IS NULL OR "st_ino"::bigint= <=3B>=3B "st_ino_target"::bigint OR "st_ino"::bigint =3D 2)
Foreign-key constraints:<= /div>
=
 =3B  =3B "fk_host_in= ode_must_exist" FOREIGN KEY ("st_ino") REFERENCES "gorfs"."inodes"("st_ino"= )
 =3B  =3B "f= k_target_inode_must_exist" FOREIGN KEY ("st_ino_target") REFERENCES "gorfs"= ."inodes"("st_ino")
Tr= iggers:
 =3B  = =3B "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"()
 =3B &= nbsp=3B "a_u_update_children" AFTER UPDATE ON "gorfs"."inode_segments" FOR = EACH ROW EXECUTE PROCEDURE "gorfs"."tf_inode_segments_update_children"()
 =3B  =3B "b_iu_= calculate_columns" BEFORE INSERT OR UPDATE ON "gorfs"."inode_segments" FOR = EACH ROW EXECUTE PROCEDURE "gorfs"."tf_inode_segments_calculate_columns"()<= /span>
 =3B  =3B "ct_= valid_data_layouts_only" AFTER INSERT ON "gorfs"."inode_segments" NOT DEFER= RABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "gorfs"."tf_inode_= se
gments_valid_data_l= ayouts_only"()


Also=2C pasting your EXPLAIN plan into =3Bhttp://explain.depesz.com/ =3Band submitting the link in your post is us= ually easier to read than pasting it into an email.

Ok. I'll do  =3Bit.


create index concurrently nodes_last_modified_rel_path_<= /span>obj_type_pidx
on gorfs.node= s (last_modified desc)
where = relative_path =3D 'main'
and = object_type =3D 'S_IFREG'=3B

gorfs.nodes is a view:=

CREATE OR REPLACE VIEW gorfs.nodes AS =3B
 =3BSELECT "p"."full_path" AS "node_full_path"=2C "h"."st_ino" = AS "parent_inode_id"=2C =3B
 =3B  =3B "t".= "st_ino" AS "inode_id"=2C =3B
 =3B  =3B &n= bsp=3B  =3B CASE
 =3B  =3B  =3B  = =3B  =3B  =3B WHEN "p"."st_ino_target"::bigint =3D 2 THEN NULL::cha= racter varying
 =3B  =3B  =3B  =3B &nb= sp=3B  =3B ELSE "p"."segment_index"::character varying
=
 =3B  =3B  =3B  =3B END AS "relative_path"=2C =3B<= /div>
 =3B  =3B "t"."st_mode"::bigint AS "raw_mode"= =2C "f"."constant_name" AS "object_type"=2C =3B
&n= bsp=3B  =3B ("gorfs"."mode_t_bits"('S_ISUID'::"gorfs"."mode_t_constant_= name")::"bit" &=3B "t"."st_mode"::"bit")::integer <=3B>=3B 0 AS "set= uid"=2C =3B
 =3B  =3B ("gorfs"."mode_t_bit= s"('S_ISGID'::"gorfs"."mode_t_constant_name")::"bit" &=3B "t"."st_mode":= :"bit")::integer <=3B>=3B 0 AS "setgid"=2C =3B
 =3B  =3B ("gorfs"."mode_t_bits"('S_ISVTX'::"gorfs"."mode_t_consta= nt_name")::"bit" &=3B "t"."st_mode"::"bit")::integer <=3B>=3B 0 AS "= sticky"=2C =3B
 =3B  =3B "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") &= amp=3B "t"."st_mode"::"bit")=2C 9)::bit(9) AS "permissions"=2C =3B
 =3B  =3B "t"."st_nlink" AS "links_count"=2C "t"."= st_uid" AS "owner_uid"=2C =3B
 =3B  =3B "t= "."st_gid" AS "owner_gid"=2C "t"."st_size" AS "data_length"=2C =3B
 =3B  =3B "t"."st_atime" AS "last_accessed"=2C "t"= ."st_mtime" AS "last_modified"=2C =3B
 =3B &nb= sp=3B "t"."st_ctime" AS "last_changed"=2C "t"."checksum_md5"=2C =3B
 =3B  =3B ("mst"."media_type" || '/'::"text") || = "mst"."subtype_string"::"text" AS "media_type"=2C =3B
<= div> =3B  =3B  =3B  =3B CASE
 =3B =  =3B  =3B  =3B  =3B  =3B WHEN "f"."constant_name" =3D '= S_IFLNK'::"gorfs"."mode_t_constant_name" THEN ( SELECT "convert_from"("ls".= "segment_data"=2C 'UTF8'::"name") AS "convert_from"
&n= bsp=3B  =3B  =3B  =3B  =3B  =3B  =3B  =3BFROM "= gorfs"."inode_segments" "ls"
 =3B  =3B  = =3B  =3B  =3B  =3B  =3B WHERE "ls"."st_ino"::bigint =3D "p"= ."st_ino_target"::bigint)
 =3B  =3B  =3B &= nbsp=3B  =3B  =3B ELSE NULL::"text"
 =3B &= nbsp=3B  =3B  =3B END AS "target"=2C =3B
&= nbsp=3B  =3B  =3B  =3B CASE
 =3B  = =3B  =3B  =3B  =3B  =3B WHEN "f"."constant_name" =3D 'S_IFR= EG'::"gorfs"."mode_t_constant_name" THEN ( SELECT "string_agg"("fs"."segmen= t_data"=2C ''::"bytea" ORDER BY "fs"."segment_index") AS "string_agg"
=
 =3B  =3B  =3B  =3B  =3B  =3B  = =3B  =3BFROM "gorfs"."inode_segments" "fs"
 = =3B  =3B  =3B  =3B  =3B  =3B  =3B WHERE "fs"."st_in= o"::bigint =3D "p"."st_ino_target"::bigint)
 =3B &= nbsp=3B  =3B  =3B  =3B  =3B ELSE NULL::"bytea"
<= div>
 =3B  =3B  =3B  =3B END AS "file_data"=2C =3B<= /div>
 =3B  =3B "t"."external_size" IS NOT NULL AS "= is_external"=2C =3B
 =3B  =3B "t"."externa= l_size" AS "data_length_target"
 =3B  =3BFROM = "gorfs"."inode_segments" "p"
 =3B  =3BJOIN "go= rfs"."dir_inodes" "h" ON "h"."st_ino"::bigint =3D "p"."st_ino"::bigint
 =3B  =3BJOIN "gorfs"."inodes" "t" ON "t"."st_ino"= ::bigint =3D "p"."st_ino_target"::bigint
 =3B &nbs= p=3BJOIN "gorfs"."mode_t_flags"() "f"("constant_name"=2C "description"=2C "= bits"=2C "bits_octal"=2C "bits_hex"=2C "bits_decimal") ON "f"."bits"::"bit"= =3D ("gorfs"."mode_t_bits"('S_IFMT'::"gorfs"."mode_t_constant_name")::"bit= " &=3B "t"."st_mode"::"bit")
 =3B  =3BLEFT = JOIN "media_subtypes" "mst" ON "mst"."media_subtype_id" =3D "t"."media_subt= ype_id"=3B
= = --_00c77afc-fea6-48b4-9da1-6333348d4bcf_--