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 <http://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
<http://iseg.st>_ino_target = file.inode_id
LIMIT
100;
*EXPLAIN ANALYZE DA QUERY:*
Link: http://explain.depesz.com/s/lqi4
*NOVA TABELA:*
|CREATETABLEgorfs.nfs_data (owner_id integer NOTNULL,file_id integer
NOTNULL,migration_path "text",migration_date timestamp withtime
zone,CONSTRAINTnfs_data_pkey PRIMARYKEY("file_id"))
|*INDEX:*
|CREATEINDEXix_nfs_data_owner_id ONgorfs.nfs_data USINGbtree
("owner_id")WHERE"migration_date"ISNULL;
|*COLUMNS:*
|ALTERTABLEgorfs.nfs_data ADDCOLUMNowner_id
integer;ALTERTABLEgorfs.nfs_data ALTERCOLUMNowner_id
SETNOTNULL;ALTERTABLEgorfs.nfs_data ADDCOLUMNfile_id
integer;ALTERTABLEgorfs.nfs_data ALTERCOLUMNfile_id
SETNOTNULL;ALTERTABLEgorfs.nfs_data ADDCOLUMNmigration_path
"text";ALTERTABLEgorfs.nfs_data ADDCOLUMNmigration_date timestamp
withtime zone;ALTERTABLEgorfs.nfs_data ADDCONSTRAINTnfs_data_pkey
PRIMARYKEY("file_id");
*VIEW:*
|
|CREATEORREPLACE VIEWgorfs.nodes
ASSELECT"p"."full_path"AS"node_full_path","h"."st_ino"AS"parent_inode_id","t"."st_ino"AS"inode_id",CASEWHEN"p"."st_ino_target"::bigint
=2THENNULL::character varyingELSE"p"."segment_index"::character
varyingENDAS"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
<>0AS"setuid",("gorfs"."mode_t_bits"('S_ISGID'::"gorfs"."mode_t_constant_name")::"bit"&"t"."st_mode"::"bit")::integer
<>0AS"setgid",("gorfs"."mode_t_bits"('S_ISVTX'::"gorfs"."mode_t_constant_name")::"bit"&"t"."st_mode"::"bit")::integer
<>0AS"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",CASEWHEN"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)ELSENULL::"text"ENDAS"target",CASEWHEN"f"."constant_name"='S_IFREG'::"gorfs"."mode_t_constant_name"THEN(SELECT"string_agg"("fs"."segment_data",''::"bytea"ORDERBY"fs"."segment_index")AS"string_agg"FROM"gorfs"."inode_segments""fs"WHERE"fs"."st_ino"::bigint
="p"."st_ino_target"::bigint)ELSENULL::"bytea"ENDAS"file_data","t"."external_size"ISNOTNULLAS"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")LEFTJOIN"media_subtypes""mst"ON"mst"."media_subtype_id"="t"."media_subtype_id";|
||
_______________________________________________
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
<mailto:pgbr-geral@listas.postgresql.org.br>
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
