Fala pessoal.. tudo bem? Preciso de ajuda para criar uma Query que está demorando MUITO....
Seguem as infos: O que eu poderia fazer para melhorar? * Eu enviei um outro e-mail mas caiu na pasta SPAM. Favor desconsiderar. Obrigado! *QUERY:* 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.nfs_data AS iseg ON file.object_type = 'S_IFREG' > AND iseg.migration_date IS NULL > AND (file.last_modified < > (transaction_timestamp() AT TIME > ZONE 'UTC' - '1 months' :: INTERVAL)) > LIMIT > 100; *EXPLAIN ANALYZE:* Link: http://explain.depesz.com/s/JNol *gorfs.nodes (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"; *TABLE nfs_data:* 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;
_______________________________________________ pgbr-geral mailing list [email protected] https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
