Olá pessoal. Estava com problema em uma SLOW QUERY, então foi decidido
mudar tudo. Vou por aqui algumas informacões para que vocês me ajudem a
criar a Query necessária, se possível.
* Desde que a nova Query mostre o mesmo resultado da Antiga...
** A ideia é criar uma nova tabela temporário para guardar a migracão,
na qual deve ser a melhor opcão (Não precisará mais do "join").
Como eu poderia construir esta Query?
Obrigado!
QUERY ANTIGA:
SELECT
file.inode_id AS file_id,
file.parent_inode_id AS file_group,
file.relative_path AS file_type,
file.file_data AS file_binary,
file.node_full_path AS file_name,
file.last_modified AS date_created
FROM
gorfs.nodes AS file
INNER JOIN
gorfs.inode_segments AS iseg ON iseg.st_ino = file.parent_inode_id
AND file.object_type = 'S_IFREG'
AND iseg.nfs_migration_date IS NULL
AND (file.last_modified <
(transaction_timestamp() AT TIME
ZONE 'UTC' - '1 months' :: INTERVAL))
AND iseg.st_ino_target =
file.inode_id
LIMIT
100;
EXPLAIN ANALYZE DA QUERY:
Link: http://explain.depesz.com/s/lqi4
NOVA TABELA:
CREATE TABLE gorfs.nfs_data
(
owner_id integer NOT NULL,
file_id integer NOT NULL,
migration_path "text",
migration_date timestamp with time zone,
CONSTRAINT nfs_data_pkey PRIMARY KEY ("file_id")
)
INDEX:
CREATE INDEX ix_nfs_data_owner_id
ON gorfs.nfs_data
USING btree
("owner_id")
WHERE "migration_date" IS NULL;
COLUMNS:
ALTER TABLE gorfs.nfs_data ADD COLUMN owner_id integer;
ALTER TABLE gorfs.nfs_data ALTER COLUMN owner_id SET NOT NULL;
ALTER TABLE gorfs.nfs_data ADD COLUMN file_id integer;
ALTER TABLE gorfs.nfs_data ALTER COLUMN file_id SET NOT NULL;
ALTER TABLE gorfs.nfs_data ADD COLUMN migration_path "text";
ALTER TABLE gorfs.nfs_data ADD COLUMN migration_date timestamp with time
zone;
ALTER TABLE gorfs.nfs_data
ADD CONSTRAINT nfs_data_pkey PRIMARY KEY("file_id");
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";
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
