Re: Slow Query - PostgreSQL 9.2

Hello

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Saulo Merlo
> Sent: Montag, 11. Januar 2016 08:12
> To: Vitaly Burovoy <vitaly.burovoy@gmail.com>
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
>
> gorgs.inode_segments:
>
>
>     -- Table: gorfs.inode_segments
>
>     -- DROP TABLE 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....
>       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)
>     )
>     WITH (
>       OIDS=FALSE
>     );

There is no field st_ctime.

>
>     S_IFSOCK:   0:   no data to store, no records here
>     S_IFLNK:    1:   contains the link target (see columns comments for details).
>     S_IFREG:    0+:  actual data segments, up to 64MB each  (see columns comments for details)
>     S_IFBLK:    0:   no data to store, no records here
>     S_IFDIR:    0+:  one record per object name in the directory
>     S_IFCHR:    0:   no data to store, no records here
>     S_IFIFO:    0:   no data to store, no records here
>     ';
>     -- Index: gorfs.ix_inode_segments_climb_tree
>
>     -- DROP INDEX gorfs.ix_inode_segments_climb_tree;
>
>     CREATE INDEX ix_inode_segments_climb_tree
>       ON gorfs.inode_segments
>       USING btree
>       ("segment_index" COLLATE pg_catalog."default", "st_ino_target");
>
>     -- Index: gorfs.ix_inode_segments_filter_by_subtree
>
>     -- DROP INDEX gorfs.ix_inode_segments_filter_by_subtree;
>
>     CREATE INDEX ix_inode_segments_filter_by_subtree
>       ON gorfs.inode_segments
>       USING btree
>       ("full_path" COLLATE pg_catalog."default" varchar_pattern_ops)
>       WHERE "full_path" IS NOT NULL;
>     COMMENT ON INDEX gorfs.ix_inode_segments_filter_by_subtree
>       IS 'Allows looking for left-anchored paths (either regex or LIKE).
>     WARNING: as of 9.2 the index is not used when the comparison term is a non deterministic function (STABLE or
> VOLATILE).
>     See http://www.postgresql.org/message-id/5451D6C4.7040308@vuole.me
>     ';
>
>     -- Index: gorfs.ix_inode_segments_full_path_resolution
>
>     -- DROP INDEX gorfs.ix_inode_segments_full_path_resolution;
>
>     CREATE INDEX ix_inode_segments_full_path_resolution
>       ON gorfs.inode_segments
>       USING btree
>       ("st_ino", "full_path" COLLATE pg_catalog."default");
>
>     -- Index: gorfs.ix_inode_segments_gsdi_pk
>
>     -- DROP INDEX gorfs.ix_inode_segments_gsdi_pk;
>
>     CREATE INDEX ix_inode_segments_gsdi_pk
>       ON gorfs.inode_segments
>       USING btree
>       (("st_ino"::"text") COLLATE pg_catalog."default", ("segment_index"::"text") COLLATE pg_catalog."default");
>
>     -- Index: gorfs.ix_inode_segments_ja_files_lookup
>
>     -- DROP INDEX gorfs.ix_inode_segments_ja_files_lookup;
>
>     CREATE INDEX ix_inode_segments_ja_files_lookup
>       ON gorfs.inode_segments
>       USING btree
>       ((
>     CASE
>         WHEN "full_path"::"text" ~ '/[^/]*\.[^/]*$'::"text" THEN "upper"("regexp_replace"("full_path"::"text",
> '.*\.'::"text", ''::"text", 'g'::"text"))
>         ELSE NULL::"text"
>     END) COLLATE pg_catalog."default")
>       WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text");
>
>     -- Index: gorfs.ix_inode_segments_notes_clientids
>
>     -- DROP INDEX gorfs.ix_inode_segments_notes_clientids;
>
>     CREATE INDEX ix_inode_segments_notes_clientids
>       ON gorfs.inode_segments
>       USING btree
>       (("split_part"("full_path"::"text", '/'::"text", 4)::integer))
>       WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text");
>
>     -- Index: gorfs.ix_inode_segments_notes_fileids
>
>     -- DROP INDEX gorfs.ix_inode_segments_notes_fileids;
>
>     CREATE INDEX ix_inode_segments_notes_fileids
>       ON gorfs.inode_segments
>       USING btree
>       (("split_part"("full_path"::"text", '/'::"text", 8)::integer))
>       WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text");
>
>     -- Index: gorfs.ix_inode_segments_notes_noteids
>
>     -- DROP INDEX gorfs.ix_inode_segments_notes_noteids;
>
>     CREATE INDEX ix_inode_segments_notes_noteids
>       ON gorfs.inode_segments
>       USING btree
>       ((NULLIF("split_part"("full_path"::"text", '/'::"text", 6), 'unassigned'::"text")::integer))
>       WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text");
>
>     -- Index: gorfs.ix_inode_segments_segment_indexes
>
>     -- DROP INDEX gorfs.ix_inode_segments_segment_indexes;
>
>     CREATE INDEX ix_inode_segments_segment_indexes
>       ON gorfs.inode_segments
>       USING btree
>       ("segment_index" COLLATE pg_catalog."default");
>
>     -- Index: gorfs.ix_inode_segments_st_ino_targets
>
>     -- DROP INDEX gorfs.ix_inode_segments_st_ino_targets;
>
>     CREATE INDEX ix_inode_segments_st_ino_targets
>       ON gorfs.inode_segments
>       USING btree
>       ("st_ino_target");
>
>     -- Index: gorfs.ix_inode_segments_st_inos
>
>     -- DROP INDEX gorfs.ix_inode_segments_st_inos;
>
>     CREATE INDEX ix_inode_segments_st_inos
>       ON gorfs.inode_segments
>       USING btree
>       ("st_ino");
>
>
>     -- Trigger: a_iud_update_inode on gorfs.inode_segments
>
>     -- DROP TRIGGER a_iud_update_inode ON gorfs.inode_segments;
>
>     CREATE TRIGGER a_iud_update_inode
>       AFTER INSERT OR UPDATE OR DELETE
>       ON gorfs.inode_segments
>       FOR EACH ROW
>       EXECUTE PROCEDURE gorfs.tf_inode_segments_update_inodes();
>     COMMENT ON TRIGGER a_iud_update_inode ON gorfs.inode_segments IS 'See invoked function';
>
>     -- Trigger: a_u_update_children on gorfs.inode_segments
>
>     -- DROP TRIGGER a_u_update_children ON gorfs.inode_segments;
>
>     CREATE TRIGGER a_u_update_children
>       AFTER UPDATE
>       ON gorfs.inode_segments
>       FOR EACH ROW
>       EXECUTE PROCEDURE gorfs.tf_inode_segments_update_children();
>
>     -- Trigger: b_iu_calculate_columns on gorfs.inode_segments
>
>     -- DROP TRIGGER b_iu_calculate_columns ON gorfs.inode_segments;
>
>     CREATE TRIGGER b_iu_calculate_columns
>       BEFORE INSERT OR UPDATE
>       ON gorfs.inode_segments
>       FOR EACH ROW
>       EXECUTE PROCEDURE gorfs.tf_inode_segments_calculate_columns();
>     COMMENT ON TRIGGER b_iu_calculate_columns ON gorfs.inode_segments IS 'See invoked function';
>
>     -- Trigger: ct_valid_data_layouts_only on gorfs.inode_segments
>
>     -- DROP TRIGGER ct_valid_data_layouts_only ON gorfs.inode_segments;
>
>     CREATE CONSTRAINT TRIGGER ct_valid_data_layouts_only
>       AFTER INSERT
>       ON gorfs.inode_segments
>       FOR EACH ROW
>       EXECUTE PROCEDURE gorfs.tf_inode_segments_valid_data_layouts_only();
>     COMMENT ON TRIGGER ct_valid_data_layouts_only ON gorfs.inode_segments IS 'See invoked function';
>
>
>
> gorfs.noes:
>
>
>     -- View: gorfs.nodes
>
>     -- DROP VIEW gorfs.nodes;
>
>     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";
>
>     -- Trigger: i_iud_action_changes on gorfs.nodes
>
>     -- DROP TRIGGER i_iud_action_changes ON gorfs.nodes;
>
>     CREATE TRIGGER i_iud_action_changes
>       INSTEAD OF INSERT OR UPDATE OR DELETE
>       ON gorfs.nodes
>       FOR EACH ROW
>       EXECUTE PROCEDURE gorfs.tf_nodes_action_changes();

The value of st_ctime comes from table gorfs.inodes. So build the index on that.

>
>
>
>
> > Date: Sun, 10 Jan 2016 23:04:20 -0800
> > Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
> > From: vitaly.burovoy@gmail.com
> > To: smerlo50@outlook.com
> > CC: pgsql-general@postgresql.org
> >
> > On 1/10/16, Saulo Merlo <smerlo50@outlook.com> wrote:
> > > CREATE INDEX CONCURRENTLY index_name ON gorfs.inode_segments(st_ctime);
> > > ERROR: column "st_ctime" does not exist
> > > Look the error I've got
> > >
> > > Lucas
> > >
> > >> Date: Sun, 10 Jan 2016 22:43:21 -0800
> > >> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
> > >> From: vitaly.burovoy@gmail.com
> > >> To: smerlo50@outlook.com
> > >> CC: pgsql-general@postgresql.org
> > >>
> > >> On 1/10/16, Saulo Merlo <smerlo50@outlook.com> wrote:
> > >> > Hi Vitaly,
> > >> >
> > >> > Yep... gorfs.nodes is a view.
> > >> > And the schema is: gorfs.inode_segments
> > >> > So... CREATE INDEX index_name ON gorfs.inode_segments(st_ctime)
> > >> > Is that correct? It would be "st_ctime"?
> > >> If "inodes" is an alias for "gorfs.inode_segments" in your view, yes,
> > >> the above DDL is OK. According to EXPLAIN's "Filter" row the column
> > >> involving in comparison is st_ctime.
> > >>
> > >> Hint: you can create the index without blocking table using "CREATE
> > >> INDEX CONCURRENTLY":
> > >> http://www.postgresql.org/docs/9.2/static/sql-createindex.html
> > >>
> > >> > I've rewriten the query as well. Thank you for that!
> > >> >
> > >> > Thank you
> > >> > Lucas
> > >>
> > >> >> Date: Sun, 10 Jan 2016 21:23:01 -0800
> > >> >> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
> > >> >> From: vitaly.burovoy@gmail.com
> > >> >> To: smerlo50@outlook.com
> > >> >> CC: pgsql-general@postgresql.org
> > >> >>
> > >> >> On 1/10/16, Vitaly Burovoy <vitaly.burovoy@gmail.com> wrote:
> > >> >> > On 1/10/16, Saulo Merlo <smerlo50@outlook.com> wrote:
> > >> >> >> I've got a slow query.. I'd like to make it faster.. Make add an
> > >> >> >> index?
> > >> >> >> Query:
> > >> >> >> SELECT
> > >> >> >> <<overquoting>>
> > >> >> >> FROM gorfs.nodes AS f
> > >> >> >> <<overquoting>>
> > >> >> >> WHERE f.file_data IS NOT NULL
> > >> >> >> AND ((transaction_timestamp() AT TIME ZONE 'UTC') >
> > >> >> >> (f.last_changed
> > >> >> >> +
> > >> >> >> '24
> > >> >> >> months' :: INTERVAL)) LIMIT 100;
> > >> >> >
> > >> >> >> <<overquoting>>
> > >> >> >> "Total runtime: 94989.208 ms"What could I do to make it faster?
> > >> >> >> Thank
> > >> >> >> you.
> > >> >> >
> > >> >> > At least you can add an index:
> > >> >> > CREATE INDEX ON gorfs.nodes(last_changed)
> > >> >> >
> > >> >> > and rewrite part of WHERE clause to:
> > >> >> > (f.last_changed < (transaction_timestamp() AT TIME ZONE 'UTC' - '24
> > >> >> > months'::INTERVAL))
> > >> >> >
> > >> >> > It allows to decrease the slowest part of your query (sequence
> > >> >> > scanning of a table, all 13.5M rows):
> > >> >> >> -> Seq Scan on "inodes" "t" (cost=0.00..1411147.24 rows=13416537
> > >> >> >> width=29) (actual time=94987.224..94987.224 rows=0 loops=1)
> > >> >> >> Filter: ("timezone"('UTC'::"text", "transaction_timestamp"())
> > >> >> >> >
> > >> >> >> (("st_ctime")::timestamp without time zone + '2 years'::interval))
> > >> >> >
> > >> >> > compare that time to the one in the topmost row of EXPLAIN:
> > >> >> >> Limit (cost=1556.99..1336437.30 rows=100 width=186) (actual
> > >> >> >> time=94987.261..94987.261 rows=0 loops=1)
> > >> >>
> > >> >> Hmm. It seems that gorfs.nodes is a view.
> > >> >> So creating index should be something like (I have no idea that schema
> > >> >> name for it):
> > >> >> CREATE INDEX ON _schema_name_.inodes(st_ctime)
> >
> > Please, post a definition of a table and a view (and all intermediate
> > views if any).
> >
> > Via psql it can be done via:
> > \d gorfs.inode_segments
> > \d+ gorfs.nodes
> >
> > --
> > Best regards,
> > Vitaly Burovoy
> >
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general