Alguém poderia dar um help aí com isto por favor? Obrigado.
Sent from my phone > On 13/01/2016, at 3:55 PM, Lucas <[email protected]> wrote: > > 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
_______________________________________________ pgbr-geral mailing list [email protected] https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
