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;
Você não mandou todo o DDL pra entendermos todas as tabelas e relacionamentos.
Todavia, me parece que faltam um (vários) índices, me parece (veja bem, *parece*) que criar um na tabela gorfs.inodes coluna st_mtime é o mais importante, é onde está o maior custo do seu explain antes dos laços e agregações.
[]s Flavio Gurgel _______________________________________________ pgbr-geral mailing list [email protected] https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
