Thanks Vitaly for all your help. I'll have a very deep look on the links you
have provided. In the meantime, I'll also post here what I need.. IF you could
help one more time, would be very very nice.
Thank you again.
This can either be nfs_file_path or nfs_migration_date (both new columns).
Adding an index on either of these columns and using 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=100 loops=1)"
" -> Nested Loop Left Join (cost=1935087.58..4177095.71 rows=1
width=138) (actual time=199195.015..315156.343 rows=100 loops=1)"
" -> Nested Loop (cost=1934568.58..4176379.93 rows=1 width=98)
(actual time=199162.474..314565.271 rows=100 loops=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.043 rows=34633 loops=1)"
" -> Nested Loop (cost=1934049.58..4175847.02 rows=1
width=86) (actual time=196125.213..305961.431 rows=34634 loops=1)"
" -> Hash Join (cost=1934049.58..4175833.65
rows=1 width=94) (actual time=196094.683..238436.508 rows=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 varying ELSE
("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.025 rows=40682288
loops=1)"
" Buckets: 32768 Batches: 128
(originally 1) Memory Usage: 16385kB"
" -> Seq Scan on "inode_segments"
"iseg" (cost=0.00..1929490.56 rows=303935 width=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) (actual time=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.228 rows=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.009 rows=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.035 rows=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.796 rows=23 loops=1)"
" SubPlan 5"
" -> Aggregate (cost=22.51..22.52 rows=1
width=32) (actual time=0.023..0.024 rows=1 loops=23)"
" -> Function Scan on
"regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=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) (actual time=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.50 rows=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.591 rows=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.019 rows=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.037 rows=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.788 rows=23 loops=1)"
" SubPlan 11"
" -> Aggregate (cost=22.51..22.52 rows=1
width=32) (actual time=0.023..0.024 rows=1 loops=23)"
" -> Function Scan on
"regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=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.55 rows=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.009 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.50 rows=1 width=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")::character varying
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=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) (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 - COLUMNALTER TABLE gorfs.inode_segments ADD COLUMN nfs_file_path
"text";
nfs_migration_date - COLUMN - HAVE TO CREATE AN INDEX TO ITALTER 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'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....
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"::bigint OR "st_ino"::bigint = 2)
)
WITH (
OIDS=FALSE
);
