Olá, Ok.. Index criado e mais dados do DDL:
Você não mandou todo o DDL pra entendermos todas as tabelas e
> relacionamentos.
*Tabela gorfs.inode_segments:*
CREATE TABLE gorfs.inode_segments
> (
> st_ino "gorfs"."ino_t" NOT NULL, -- Inode number the segment belongs to.
> alongside segment_index, it forms the table's primary key to ensure
> uniqueness per relevant scope
> segment_index "gorfs"."pathname_component" NOT NULL, -- See st_no's
> column description for further details. The meaning of this column varies
> based on the host inode type:...
> st_ino_target "gorfs"."ino_t", -- Target inode number. Meaningful for
> directory inode segments (objects in the directory)
> full_path "gorfs"."absolute_pathname", -- Exploded absolute canonical
> path for quick lookups. Meaningful only for directory inode segments
> (objects in the directory)
> segment_data "bytea", -- Actual data segment. Meaningful only for
> S_IFLNK and S_IFREG....
> nfs_migration_date timestamp with time zone,
> nfs_file_path "text",
> CONSTRAINT pk_inode_segments PRIMARY KEY ("st_ino", "segment_index"),
> CONSTRAINT fk_host_inode_must_exist FOREIGN KEY (st_ino)
> REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT fk_target_inode_must_exist FOREIGN KEY (st_ino_target)
> REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT uc_no_duplicate_full_paths UNIQUE ("full_path"),
> CONSTRAINT cc_only_root_can_be_its_own_parent CHECK ("st_ino_target" IS
> NULL OR "st_ino"::bigint <> "st_ino_target"::bigint OR "st_ino"::bigint = 2)
> )
*Tabela gorfs.inodes:*
CREATE TABLE gorfs.inodes
(
st_dev "gorfs"."dev_t" DEFAULT NULL::bigint, -- ID of device containing
> file. Meaningless in this implementation....
st_ino "gorfs"."ino_t" NOT NULL DEFAULT
> "nextval"('"gorfs"."inodes_st_ino_idseq"'::"regclass"), -- Inode number....
st_mode "gorfs"."mode_t" NOT NULL, -- File type/mode bits....
st_nlink "gorfs"."nlink_t" NOT NULL, -- Number of hard links (directory
> segments) pointing to this inode. See stat(2) manual page for details (man
> 2 stat)
st_uid "gorfs"."uid_t" NOT NULL, -- User ID that owns the file. See
> stat(2) manual page for details (man 2 stat)
st_gid "gorfs"."gid_t" NOT NULL, -- Group ID that owns the file.See
> stat(2) manual page for details (man 2 stat)
st_rdev "gorfs"."dev_t", -- Device number (currently we don't support
> device files). See stat(2) manual page for details (man 2 stat)
st_size "gorfs"."off_t", -- File size, if applicable. See stat(2) manual
> page for details (man 2 stat)
st_blksize "gorfs"."blksize_t", -- Block size for I/O. Meaningless here,
> hard coded to 512. See stat(2) manual page for details (man 2 stat)
st_blocks "gorfs"."blkcnt_t", -- Number of allocated blocks. Meaningless
> here, but calculated from block size. See stat(2) manual page for details
> (man 2 stat)
st_atime "gorfs"."time_t" NOT NULL, -- Timestamp of last access. Stored
> as a timestamp as opposed to unix TS. See stat(2) manual page for details
> (man 2 stat)
st_mtime "gorfs"."time_t" NOT NULL, -- Timestamp of last modification.
> Stored as a timestamp as opposed to unix TS. See stat(2) manual page for
> details (man 2 stat)
st_ctime "gorfs"."time_t" NOT NULL, -- Timestamp of last change. Stored
> as a timestamp as opposed to unix TS. See stat(2) manual page for details
> (man 2 stat)
checksum_md5 "md5_hash", -- MD5 checksum of the file. Supplied by the
> application as the DB might not even see the payload
media_subtype_id integer, -- Reference to MIME type (see FK constraint).
> We can't support all media types but unknow types can be stored as
> application/octet-stream
external_size "gorfs"."off_t", -- For symlinks only. Meaningful for fat
> links only: total size of the fat link target. Null for normal symlinks
CONSTRAINT pk_inodes PRIMARY KEY ("st_ino"),
CONSTRAINT fk_media_subtype_must_exist FOREIGN KEY (media_subtype_id)
REFERENCES public.media_subtypes (media_subtype_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT cc_mount_devices_not_supported CHECK ("st_dev" IS NULL)
*INDEXES gorfs.inodes:*
>
>> CREATE INDEX ix_inodes_file_type_bits
>
> ON gorfs.inodes
>
> USING btree
>
> (("gorfs"."mode_t_bits"('S_IFMT'::"gorfs"."mode_t_constant_name")::"bit"
>> & "st_mode"::"bit"));
>
> CREATE INDEX ix_inodes_gsdi_pk
>
> ON gorfs.inodes
>
> USING btree
>
> (("st_ino"::"text") COLLATE pg_catalog."default");
>
> CREATE INDEX ix_inodes_media_subtype_ids
>
> ON gorfs.inodes
>
> USING btree
>
> ("media_subtype_id");
>
>
>
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.
Index criado:
CREATE INDEX CONCURRENTLY ix_inode_st_mtime ON gorfs.inodes USING btree
("st_mtime");
Segue novo EXPLAIN ANALYZE:
Link: http://explain.depesz.com/s/wvsv
Lucas Possamai
kinghost.co.nz
<http://forum.kinghost.co.nz/memberlist.php?mode=viewprofile&u=2&sid=e999f8370385657a65d41d5ff60b0b38>
On 14 January 2016 at 22:07, Flavio Henrique Araque Gurgel <[email protected]
> wrote:
> 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
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
