Slow Query - PostgreSQL 9.2

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 j.clientid AS client_id,
ni.segment_index AS note_id,
f.inode_id AS file_id,
f.node_full_path AS filename,
f.last_changed AS date_created,
f.file_data AS main_binary,
medium.inode_id AS medium_id,
medium.file_data AS medium_binary,
thumbnail.inode_id AS thumbnail_id,
thumbnail.file_data AS thumbnail_binary
FROM gorfs.nodes AS f
INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.inode_id
INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
AND mv.segment_index = 'main.with_name'
INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino
INNER JOIN gorfs.inode_segments AS fn ON fn.st_ino_target = fi.st_ino
INNER JOIN gorfs.inode_segments AS ni ON ni.st_ino_target = fn.st_ino
INNER JOIN public.ja_notes AS n ON n.id = CAST(ni.segment_index AS INTEGER)
INNER JOIN public.ja_jobs AS j ON j.id = n.jobid
LEFT JOIN
(SELECT f.inode_id,
f.file_data,
fi.st_ino
FROM gorfs.nodes AS f
INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.inode_id
INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
AND mv.segment_index = 'medium.with_name'
INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino) AS
medium ON medium.st_ino = fn.st_ino_target
LEFT JOIN
(SELECT f.inode_id,
f.file_data,
fi.st_ino
FROM gorfs.nodes AS f
INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.inode_id
INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
AND mv.segment_index = 'thumbnail.with_name'
INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino) AS
thumbnail ON thumbnail.st_ino = fn.st_ino_target
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)

--
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

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)

--
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

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"?
I've rewriten the query as well. Thank you for that!
Thank youLucas

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)

--
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

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

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

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);
S_IFSOCK: 0: no data to store, no records hereS_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 hereS_IFDIR: 0+: one record per object name in the directoryS_IFCHR: 0: no data to store, no records hereS_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 /messages/by-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();

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 /messages/by-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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 1/10/16, Saulo Merlo <smerlo50@outlook.com> wrote:

gorgs.inode_segments:
<<overquoting>>

gorfs.nodes:
-- View: gorfs.nodes
-- DROP VIEW gorfs.nodes;
CREATE OR REPLACE VIEW gorfs.nodes AS
SELECT
<<overquoting>>
"t"."st_ctime" AS "last_changed", ...
<<overquoting>>
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"(...) ON ...
LEFT JOIN "media_subtypes" "mst" ON "mst"."media_subtype_id" = "t"."media_subtype_id";
<<overquoting>>

It seems alias for "t" is not "gorfs"."inode_segments" (it is "p"),
but "gorfs"."inodes" (in the second "LEFT JOIN" clause).
So, the correct DDL is:
CREATE INDEX CONCURRENTLY index_name ON gorfs.inodes(st_ctime);

P.S.: you can avoid "index_name" if the exact name is not important
for you. In such case name of the index will be constructed
automatically based on table name and column name(s).

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

Thank you!

"Limit (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.053..0.053 rows=0 loops=1)"" -> Nested Loop Left Join (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.050..0.050 rows=0 loops=1)"" -> Nested Loop Left Join (cost=1038.00..2526.07 rows=1 width=154) (actual time=0.047..0.047 rows=0 loops=1)"" -> Nested Loop (cost=519.01..824.17 rows=1 width=114) (actual time=0.045..0.045 rows=0 loops=1)"" -> Nested Loop (cost=519.01..818.20 rows=1 width=114) (actual time=0.043..0.043 rows=0 loops=1)"" -> Nested Loop (cost=519.00..808.69 rows=1 width=106) (actual time=0.041..0.041 rows=0 loops=1)"" -> Nested Loop (cost=519.00..793.43 rows=1 width=106) (actual time=0.040..0.040 rows=0 loops=1)"" -> Nested Loop (cost=519.00..778.17 rows=1 width=98) (actual time=0.038..0.038 rows=0 loops=1)"" -> Nested Loop (cost=519.00..762.91 rows=1 width=98) (actual time=0.036..0.036 rows=0 loops=1)"" -> Nested Loop (cost=519.00..747.64 rows=1 width=98) (actual time=0.034..0.034 rows=0 loops=1)"" Join Filter: (("t"."st_ino")::bigint = ("fd"."st_ino_target")::bigint)"" -> Nested Loop (cost=519.00..732.38 rows=1 width=90) (actual time=0.031..0.031 rows=0 loops=1)"" -> Nested Loop (cost=519.00..718.94 rows=1 width=98) (actual time=0.029..0.029 rows=0 loops=1)"" Join Filter: (CASE WHEN ("sb"."constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name") THEN (SubPlan 4) ELSE NULL::"bytea" END IS NOT NULL)"" -> Nested Loop (cost=519.00..531.96 rows=1 width=20) (actual time=0.027..0.027 rows=0 loops=1)"" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"" -> Index Scan using "ix_inode_segments_nfs" on "inodes" "t" (cost=0.01..11.76 rows=1 width=29) (actual time=0.026..0.026 rows=0 loops=1)"" Index Cond: (("st_ctime")::timestamp without time zone < ("timezone"('UTC'::"text", "transaction_timestamp"()) - '2 years'::interval))"" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"" CTE stat_h"" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"" CTE stat_h_with_bits"" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"" SubPlan 6"" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.30 rows=1 width=78) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("t"."st_ino")::bigint)"" SubPlan 4"" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fd" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("p"."st_ino_target")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "mv" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino")::bigint)"" Filter: (("segment_index")::"text" = 'main.with_name'::"text")"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fi" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("mv"."st_ino")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fn" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fi"."st_ino")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "ni" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fn"."st_ino")::bigint)"" -> Index Scan using "ja_notes_pkey" on "ja_notes" "n" (cost=0.00..9.50 rows=1 width=16) (never executed)"" Index Cond: ("id" = ("ni"."segment_index")::integer)"" -> Index Only Scan using "ix_jobs_top_by_client" on "ja_jobs" "j" (cost=0.00..5.96 rows=1 width=16) (never executed)"" Index Cond: ("id" = "n"."jobid")"" Heap Fetches: 0"" -> Nested Loop (cost=519.00..1701.89 rows=1 width=48) (never executed)"" Join Filter: (("sb"."bits")::"bit" = (B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit"))"" -> Nested Loop (cost=519.00..1688.45 rows=1 width=88) (never executed)"" -> Nested Loop (cost=0.00..1168.36 rows=1 width=24) (never executed)"" -> Nested Loop (cost=0.00..1154.93 rows=1 width=32) (never executed)"" -> Nested Loop (cost=0.00..1139.67 rows=1 width=16) (never executed)"" -> Nested Loop (cost=0.00..968.55 rows=1 width=16) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..170.61 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94 rows=1 width=16) (never executed)"" Index Cond: ((("st_ino")::bigint = ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" = 'medium.with_name'::"text"))"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("mv"."st_ino_target")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"" CTE stat_h"" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"" CTE stat_h_with_bits"" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"" SubPlan 9"" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"" SubPlan 2"" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.42 rows=1 width=21) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> Nested Loop (cost=519.00..1702.00 rows=1 width=48) (never executed)"" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"" -> Nested Loop (cost=0.00..1181.79 rows=1 width=33) (never executed)"" -> Nested Loop (cost=0.00..1168.36 rows=1 width=24) (never executed)"" -> Nested Loop (cost=0.00..1154.93 rows=1 width=32) (never executed)"" -> Nested Loop (cost=0.00..1139.67 rows=1 width=16) (never executed)"" -> Nested Loop (cost=0.00..968.55 rows=1 width=16) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..170.61 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94 rows=1 width=16) (never executed)"" Index Cond: ((("st_ino")::bigint = ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" = 'thumbnail.with_name'::"text"))"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("mv"."st_ino_target")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.42 rows=1 width=21) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"" CTE stat_h"" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"" CTE stat_h_with_bits"" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"" SubPlan 12"" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"" SubPlan 3"" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" SubPlan 1"" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)""Total runtime: 1.740 ms"

Still getting a sloooow one..Any thoughts?
My hypothesis is; the 1 clause that will always be used is in the WHERE statement below. This can either be nfs_file_path or nfs_migration_date (both new columns). Adding an index on either of these columns and using them in the clause should improve things greatly.
How could I do that?Lucas
"Limit (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.051..0.051 rows=0 loops=1)"
" -> Nested Loop Left Join (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.048..0.048 rows=0 loops=1)"
" -> Nested Loop Left Join (cost=1038.00..2526.07 rows=1 width=154) (actual time=0.046..0.046 rows=0 loops=1)"
" -> Nested Loop (cost=519.01..824.17 rows=1 width=114) (actual time=0.044..0.044 rows=0 loops=1)"
" -> Nested Loop (cost=519.01..818.20 rows=1 width=114) (actual time=0.042..0.042 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..808.69 rows=1 width=106) (actual time=0.040..0.040 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..793.43 rows=1 width=106) (actual time=0.038..0.038 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..778.17 rows=1 width=98) (actual time=0.036..0.036 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..762.91 rows=1 width=98) (actual time=0.034..0.034 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..747.64 rows=1 width=98) (actual time=0.032..0.032 rows=0 loops=1)"
" Join Filter: (("t"."st_ino")::bigint = ("fd"."st_ino_target")::bigint)"
" -> Nested Loop (cost=519.00..732.38 rows=1 width=90) (actual time=0.030..0.030 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..718.94 rows=1 width=98) (actual time=0.028..0.028 rows=0 loops=1)"
" Join Filter: (CASE WHEN ("sb"."constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name") THEN (SubPlan 4) ELSE NULL::"bytea" END IS NOT NULL)"
" -> Nested Loop (cost=519.00..531.96 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"
" -> Index Scan using "ix_inode_segments_nfs" on "inodes" "t" (cost=0.01..11.76 rows=1 width=29) (actual time=0.024..0.024 rows=0 loops=1)"
" Index Cond: (("st_ctime")::timestamp without time zone < ("timezone"('UTC'::"text", "transaction_timestamp"()) - '1 year 6 mons'::interval))"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"
" SubPlan 6"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.30 rows=1 width=78) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("t"."st_ino")::bigint)"
" SubPlan 4"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fd" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("p"."st_ino_target")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "mv" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino")::bigint)"
" Filter: (("segment_index")::"text" = 'main.with_name'::"text")"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fi" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("mv"."st_ino")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fn" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fi"."st_ino")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "ni" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fn"."st_ino")::bigint)"
" -> Index Scan using "ja_notes_pkey" on "ja_notes" "n" (cost=0.00..9.50 rows=1 width=16) (never executed)"
" Index Cond: ("id" = ("ni"."segment_index")::integer)"
" -> Index Only Scan using "ix_jobs_top_by_client" on "ja_jobs" "j" (cost=0.00..5.96 rows=1 width=16) (never executed)"
" Index Cond: ("id" = "n"."jobid")"
" Heap Fetches: 0"
" -> Nested Loop (cost=519.00..1701.89 rows=1 width=48) (never executed)"
" Join Filter: (("sb"."bits")::"bit" = (B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit"))"
" -> Nested Loop (cost=519.00..1688.45 rows=1 width=88) (never executed)"
" -> Nested Loop (cost=0.00..1168.36 rows=1 width=24) (never executed)"
" -> Nested Loop (cost=0.00..1154.93 rows=1 width=32) (never executed)"
" -> Nested Loop (cost=0.00..1139.67 rows=1 width=16) (never executed)"
" -> Nested Loop (cost=0.00..968.55 rows=1 width=16) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..170.61 rows=40 width=16) (never executed)"
" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94 rows=1 width=16) (never executed)"
" Index Cond: ((("st_ino")::bigint = ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" = 'medium.with_name'::"text"))"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71 rows=40 width=16) (never executed)"
" Index Cond: (("st_ino")::bigint = ("mv"."st_ino_target")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"
" SubPlan 9"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"
" SubPlan 2"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.42 rows=1 width=21) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> Nested Loop (cost=519.00..1702.00 rows=1 width=48) (never executed)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"
" -> Nested Loop (cost=0.00..1181.79 rows=1 width=33) (never executed)"
" -> Nested Loop (cost=0.00..1168.36 rows=1 width=24) (never executed)"
" -> Nested Loop (cost=0.00..1154.93 rows=1 width=32) (never executed)"
" -> Nested Loop (cost=0.00..1139.67 rows=1 width=16) (never executed)"
" -> Nested Loop (cost=0.00..968.55 rows=1 width=16) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..170.61 rows=40 width=16) (never executed)"
" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94 rows=1 width=16) (never executed)"
" Index Cond: ((("st_ino")::bigint = ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" = 'thumbnail.with_name'::"text"))"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71 rows=40 width=16) (never executed)"
" Index Cond: (("st_ino")::bigint = ("mv"."st_ino_target")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.42 rows=1 width=21) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"
" SubPlan 12"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"
" SubPlan 3"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" SubPlan 1"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
"Total runtime: 1.395 ms"

From: smerlo50@outlook.com
To: clavadetscher@swisspug.org; vitaly.burovoy@gmail.com
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
Date: Mon, 11 Jan 2016 07:48:04 +0000

Thank you!

"Limit (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.053..0.053 rows=0 loops=1)"" -> Nested Loop Left Join (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.050..0.050 rows=0 loops=1)"" -> Nested Loop Left Join (cost=1038.00..2526.07 rows=1 width=154) (actual time=0.047..0.047 rows=0 loops=1)"" -> Nested Loop (cost=519.01..824.17 rows=1 width=114) (actual time=0.045..0.045 rows=0 loops=1)"" -> Nested Loop (cost=519.01..818.20 rows=1 width=114) (actual time=0.043..0.043 rows=0 loops=1)"" -> Nested Loop (cost=519.00..808.69 rows=1 width=106) (actual time=0.041..0.041 rows=0 loops=1)"" -> Nested Loop (cost=519.00..793.43 rows=1 width=106) (actual time=0.040..0.040 rows=0 loops=1)"" -> Nested Loop (cost=519.00..778.17 rows=1 width=98) (actual time=0.038..0.038 rows=0 loops=1)"" -> Nested Loop (cost=519.00..762.91 rows=1 width=98) (actual time=0.036..0.036 rows=0 loops=1)"" -> Nested Loop (cost=519.00..747.64 rows=1 width=98) (actual time=0.034..0.034 rows=0 loops=1)"" Join Filter: (("t"."st_ino")::bigint = ("fd"."st_ino_target")::bigint)"" -> Nested Loop (cost=519.00..732.38 rows=1 width=90) (actual time=0.031..0.031 rows=0 loops=1)"" -> Nested Loop (cost=519.00..718.94 rows=1 width=98) (actual time=0.029..0.029 rows=0 loops=1)"" Join Filter: (CASE WHEN ("sb"."constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name") THEN (SubPlan 4) ELSE NULL::"bytea" END IS NOT NULL)"" -> Nested Loop (cost=519.00..531.96 rows=1 width=20) (actual time=0.027..0.027 rows=0 loops=1)"" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"" -> Index Scan using "ix_inode_segments_nfs" on "inodes" "t" (cost=0.01..11.76 rows=1 width=29) (actual time=0.026..0.026 rows=0 loops=1)"" Index Cond: (("st_ctime")::timestamp without time zone < ("timezone"('UTC'::"text", "transaction_timestamp"()) - '2 years'::interval))"" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"" CTE stat_h"" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"" CTE stat_h_with_bits"" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"" SubPlan 6"" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.30 rows=1 width=78) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("t"."st_ino")::bigint)"" SubPlan 4"" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fd" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("p"."st_ino_target")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "mv" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino")::bigint)"" Filter: (("segment_index")::"text" = 'main.with_name'::"text")"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fi" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("mv"."st_ino")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fn" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fi"."st_ino")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "ni" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fn"."st_ino")::bigint)"" -> Index Scan using "ja_notes_pkey" on "ja_notes" "n" (cost=0.00..9.50 rows=1 width=16) (never executed)"" Index Cond: ("id" = ("ni"."segment_index")::integer)"" -> Index Only Scan using "ix_jobs_top_by_client" on "ja_jobs" "j" (cost=0.00..5.96 rows=1 width=16) (never executed)"" Index Cond: ("id" = "n"."jobid")"" Heap Fetches: 0"" -> Nested Loop (cost=519.00..1701.89 rows=1 width=48) (never executed)"" Join Filter: (("sb"."bits")::"bit" = (B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit"))"" -> Nested Loop (cost=519.00..1688.45 rows=1 width=88) (never executed)"" -> Nested Loop (cost=0.00..1168.36 rows=1 width=24) (never executed)"" -> Nested Loop (cost=0.00..1154.93 rows=1 width=32) (never executed)"" -> Nested Loop (cost=0.00..1139.67 rows=1 width=16) (never executed)"" -> Nested Loop (cost=0.00..968.55 rows=1 width=16) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..170.61 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94 rows=1 width=16) (never executed)"" Index Cond: ((("st_ino")::bigint = ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" = 'medium.with_name'::"text"))"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("mv"."st_ino_target")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"" CTE stat_h"" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"" CTE stat_h_with_bits"" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"" SubPlan 9"" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"" SubPlan 2"" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.42 rows=1 width=21) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> Nested Loop (cost=519.00..1702.00 rows=1 width=48) (never executed)"" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"" -> Nested Loop (cost=0.00..1181.79 rows=1 width=33) (never executed)"" -> Nested Loop (cost=0.00..1168.36 rows=1 width=24) (never executed)"" -> Nested Loop (cost=0.00..1154.93 rows=1 width=32) (never executed)"" -> Nested Loop (cost=0.00..1139.67 rows=1 width=16) (never executed)"" -> Nested Loop (cost=0.00..968.55 rows=1 width=16) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..170.61 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94 rows=1 width=16) (never executed)"" Index Cond: ((("st_ino")::bigint = ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" = 'thumbnail.with_name'::"text"))"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("mv"."st_ino_target")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.42 rows=1 width=21) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"" CTE stat_h"" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"" CTE stat_h_with_bits"" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"" SubPlan 12"" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"" SubPlan 3"" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" SubPlan 1"" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)""Total runtime: 1.740 ms"

NEW QUERY:

SELECT j.clientid AS client_id,
ni.segment_index AS note_id,
f.st_ino AS file_id,
f.full_path AS filename,
f.segment_data AS main_binary,
fmeta.st_mtime AS date_created,
medium.inode_id AS medium_id,
medium.file_data AS medium_binary,
thumbnail.inode_id AS thumbnail_id,
thumbnail.file_data AS thumbnail_binary
FROM gorfs.inode_segments AS f
INNER JOIN gorfs.inodes AS fmeta ON fmeta.st_ino = f.st_ino
INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.st_ino
INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
AND mv.segment_index = 'main.with_name'
INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino
INNER JOIN gorfs.inode_segments AS fn ON fn.st_ino_target = fi.st_ino
INNER JOIN gorfs.inode_segments AS ni ON ni.st_ino_target = fn.st_ino
INNER JOIN public.ja_notes AS n ON n.id = CAST(ni.segment_index AS INTEGER)
INNER JOIN public.ja_jobs AS j ON j.id = n.jobid
LEFT JOIN
(SELECT f.st_ino AS inode_id,
f.segment_data AS file_data,
fi.st_ino
FROM gorfs.inode_segments AS f
INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.st_ino
INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
AND mv.segment_index = 'medium.with_name'
INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino ) AS medium ON medium.st_ino = fn.st_ino_target
LEFT JOIN
(SELECT f.st_ino AS inode_id,
f.segment_data AS file_data,
fi.st_ino
FROM gorfs.inode_segments AS f
INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.st_ino
INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
AND mv.segment_index = 'thumbnail.with_name'
INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino ) AS thumbnail ON thumbnail.st_ino = fn.st_ino_target
WHERE f.nfs_file_path IS NULL
AND ((transaction_timestamp() AT TIME ZONE \'UTC\') > (f.st_mtime+ \'' . $fileMigrationMonthAge . ' months\' :: INTERVAL)) LIMIT 100;

From: smerlo50@outlook.com
To: clavadetscher@swisspug.org; vitaly.burovoy@gmail.com
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
Date: Mon, 11 Jan 2016 20:02:54 +0000

Still getting a sloooow one..Any thoughts?
My hypothesis is; the 1 clause that will always be used is in the WHERE statement below. This can either be nfs_file_path or nfs_migration_date (both new columns). Adding an index on either of these columns and using them in the clause should improve things greatly.
How could I do that?Lucas
"Limit (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.051..0.051 rows=0 loops=1)"
" -> Nested Loop Left Join (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.048..0.048 rows=0 loops=1)"
" -> Nested Loop Left Join (cost=1038.00..2526.07 rows=1 width=154) (actual time=0.046..0.046 rows=0 loops=1)"
" -> Nested Loop (cost=519.01..824.17 rows=1 width=114) (actual time=0.044..0.044 rows=0 loops=1)"
" -> Nested Loop (cost=519.01..818.20 rows=1 width=114) (actual time=0.042..0.042 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..808.69 rows=1 width=106) (actual time=0.040..0.040 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..793.43 rows=1 width=106) (actual time=0.038..0.038 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..778.17 rows=1 width=98) (actual time=0.036..0.036 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..762.91 rows=1 width=98) (actual time=0.034..0.034 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..747.64 rows=1 width=98) (actual time=0.032..0.032 rows=0 loops=1)"
" Join Filter: (("t"."st_ino")::bigint = ("fd"."st_ino_target")::bigint)"
" -> Nested Loop (cost=519.00..732.38 rows=1 width=90) (actual time=0.030..0.030 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..718.94 rows=1 width=98) (actual time=0.028..0.028 rows=0 loops=1)"
" Join Filter: (CASE WHEN ("sb"."constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name") THEN (SubPlan 4) ELSE NULL::"bytea" END IS NOT NULL)"
" -> Nested Loop (cost=519.00..531.96 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"
" -> Index Scan using "ix_inode_segments_nfs" on "inodes" "t" (cost=0.01..11.76 rows=1 width=29) (actual time=0.024..0.024 rows=0 loops=1)"
" Index Cond: (("st_ctime")::timestamp without time zone < ("timezone"('UTC'::"text", "transaction_timestamp"()) - '1 year 6 mons'::interval))"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"
" SubPlan 6"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.30 rows=1 width=78) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("t"."st_ino")::bigint)"
" SubPlan 4"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fd" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("p"."st_ino_target")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "mv" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino")::bigint)"
" Filter: (("segment_index")::"text" = 'main.with_name'::"text")"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fi" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("mv"."st_ino")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fn" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fi"."st_ino")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "ni" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fn"."st_ino")::bigint)"
" -> Index Scan using "ja_notes_pkey" on "ja_notes" "n" (cost=0.00..9.50 rows=1 width=16) (never executed)"
" Index Cond: ("id" = ("ni"."segment_index")::integer)"
" -> Index Only Scan using "ix_jobs_top_by_client" on "ja_jobs" "j" (cost=0.00..5.96 rows=1 width=16) (never executed)"
" Index Cond: ("id" = "n"."jobid")"
" Heap Fetches: 0"
" -> Nested Loop (cost=519.00..1701.89 rows=1 width=48) (never executed)"
" Join Filter: (("sb"."bits")::"bit" = (B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit"))"
" -> Nested Loop (cost=519.00..1688.45 rows=1 width=88) (never executed)"
" -> Nested Loop (cost=0.00..1168.36 rows=1 width=24) (never executed)"
" -> Nested Loop (cost=0.00..1154.93 rows=1 width=32) (never executed)"
" -> Nested Loop (cost=0.00..1139.67 rows=1 width=16) (never executed)"
" -> Nested Loop (cost=0.00..968.55 rows=1 width=16) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..170.61 rows=40 width=16) (never executed)"
" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94 rows=1 width=16) (never executed)"
" Index Cond: ((("st_ino")::bigint = ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" = 'medium.with_name'::"text"))"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71 rows=40 width=16) (never executed)"
" Index Cond: (("st_ino")::bigint = ("mv"."st_ino_target")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"
" SubPlan 9"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"
" SubPlan 2"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.42 rows=1 width=21) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> Nested Loop (cost=519.00..1702.00 rows=1 width=48) (never executed)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"
" -> Nested Loop (cost=0.00..1181.79 rows=1 width=33) (never executed)"
" -> Nested Loop (cost=0.00..1168.36 rows=1 width=24) (never executed)"
" -> Nested Loop (cost=0.00..1154.93 rows=1 width=32) (never executed)"
" -> Nested Loop (cost=0.00..1139.67 rows=1 width=16) (never executed)"
" -> Nested Loop (cost=0.00..968.55 rows=1 width=16) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..170.61 rows=40 width=16) (never executed)"
" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94 rows=1 width=16) (never executed)"
" Index Cond: ((("st_ino")::bigint = ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" = 'thumbnail.with_name'::"text"))"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71 rows=40 width=16) (never executed)"
" Index Cond: (("st_ino")::bigint = ("mv"."st_ino_target")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.42 rows=1 width=21) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"
" SubPlan 12"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"
" SubPlan 3"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" SubPlan 1"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
"Total runtime: 1.395 ms"

From: smerlo50@outlook.com
To: clavadetscher@swisspug.org; vitaly.burovoy@gmail.com
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
Date: Mon, 11 Jan 2016 07:48:04 +0000

Thank you!

"Limit (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.053..0.053 rows=0 loops=1)"" -> Nested Loop Left Join (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.050..0.050 rows=0 loops=1)"" -> Nested Loop Left Join (cost=1038.00..2526.07 rows=1 width=154) (actual time=0.047..0.047 rows=0 loops=1)"" -> Nested Loop (cost=519.01..824.17 rows=1 width=114) (actual time=0.045..0.045 rows=0 loops=1)"" -> Nested Loop (cost=519.01..818.20 rows=1 width=114) (actual time=0.043..0.043 rows=0 loops=1)"" -> Nested Loop (cost=519.00..808.69 rows=1 width=106) (actual time=0.041..0.041 rows=0 loops=1)"" -> Nested Loop (cost=519.00..793.43 rows=1 width=106) (actual time=0.040..0.040 rows=0 loops=1)"" -> Nested Loop (cost=519.00..778.17 rows=1 width=98) (actual time=0.038..0.038 rows=0 loops=1)"" -> Nested Loop (cost=519.00..762.91 rows=1 width=98) (actual time=0.036..0.036 rows=0 loops=1)"" -> Nested Loop (cost=519.00..747.64 rows=1 width=98) (actual time=0.034..0.034 rows=0 loops=1)"" Join Filter: (("t"."st_ino")::bigint = ("fd"."st_ino_target")::bigint)"" -> Nested Loop (cost=519.00..732.38 rows=1 width=90) (actual time=0.031..0.031 rows=0 loops=1)"" -> Nested Loop (cost=519.00..718.94 rows=1 width=98) (actual time=0.029..0.029 rows=0 loops=1)"" Join Filter: (CASE WHEN ("sb"."constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name") THEN (SubPlan 4) ELSE NULL::"bytea" END IS NOT NULL)"" -> Nested Loop (cost=519.00..531.96 rows=1 width=20) (actual time=0.027..0.027 rows=0 loops=1)"" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"" -> Index Scan using "ix_inode_segments_nfs" on "inodes" "t" (cost=0.01..11.76 rows=1 width=29) (actual time=0.026..0.026 rows=0 loops=1)"" Index Cond: (("st_ctime")::timestamp without time zone < ("timezone"('UTC'::"text", "transaction_timestamp"()) - '2 years'::interval))"" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"" CTE stat_h"" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"" CTE stat_h_with_bits"" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"" SubPlan 6"" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.30 rows=1 width=78) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("t"."st_ino")::bigint)"" SubPlan 4"" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fd" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("p"."st_ino_target")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "mv" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino")::bigint)"" Filter: (("segment_index")::"text" = 'main.with_name'::"text")"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fi" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("mv"."st_ino")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fn" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fi"."st_ino")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "ni" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fn"."st_ino")::bigint)"" -> Index Scan using "ja_notes_pkey" on "ja_notes" "n" (cost=0.00..9.50 rows=1 width=16) (never executed)"" Index Cond: ("id" = ("ni"."segment_index")::integer)"" -> Index Only Scan using "ix_jobs_top_by_client" on "ja_jobs" "j" (cost=0.00..5.96 rows=1 width=16) (never executed)"" Index Cond: ("id" = "n"."jobid")"" Heap Fetches: 0"" -> Nested Loop (cost=519.00..1701.89 rows=1 width=48) (never executed)"" Join Filter: (("sb"."bits")::"bit" = (B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit"))"" -> Nested Loop (cost=519.00..1688.45 rows=1 width=88) (never executed)"" -> Nested Loop (cost=0.00..1168.36 rows=1 width=24) (never executed)"" -> Nested Loop (cost=0.00..1154.93 rows=1 width=32) (never executed)"" -> Nested Loop (cost=0.00..1139.67 rows=1 width=16) (never executed)"" -> Nested Loop (cost=0.00..968.55 rows=1 width=16) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..170.61 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94 rows=1 width=16) (never executed)"" Index Cond: ((("st_ino")::bigint = ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" = 'medium.with_name'::"text"))"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("mv"."st_ino_target")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"" CTE stat_h"" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"" CTE stat_h_with_bits"" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"" SubPlan 9"" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"" SubPlan 2"" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.42 rows=1 width=21) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> Nested Loop (cost=519.00..1702.00 rows=1 width=48) (never executed)"" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"" -> Nested Loop (cost=0.00..1181.79 rows=1 width=33) (never executed)"" -> Nested Loop (cost=0.00..1168.36 rows=1 width=24) (never executed)"" -> Nested Loop (cost=0.00..1154.93 rows=1 width=32) (never executed)"" -> Nested Loop (cost=0.00..1139.67 rows=1 width=16) (never executed)"" -> Nested Loop (cost=0.00..968.55 rows=1 width=16) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..170.61 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94 rows=1 width=16) (never executed)"" Index Cond: ((("st_ino")::bigint = ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" = 'thumbnail.with_name'::"text"))"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("mv"."st_ino_target")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.42 rows=1 width=21) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"" CTE stat_h"" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"" CTE stat_h_with_bits"" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"" SubPlan 12"" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"" SubPlan 3"" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" SubPlan 1"" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)""Total runtime: 1.740 ms"

Hey guys..
How could I create a timestampandtz index?
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)
)
From: smerlo50@outlook.com
To: clavadetscher@swisspug.org; vitaly.burovoy@gmail.com
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
Date: Mon, 11 Jan 2016 20:13:27 +0000

NEW QUERY:

SELECT j.clientid AS client_id,
ni.segment_index AS note_id,
f.st_ino AS file_id,
f.full_path AS filename,
f.segment_data AS main_binary,
fmeta.st_mtime AS date_created,
medium.inode_id AS medium_id,
medium.file_data AS medium_binary,
thumbnail.inode_id AS thumbnail_id,
thumbnail.file_data AS thumbnail_binary
FROM gorfs.inode_segments AS f
INNER JOIN gorfs.inodes AS fmeta ON fmeta.st_ino = f.st_ino
INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.st_ino
INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
AND mv.segment_index = 'main.with_name'
INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino
INNER JOIN gorfs.inode_segments AS fn ON fn.st_ino_target = fi.st_ino
INNER JOIN gorfs.inode_segments AS ni ON ni.st_ino_target = fn.st_ino
INNER JOIN public.ja_notes AS n ON n.id = CAST(ni.segment_index AS INTEGER)
INNER JOIN public.ja_jobs AS j ON j.id = n.jobid
LEFT JOIN
(SELECT f.st_ino AS inode_id,
f.segment_data AS file_data,
fi.st_ino
FROM gorfs.inode_segments AS f
INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.st_ino
INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
AND mv.segment_index = 'medium.with_name'
INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino ) AS medium ON medium.st_ino = fn.st_ino_target
LEFT JOIN
(SELECT f.st_ino AS inode_id,
f.segment_data AS file_data,
fi.st_ino
FROM gorfs.inode_segments AS f
INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.st_ino
INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
AND mv.segment_index = 'thumbnail.with_name'
INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino ) AS thumbnail ON thumbnail.st_ino = fn.st_ino_target
WHERE f.nfs_file_path IS NULL
AND ((transaction_timestamp() AT TIME ZONE \'UTC\') > (f.st_mtime+ \'' . $fileMigrationMonthAge . ' months\' :: INTERVAL)) LIMIT 100;

From: smerlo50@outlook.com
To: clavadetscher@swisspug.org; vitaly.burovoy@gmail.com
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
Date: Mon, 11 Jan 2016 20:02:54 +0000

Still getting a sloooow one..Any thoughts?
My hypothesis is; the 1 clause that will always be used is in the WHERE statement below. This can either be nfs_file_path or nfs_migration_date (both new columns). Adding an index on either of these columns and using them in the clause should improve things greatly.
How could I do that?Lucas
"Limit (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.051..0.051 rows=0 loops=1)"
" -> Nested Loop Left Join (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.048..0.048 rows=0 loops=1)"
" -> Nested Loop Left Join (cost=1038.00..2526.07 rows=1 width=154) (actual time=0.046..0.046 rows=0 loops=1)"
" -> Nested Loop (cost=519.01..824.17 rows=1 width=114) (actual time=0.044..0.044 rows=0 loops=1)"
" -> Nested Loop (cost=519.01..818.20 rows=1 width=114) (actual time=0.042..0.042 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..808.69 rows=1 width=106) (actual time=0.040..0.040 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..793.43 rows=1 width=106) (actual time=0.038..0.038 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..778.17 rows=1 width=98) (actual time=0.036..0.036 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..762.91 rows=1 width=98) (actual time=0.034..0.034 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..747.64 rows=1 width=98) (actual time=0.032..0.032 rows=0 loops=1)"
" Join Filter: (("t"."st_ino")::bigint = ("fd"."st_ino_target")::bigint)"
" -> Nested Loop (cost=519.00..732.38 rows=1 width=90) (actual time=0.030..0.030 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..718.94 rows=1 width=98) (actual time=0.028..0.028 rows=0 loops=1)"
" Join Filter: (CASE WHEN ("sb"."constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name") THEN (SubPlan 4) ELSE NULL::"bytea" END IS NOT NULL)"
" -> Nested Loop (cost=519.00..531.96 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"
" -> Index Scan using "ix_inode_segments_nfs" on "inodes" "t" (cost=0.01..11.76 rows=1 width=29) (actual time=0.024..0.024 rows=0 loops=1)"
" Index Cond: (("st_ctime")::timestamp without time zone < ("timezone"('UTC'::"text", "transaction_timestamp"()) - '1 year 6 mons'::interval))"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"
" SubPlan 6"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.30 rows=1 width=78) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("t"."st_ino")::bigint)"
" SubPlan 4"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fd" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("p"."st_ino_target")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "mv" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino")::bigint)"
" Filter: (("segment_index")::"text" = 'main.with_name'::"text")"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fi" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("mv"."st_ino")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fn" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fi"."st_ino")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "ni" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fn"."st_ino")::bigint)"
" -> Index Scan using "ja_notes_pkey" on "ja_notes" "n" (cost=0.00..9.50 rows=1 width=16) (never executed)"
" Index Cond: ("id" = ("ni"."segment_index")::integer)"
" -> Index Only Scan using "ix_jobs_top_by_client" on "ja_jobs" "j" (cost=0.00..5.96 rows=1 width=16) (never executed)"
" Index Cond: ("id" = "n"."jobid")"
" Heap Fetches: 0"
" -> Nested Loop (cost=519.00..1701.89 rows=1 width=48) (never executed)"
" Join Filter: (("sb"."bits")::"bit" = (B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit"))"
" -> Nested Loop (cost=519.00..1688.45 rows=1 width=88) (never executed)"
" -> Nested Loop (cost=0.00..1168.36 rows=1 width=24) (never executed)"
" -> Nested Loop (cost=0.00..1154.93 rows=1 width=32) (never executed)"
" -> Nested Loop (cost=0.00..1139.67 rows=1 width=16) (never executed)"
" -> Nested Loop (cost=0.00..968.55 rows=1 width=16) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..170.61 rows=40 width=16) (never executed)"
" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94 rows=1 width=16) (never executed)"
" Index Cond: ((("st_ino")::bigint = ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" = 'medium.with_name'::"text"))"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71 rows=40 width=16) (never executed)"
" Index Cond: (("st_ino")::bigint = ("mv"."st_ino_target")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"
" SubPlan 9"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"
" SubPlan 2"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.42 rows=1 width=21) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> Nested Loop (cost=519.00..1702.00 rows=1 width=48) (never executed)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"
" -> Nested Loop (cost=0.00..1181.79 rows=1 width=33) (never executed)"
" -> Nested Loop (cost=0.00..1168.36 rows=1 width=24) (never executed)"
" -> Nested Loop (cost=0.00..1154.93 rows=1 width=32) (never executed)"
" -> Nested Loop (cost=0.00..1139.67 rows=1 width=16) (never executed)"
" -> Nested Loop (cost=0.00..968.55 rows=1 width=16) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..170.61 rows=40 width=16) (never executed)"
" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94 rows=1 width=16) (never executed)"
" Index Cond: ((("st_ino")::bigint = ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" = 'thumbnail.with_name'::"text"))"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71 rows=40 width=16) (never executed)"
" Index Cond: (("st_ino")::bigint = ("mv"."st_ino_target")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.42 rows=1 width=21) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"
" SubPlan 12"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"
" SubPlan 3"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" SubPlan 1"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
"Total runtime: 1.395 ms"

From: smerlo50@outlook.com
To: clavadetscher@swisspug.org; vitaly.burovoy@gmail.com
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
Date: Mon, 11 Jan 2016 07:48:04 +0000

Thank you!

"Limit (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.053..0.053 rows=0 loops=1)"" -> Nested Loop Left Join (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.050..0.050 rows=0 loops=1)"" -> Nested Loop Left Join (cost=1038.00..2526.07 rows=1 width=154) (actual time=0.047..0.047 rows=0 loops=1)"" -> Nested Loop (cost=519.01..824.17 rows=1 width=114) (actual time=0.045..0.045 rows=0 loops=1)"" -> Nested Loop (cost=519.01..818.20 rows=1 width=114) (actual time=0.043..0.043 rows=0 loops=1)"" -> Nested Loop (cost=519.00..808.69 rows=1 width=106) (actual time=0.041..0.041 rows=0 loops=1)"" -> Nested Loop (cost=519.00..793.43 rows=1 width=106) (actual time=0.040..0.040 rows=0 loops=1)"" -> Nested Loop (cost=519.00..778.17 rows=1 width=98) (actual time=0.038..0.038 rows=0 loops=1)"" -> Nested Loop (cost=519.00..762.91 rows=1 width=98) (actual time=0.036..0.036 rows=0 loops=1)"" -> Nested Loop (cost=519.00..747.64 rows=1 width=98) (actual time=0.034..0.034 rows=0 loops=1)"" Join Filter: (("t"."st_ino")::bigint = ("fd"."st_ino_target")::bigint)"" -> Nested Loop (cost=519.00..732.38 rows=1 width=90) (actual time=0.031..0.031 rows=0 loops=1)"" -> Nested Loop (cost=519.00..718.94 rows=1 width=98) (actual time=0.029..0.029 rows=0 loops=1)"" Join Filter: (CASE WHEN ("sb"."constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name") THEN (SubPlan 4) ELSE NULL::"bytea" END IS NOT NULL)"" -> Nested Loop (cost=519.00..531.96 rows=1 width=20) (actual time=0.027..0.027 rows=0 loops=1)"" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"" -> Index Scan using "ix_inode_segments_nfs" on "inodes" "t" (cost=0.01..11.76 rows=1 width=29) (actual time=0.026..0.026 rows=0 loops=1)"" Index Cond: (("st_ctime")::timestamp without time zone < ("timezone"('UTC'::"text", "transaction_timestamp"()) - '2 years'::interval))"" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"" CTE stat_h"" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"" CTE stat_h_with_bits"" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"" SubPlan 6"" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.30 rows=1 width=78) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("t"."st_ino")::bigint)"" SubPlan 4"" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fd" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("p"."st_ino_target")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "mv" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino")::bigint)"" Filter: (("segment_index")::"text" = 'main.with_name'::"text")"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fi" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("mv"."st_ino")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fn" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fi"."st_ino")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "ni" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fn"."st_ino")::bigint)"" -> Index Scan using "ja_notes_pkey" on "ja_notes" "n" (cost=0.00..9.50 rows=1 width=16) (never executed)"" Index Cond: ("id" = ("ni"."segment_index")::integer)"" -> Index Only Scan using "ix_jobs_top_by_client" on "ja_jobs" "j" (cost=0.00..5.96 rows=1 width=16) (never executed)"" Index Cond: ("id" = "n"."jobid")"" Heap Fetches: 0"" -> Nested Loop (cost=519.00..1701.89 rows=1 width=48) (never executed)"" Join Filter: (("sb"."bits")::"bit" = (B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit"))"" -> Nested Loop (cost=519.00..1688.45 rows=1 width=88) (never executed)"" -> Nested Loop (cost=0.00..1168.36 rows=1 width=24) (never executed)"" -> Nested Loop (cost=0.00..1154.93 rows=1 width=32) (never executed)"" -> Nested Loop (cost=0.00..1139.67 rows=1 width=16) (never executed)"" -> Nested Loop (cost=0.00..968.55 rows=1 width=16) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..170.61 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94 rows=1 width=16) (never executed)"" Index Cond: ((("st_ino")::bigint = ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" = 'medium.with_name'::"text"))"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("mv"."st_ino_target")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"" CTE stat_h"" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"" CTE stat_h_with_bits"" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"" SubPlan 9"" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"" SubPlan 2"" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.42 rows=1 width=21) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> Nested Loop (cost=519.00..1702.00 rows=1 width=48) (never executed)"" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"" -> Nested Loop (cost=0.00..1181.79 rows=1 width=33) (never executed)"" -> Nested Loop (cost=0.00..1168.36 rows=1 width=24) (never executed)"" -> Nested Loop (cost=0.00..1154.93 rows=1 width=32) (never executed)"" -> Nested Loop (cost=0.00..1139.67 rows=1 width=16) (never executed)"" -> Nested Loop (cost=0.00..968.55 rows=1 width=16) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..170.61 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94 rows=1 width=16) (never executed)"" Index Cond: ((("st_ino")::bigint = ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" = 'thumbnail.with_name'::"text"))"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("mv"."st_ino_target")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.42 rows=1 width=21) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"" CTE stat_h"" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"" CTE stat_h_with_bits"" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"" SubPlan 12"" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"" SubPlan 3"" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" SubPlan 1"" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)""Total runtime: 1.740 ms"

On 1/11/16, Saulo Merlo <smerlo50@outlook.com> wrote:

NEW QUERY:

SELECT
<<overquoting>>
WHERE f.nfs_file_path IS NULL
AND ((transaction_timestamp() AT TIME ZONE \'UTC\') > (f.st_mtime+ \'' . $fileMigrationMonthAge . ' months\' :: INTERVAL)) LIMIT 100;

From: smerlo50@outlook.com
To: clavadetscher@swisspug.org; vitaly.burovoy@gmail.com
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
Date: Mon, 11 Jan 2016 20:02:54 +0000

Still getting a sloooow one..
Any thoughts?

My hypothesis is; the 1 clause that will always be used is in the WHERE statement below. This can either be nfs_file_path or nfs_migration_date (both new columns). Adding an index on either of these columns and using them in the clause should improve things greatly.

How could I do that?
Lucas

"Limit (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.051..0.051 rows=0 loops=1)"
<<overquoting>>
"Total runtime: 1.395 ms"

Firstly, 1.4ms is not bad, I don't know how to improve your query.

Secondly, why do you leave second condition in the WHERE clause as it
was in your first letter? Such version of the condition can't use
index because of absence of it. It's impossible to create index with
column "(f.st_mtime+ \'' . $fileMigrationMonthAge . ' months\' ::
INTERVAL)". You have to change the condition the way where one part of
a condition at an optimization part can be simplified to a constant
and the other part of the condition represents a column of an existent
index (as it was written in my first answer).

--
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

Ok, thanks Vitaly.
I need to create a TEXT or CARCHAR index..
and another one with timestamptz
How can I do?Thanks

From: smerlo50@outlook.com
To: clavadetscher@swisspug.org; vitaly.burovoy@gmail.com
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
Date: Mon, 11 Jan 2016 21:37:43 +0000

Hey guys..
How could I create a timestampandtz index?
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)
)
From: smerlo50@outlook.com
To: clavadetscher@swisspug.org; vitaly.burovoy@gmail.com
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
Date: Mon, 11 Jan 2016 20:13:27 +0000

NEW QUERY:

SELECT j.clientid AS client_id,
ni.segment_index AS note_id,
f.st_ino AS file_id,
f.full_path AS filename,
f.segment_data AS main_binary,
fmeta.st_mtime AS date_created,
medium.inode_id AS medium_id,
medium.file_data AS medium_binary,
thumbnail.inode_id AS thumbnail_id,
thumbnail.file_data AS thumbnail_binary
FROM gorfs.inode_segments AS f
INNER JOIN gorfs.inodes AS fmeta ON fmeta.st_ino = f.st_ino
INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.st_ino
INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
AND mv.segment_index = 'main.with_name'
INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino
INNER JOIN gorfs.inode_segments AS fn ON fn.st_ino_target = fi.st_ino
INNER JOIN gorfs.inode_segments AS ni ON ni.st_ino_target = fn.st_ino
INNER JOIN public.ja_notes AS n ON n.id = CAST(ni.segment_index AS INTEGER)
INNER JOIN public.ja_jobs AS j ON j.id = n.jobid
LEFT JOIN
(SELECT f.st_ino AS inode_id,
f.segment_data AS file_data,
fi.st_ino
FROM gorfs.inode_segments AS f
INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.st_ino
INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
AND mv.segment_index = 'medium.with_name'
INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino ) AS medium ON medium.st_ino = fn.st_ino_target
LEFT JOIN
(SELECT f.st_ino AS inode_id,
f.segment_data AS file_data,
fi.st_ino
FROM gorfs.inode_segments AS f
INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.st_ino
INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
AND mv.segment_index = 'thumbnail.with_name'
INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino ) AS thumbnail ON thumbnail.st_ino = fn.st_ino_target
WHERE f.nfs_file_path IS NULL
AND ((transaction_timestamp() AT TIME ZONE \'UTC\') > (f.st_mtime+ \'' . $fileMigrationMonthAge . ' months\' :: INTERVAL)) LIMIT 100;

From: smerlo50@outlook.com
To: clavadetscher@swisspug.org; vitaly.burovoy@gmail.com
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
Date: Mon, 11 Jan 2016 20:02:54 +0000

Still getting a sloooow one..Any thoughts?
My hypothesis is; the 1 clause that will always be used is in the WHERE statement below. This can either be nfs_file_path or nfs_migration_date (both new columns). Adding an index on either of these columns and using them in the clause should improve things greatly.
How could I do that?Lucas
"Limit (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.051..0.051 rows=0 loops=1)"
" -> Nested Loop Left Join (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.048..0.048 rows=0 loops=1)"
" -> Nested Loop Left Join (cost=1038.00..2526.07 rows=1 width=154) (actual time=0.046..0.046 rows=0 loops=1)"
" -> Nested Loop (cost=519.01..824.17 rows=1 width=114) (actual time=0.044..0.044 rows=0 loops=1)"
" -> Nested Loop (cost=519.01..818.20 rows=1 width=114) (actual time=0.042..0.042 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..808.69 rows=1 width=106) (actual time=0.040..0.040 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..793.43 rows=1 width=106) (actual time=0.038..0.038 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..778.17 rows=1 width=98) (actual time=0.036..0.036 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..762.91 rows=1 width=98) (actual time=0.034..0.034 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..747.64 rows=1 width=98) (actual time=0.032..0.032 rows=0 loops=1)"
" Join Filter: (("t"."st_ino")::bigint = ("fd"."st_ino_target")::bigint)"
" -> Nested Loop (cost=519.00..732.38 rows=1 width=90) (actual time=0.030..0.030 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..718.94 rows=1 width=98) (actual time=0.028..0.028 rows=0 loops=1)"
" Join Filter: (CASE WHEN ("sb"."constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name") THEN (SubPlan 4) ELSE NULL::"bytea" END IS NOT NULL)"
" -> Nested Loop (cost=519.00..531.96 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"
" -> Index Scan using "ix_inode_segments_nfs" on "inodes" "t" (cost=0.01..11.76 rows=1 width=29) (actual time=0.024..0.024 rows=0 loops=1)"
" Index Cond: (("st_ctime")::timestamp without time zone < ("timezone"('UTC'::"text", "transaction_timestamp"()) - '1 year 6 mons'::interval))"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"
" SubPlan 6"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.30 rows=1 width=78) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("t"."st_ino")::bigint)"
" SubPlan 4"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fd" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("p"."st_ino_target")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "mv" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino")::bigint)"
" Filter: (("segment_index")::"text" = 'main.with_name'::"text")"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fi" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("mv"."st_ino")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fn" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fi"."st_ino")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "ni" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fn"."st_ino")::bigint)"
" -> Index Scan using "ja_notes_pkey" on "ja_notes" "n" (cost=0.00..9.50 rows=1 width=16) (never executed)"
" Index Cond: ("id" = ("ni"."segment_index")::integer)"
" -> Index Only Scan using "ix_jobs_top_by_client" on "ja_jobs" "j" (cost=0.00..5.96 rows=1 width=16) (never executed)"
" Index Cond: ("id" = "n"."jobid")"
" Heap Fetches: 0"
" -> Nested Loop (cost=519.00..1701.89 rows=1 width=48) (never executed)"
" Join Filter: (("sb"."bits")::"bit" = (B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit"))"
" -> Nested Loop (cost=519.00..1688.45 rows=1 width=88) (never executed)"
" -> Nested Loop (cost=0.00..1168.36 rows=1 width=24) (never executed)"
" -> Nested Loop (cost=0.00..1154.93 rows=1 width=32) (never executed)"
" -> Nested Loop (cost=0.00..1139.67 rows=1 width=16) (never executed)"
" -> Nested Loop (cost=0.00..968.55 rows=1 width=16) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..170.61 rows=40 width=16) (never executed)"
" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94 rows=1 width=16) (never executed)"
" Index Cond: ((("st_ino")::bigint = ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" = 'medium.with_name'::"text"))"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71 rows=40 width=16) (never executed)"
" Index Cond: (("st_ino")::bigint = ("mv"."st_ino_target")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"
" SubPlan 9"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"
" SubPlan 2"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.42 rows=1 width=21) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> Nested Loop (cost=519.00..1702.00 rows=1 width=48) (never executed)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"
" -> Nested Loop (cost=0.00..1181.79 rows=1 width=33) (never executed)"
" -> Nested Loop (cost=0.00..1168.36 rows=1 width=24) (never executed)"
" -> Nested Loop (cost=0.00..1154.93 rows=1 width=32) (never executed)"
" -> Nested Loop (cost=0.00..1139.67 rows=1 width=16) (never executed)"
" -> Nested Loop (cost=0.00..968.55 rows=1 width=16) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..170.61 rows=40 width=16) (never executed)"
" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94 rows=1 width=16) (never executed)"
" Index Cond: ((("st_ino")::bigint = ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" = 'thumbnail.with_name'::"text"))"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71 rows=40 width=16) (never executed)"
" Index Cond: (("st_ino")::bigint = ("mv"."st_ino_target")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.42 rows=1 width=21) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"
" SubPlan 12"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"
" SubPlan 3"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" SubPlan 1"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
"Total runtime: 1.395 ms"

From: smerlo50@outlook.com
To: clavadetscher@swisspug.org; vitaly.burovoy@gmail.com
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
Date: Mon, 11 Jan 2016 07:48:04 +0000

Thank you!

"Limit (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.053..0.053 rows=0 loops=1)"" -> Nested Loop Left Join (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.050..0.050 rows=0 loops=1)"" -> Nested Loop Left Join (cost=1038.00..2526.07 rows=1 width=154) (actual time=0.047..0.047 rows=0 loops=1)"" -> Nested Loop (cost=519.01..824.17 rows=1 width=114) (actual time=0.045..0.045 rows=0 loops=1)"" -> Nested Loop (cost=519.01..818.20 rows=1 width=114) (actual time=0.043..0.043 rows=0 loops=1)"" -> Nested Loop (cost=519.00..808.69 rows=1 width=106) (actual time=0.041..0.041 rows=0 loops=1)"" -> Nested Loop (cost=519.00..793.43 rows=1 width=106) (actual time=0.040..0.040 rows=0 loops=1)"" -> Nested Loop (cost=519.00..778.17 rows=1 width=98) (actual time=0.038..0.038 rows=0 loops=1)"" -> Nested Loop (cost=519.00..762.91 rows=1 width=98) (actual time=0.036..0.036 rows=0 loops=1)"" -> Nested Loop (cost=519.00..747.64 rows=1 width=98) (actual time=0.034..0.034 rows=0 loops=1)"" Join Filter: (("t"."st_ino")::bigint = ("fd"."st_ino_target")::bigint)"" -> Nested Loop (cost=519.00..732.38 rows=1 width=90) (actual time=0.031..0.031 rows=0 loops=1)"" -> Nested Loop (cost=519.00..718.94 rows=1 width=98) (actual time=0.029..0.029 rows=0 loops=1)"" Join Filter: (CASE WHEN ("sb"."constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name") THEN (SubPlan 4) ELSE NULL::"bytea" END IS NOT NULL)"" -> Nested Loop (cost=519.00..531.96 rows=1 width=20) (actual time=0.027..0.027 rows=0 loops=1)"" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"" -> Index Scan using "ix_inode_segments_nfs" on "inodes" "t" (cost=0.01..11.76 rows=1 width=29) (actual time=0.026..0.026 rows=0 loops=1)"" Index Cond: (("st_ctime")::timestamp without time zone < ("timezone"('UTC'::"text", "transaction_timestamp"()) - '2 years'::interval))"" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"" CTE stat_h"" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"" CTE stat_h_with_bits"" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"" SubPlan 6"" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.30 rows=1 width=78) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("t"."st_ino")::bigint)"" SubPlan 4"" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fd" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("p"."st_ino_target")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "mv" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino")::bigint)"" Filter: (("segment_index")::"text" = 'main.with_name'::"text")"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fi" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("mv"."st_ino")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fn" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fi"."st_ino")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "ni" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fn"."st_ino")::bigint)"" -> Index Scan using "ja_notes_pkey" on "ja_notes" "n" (cost=0.00..9.50 rows=1 width=16) (never executed)"" Index Cond: ("id" = ("ni"."segment_index")::integer)"" -> Index Only Scan using "ix_jobs_top_by_client" on "ja_jobs" "j" (cost=0.00..5.96 rows=1 width=16) (never executed)"" Index Cond: ("id" = "n"."jobid")"" Heap Fetches: 0"" -> Nested Loop (cost=519.00..1701.89 rows=1 width=48) (never executed)"" Join Filter: (("sb"."bits")::"bit" = (B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit"))"" -> Nested Loop (cost=519.00..1688.45 rows=1 width=88) (never executed)"" -> Nested Loop (cost=0.00..1168.36 rows=1 width=24) (never executed)"" -> Nested Loop (cost=0.00..1154.93 rows=1 width=32) (never executed)"" -> Nested Loop (cost=0.00..1139.67 rows=1 width=16) (never executed)"" -> Nested Loop (cost=0.00..968.55 rows=1 width=16) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..170.61 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94 rows=1 width=16) (never executed)"" Index Cond: ((("st_ino")::bigint = ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" = 'medium.with_name'::"text"))"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("mv"."st_ino_target")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"" CTE stat_h"" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"" CTE stat_h_with_bits"" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"" SubPlan 9"" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"" SubPlan 2"" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.42 rows=1 width=21) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> Nested Loop (cost=519.00..1702.00 rows=1 width=48) (never executed)"" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"" -> Nested Loop (cost=0.00..1181.79 rows=1 width=33) (never executed)"" -> Nested Loop (cost=0.00..1168.36 rows=1 width=24) (never executed)"" -> Nested Loop (cost=0.00..1154.93 rows=1 width=32) (never executed)"" -> Nested Loop (cost=0.00..1139.67 rows=1 width=16) (never executed)"" -> Nested Loop (cost=0.00..968.55 rows=1 width=16) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..170.61 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94 rows=1 width=16) (never executed)"" Index Cond: ((("st_ino")::bigint = ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" = 'thumbnail.with_name'::"text"))"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("mv"."st_ino_target")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.42 rows=1 width=21) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"" CTE stat_h"" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"" CTE stat_h_with_bits"" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"" SubPlan 12"" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"" SubPlan 3"" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" SubPlan 1"" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)""Total runtime: 1.740 ms"

UPDATED LAST EMAIL

From: smerlo50@outlook.com
To: clavadetscher@swisspug.org; vitaly.burovoy@gmail.com
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
Date: Mon, 11 Jan 2016 21:58:42 +0000

Ok, thanks Vitaly.
I need to create a TEXT or CARCHAR index..
and another one with timestamptz
How can I do?Thanks
EXPLAIN ANALYZE:

"Limit (cost=1935605.69..4178324.29 rows=1 width=170) (actual time=192862.383..288870.658 rows=100 loops=1)"
" -> Nested Loop Left Join (cost=1935605.69..4178324.29 rows=1 width=170) (actual time=192862.381..288870.354 rows=100 loops=1)"
" -> Nested Loop Left Join (cost=1935086.70..4177093.51 rows=1 width=138) (actual time=192862.144..288853.281 rows=100 loops=1)"
" -> Nested Loop (cost=1934567.70..4176377.73 rows=1 width=98) (actual time=192844.318..288152.810 rows=100 loops=1)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"
" Rows Removed by Join Filter: 25754"
" -> Nested Loop (cost=1934048.71..4175858.19 rows=1 width=103) (actual time=191738.882..287794.378 rows=25854 loops=1)"
" -> Nested Loop (cost=1934048.71..4175844.82 rows=1 width=86) (actual time=191738.847..280920.634 rows=25854 loops=1)"
" -> Hash Join (cost=1934048.71..4175831.45 rows=1 width=94) (actual time=191678.189..226929.370 rows=25854 loops=1)"
" Hash Cond: ((("p"."st_ino")::bigint = ("iseg"."st_ino")::bigint) AND (("p"."st_ino_target")::bigint = ("iseg"."st_ino_target")::bigint))"
" -> Seq Scan on "inode_segments" "p" (cost=0.00..2233424.52 rows=303935 width=78) (actual time=12.839..28971.266 rows=3331297 loops=1)"
" Filter: ((CASE WHEN (("st_ino_target")::bigint = 2) THEN NULL::character varying ELSE ("segment_index")::character varying END)::"text" = 'main'::"text")"
" Rows Removed by Filter: 18841223"
" -> Hash (cost=1929489.68..1929489.68 rows=303935 width=16) (actual time=191445.664..191445.664 rows=40682177 loops=1)"
" Buckets: 32768 Batches: 128 (originally 1) Memory Usage: 16385kB"
" -> Seq Scan on "inode_segments" "iseg" (cost=0.00..1929489.68 rows=303935 width=16) (actual time=0.004..109181.504 rows=60786970 loops=1)"
" Filter: ("nfs_migration_date" IS NULL)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.36 rows=1 width=8) (actual time=2.080..2.082 rows=1 loops=25854)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.36 rows=1 width=29) (actual time=0.257..0.259 rows=1 loops=25854)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" Filter: (("st_mtime")::timestamp without time zone < ("timezone"('UTC'::"text", "transaction_timestamp"()) - '1 mon'::interval))"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.52 rows=1 width=72) (actual time=0.004..0.009 rows=1 loops=25854)"
" Filter: ("constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name")"
" Rows Removed by Filter: 22"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (actual time=0.007..0.041 rows=23 loops=1)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (actual time=0.122..0.961 rows=23 loops=1)"
" SubPlan 5"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (actual time=0.025..0.026 rows=1 loops=23)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (actual time=0.010..0.013 rows=3 loops=23)"
" -> Nested Loop (cost=519.00..715.77 rows=1 width=48) (actual time=6.971..6.997 rows=1 loops=100)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"
" -> Nested Loop (cost=0.00..196.22 rows=1 width=33) (actual time=6.649..6.660 rows=1 loops=100)"
" -> Nested Loop (cost=0.00..182.86 rows=1 width=16) (actual time=5.668..5.674 rows=1 loops=100)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.36 rows=1 width=8) (actual time=0.009..0.011 rows=1 loops=100)"
" Index Cond: (("st_ino")::bigint = ("i"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "p" (cost=0.00..169.50 rows=1 width=16) (actual time=5.652..5.654 rows=1 loops=100)"
" Index Cond: (("st_ino")::bigint = ("i"."st_ino")::bigint)"
" Filter: ((CASE WHEN (("st_ino_target")::bigint = 2) THEN NULL::character varying ELSE ("segment_index")::character varying END)::"text" = 'thumbnail'::"text")"
" Rows Removed by Filter: 1"
" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.35 rows=1 width=21) (actual time=1.004..1.006 rows=1 loops=97)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.52 rows=1 width=72) (actual time=0.008..0.021 rows=1 loops=97)"
" Filter: ("constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name")"
" Rows Removed by Filter: 22"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (actual time=0.005..0.053 rows=23 loops=1)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (actual time=0.130..0.819 rows=23 loops=1)"
" SubPlan 11"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (actual time=0.024..0.024 rows=1 loops=23)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (actual time=0.009..0.013 rows=3 loops=23)"
" SubPlan 3"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (actual time=0.309..0.310 rows=1 loops=97)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (actual time=0.285..0.287 rows=1 loops=97)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> Nested Loop (cost=519.00..715.77 rows=1 width=48) (actual time=0.031..0.031 rows=0 loops=100)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"
" -> Nested Loop (cost=0.00..196.22 rows=1 width=33) (actual time=0.028..0.028 rows=0 loops=100)"
" -> Nested Loop (cost=0.00..182.86 rows=1 width=16) (actual time=0.025..0.025 rows=0 loops=100)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.36 rows=1 width=8) (actual time=0.009..0.011 rows=1 loops=100)"
" Index Cond: (("st_ino")::bigint = ("i"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "p" (cost=0.00..169.50 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=100)"
" Index Cond: (("st_ino")::bigint = ("i"."st_ino")::bigint)"
" Filter: ((CASE WHEN (("st_ino_target")::bigint = 2) THEN NULL::character varying ELSE ("segment_index")::character varying END)::"text" = 'medium'::"text")"
" Rows Removed by Filter: 2"
" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.35 rows=1 width=21) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.52 rows=1 width=72) (never executed)"
" Filter: ("constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name")"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"
" SubPlan 8"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"
" SubPlan 2"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" SubPlan 1"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (actual time=0.128..0.129 rows=1 loops=100)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (actual time=0.114..0.116 rows=1 loops=100)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
"Total runtime: 289499.220 ms"

From: smerlo50@outlook.com
To: clavadetscher@swisspug.org; vitaly.burovoy@gmail.com
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
Date: Mon, 11 Jan 2016 21:37:43 +0000

Hey guys..
How could I create a timestampandtz index?
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)
)
From: smerlo50@outlook.com
To: clavadetscher@swisspug.org; vitaly.burovoy@gmail.com
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
Date: Mon, 11 Jan 2016 20:13:27 +0000

NEW QUERY:

SELECT j.clientid AS client_id,
ni.segment_index AS note_id,
f.st_ino AS file_id,
f.full_path AS filename,
f.segment_data AS main_binary,
fmeta.st_mtime AS date_created,
medium.inode_id AS medium_id,
medium.file_data AS medium_binary,
thumbnail.inode_id AS thumbnail_id,
thumbnail.file_data AS thumbnail_binary
FROM gorfs.inode_segments AS f
INNER JOIN gorfs.inodes AS fmeta ON fmeta.st_ino = f.st_ino
INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.st_ino
INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
AND mv.segment_index = 'main.with_name'
INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino
INNER JOIN gorfs.inode_segments AS fn ON fn.st_ino_target = fi.st_ino
INNER JOIN gorfs.inode_segments AS ni ON ni.st_ino_target = fn.st_ino
INNER JOIN public.ja_notes AS n ON n.id = CAST(ni.segment_index AS INTEGER)
INNER JOIN public.ja_jobs AS j ON j.id = n.jobid
LEFT JOIN
(SELECT f.st_ino AS inode_id,
f.segment_data AS file_data,
fi.st_ino
FROM gorfs.inode_segments AS f
INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.st_ino
INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
AND mv.segment_index = 'medium.with_name'
INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino ) AS medium ON medium.st_ino = fn.st_ino_target
LEFT JOIN
(SELECT f.st_ino AS inode_id,
f.segment_data AS file_data,
fi.st_ino
FROM gorfs.inode_segments AS f
INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.st_ino
INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
AND mv.segment_index = 'thumbnail.with_name'
INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino ) AS thumbnail ON thumbnail.st_ino = fn.st_ino_target
WHERE f.nfs_file_path IS NULL
AND ((transaction_timestamp() AT TIME ZONE \'UTC\') > (f.st_mtime+ \'' . $fileMigrationMonthAge . ' months\' :: INTERVAL)) LIMIT 100;

From: smerlo50@outlook.com
To: clavadetscher@swisspug.org; vitaly.burovoy@gmail.com
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
Date: Mon, 11 Jan 2016 20:02:54 +0000

Still getting a sloooow one..Any thoughts?
My hypothesis is; the 1 clause that will always be used is in the WHERE statement below. This can either be nfs_file_path or nfs_migration_date (both new columns). Adding an index on either of these columns and using them in the clause should improve things greatly.
How could I do that?Lucas
"Limit (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.051..0.051 rows=0 loops=1)"
" -> Nested Loop Left Join (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.048..0.048 rows=0 loops=1)"
" -> Nested Loop Left Join (cost=1038.00..2526.07 rows=1 width=154) (actual time=0.046..0.046 rows=0 loops=1)"
" -> Nested Loop (cost=519.01..824.17 rows=1 width=114) (actual time=0.044..0.044 rows=0 loops=1)"
" -> Nested Loop (cost=519.01..818.20 rows=1 width=114) (actual time=0.042..0.042 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..808.69 rows=1 width=106) (actual time=0.040..0.040 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..793.43 rows=1 width=106) (actual time=0.038..0.038 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..778.17 rows=1 width=98) (actual time=0.036..0.036 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..762.91 rows=1 width=98) (actual time=0.034..0.034 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..747.64 rows=1 width=98) (actual time=0.032..0.032 rows=0 loops=1)"
" Join Filter: (("t"."st_ino")::bigint = ("fd"."st_ino_target")::bigint)"
" -> Nested Loop (cost=519.00..732.38 rows=1 width=90) (actual time=0.030..0.030 rows=0 loops=1)"
" -> Nested Loop (cost=519.00..718.94 rows=1 width=98) (actual time=0.028..0.028 rows=0 loops=1)"
" Join Filter: (CASE WHEN ("sb"."constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name") THEN (SubPlan 4) ELSE NULL::"bytea" END IS NOT NULL)"
" -> Nested Loop (cost=519.00..531.96 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"
" -> Index Scan using "ix_inode_segments_nfs" on "inodes" "t" (cost=0.01..11.76 rows=1 width=29) (actual time=0.024..0.024 rows=0 loops=1)"
" Index Cond: (("st_ctime")::timestamp without time zone < ("timezone"('UTC'::"text", "transaction_timestamp"()) - '1 year 6 mons'::interval))"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"
" SubPlan 6"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.30 rows=1 width=78) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("t"."st_ino")::bigint)"
" SubPlan 4"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fd" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("p"."st_ino_target")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "mv" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino")::bigint)"
" Filter: (("segment_index")::"text" = 'main.with_name'::"text")"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fi" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("mv"."st_ino")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fn" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fi"."st_ino")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "ni" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fn"."st_ino")::bigint)"
" -> Index Scan using "ja_notes_pkey" on "ja_notes" "n" (cost=0.00..9.50 rows=1 width=16) (never executed)"
" Index Cond: ("id" = ("ni"."segment_index")::integer)"
" -> Index Only Scan using "ix_jobs_top_by_client" on "ja_jobs" "j" (cost=0.00..5.96 rows=1 width=16) (never executed)"
" Index Cond: ("id" = "n"."jobid")"
" Heap Fetches: 0"
" -> Nested Loop (cost=519.00..1701.89 rows=1 width=48) (never executed)"
" Join Filter: (("sb"."bits")::"bit" = (B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit"))"
" -> Nested Loop (cost=519.00..1688.45 rows=1 width=88) (never executed)"
" -> Nested Loop (cost=0.00..1168.36 rows=1 width=24) (never executed)"
" -> Nested Loop (cost=0.00..1154.93 rows=1 width=32) (never executed)"
" -> Nested Loop (cost=0.00..1139.67 rows=1 width=16) (never executed)"
" -> Nested Loop (cost=0.00..968.55 rows=1 width=16) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..170.61 rows=40 width=16) (never executed)"
" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94 rows=1 width=16) (never executed)"
" Index Cond: ((("st_ino")::bigint = ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" = 'medium.with_name'::"text"))"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71 rows=40 width=16) (never executed)"
" Index Cond: (("st_ino")::bigint = ("mv"."st_ino_target")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"
" SubPlan 9"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"
" SubPlan 2"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.42 rows=1 width=21) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> Nested Loop (cost=519.00..1702.00 rows=1 width=48) (never executed)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"
" -> Nested Loop (cost=0.00..1181.79 rows=1 width=33) (never executed)"
" -> Nested Loop (cost=0.00..1168.36 rows=1 width=24) (never executed)"
" -> Nested Loop (cost=0.00..1154.93 rows=1 width=32) (never executed)"
" -> Nested Loop (cost=0.00..1139.67 rows=1 width=16) (never executed)"
" -> Nested Loop (cost=0.00..968.55 rows=1 width=16) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..170.61 rows=40 width=16) (never executed)"
" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94 rows=1 width=16) (never executed)"
" Index Cond: ((("st_ino")::bigint = ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" = 'thumbnail.with_name'::"text"))"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71 rows=40 width=16) (never executed)"
" Index Cond: (("st_ino")::bigint = ("mv"."st_ino_target")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.25 rows=1 width=16) (never executed)"
" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.42 rows=1 width=21) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"
" SubPlan 12"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"
" SubPlan 3"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" SubPlan 1"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
"Total runtime: 1.395 ms"

From: smerlo50@outlook.com
To: clavadetscher@swisspug.org; vitaly.burovoy@gmail.com
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
Date: Mon, 11 Jan 2016 07:48:04 +0000

Thank you!

"Limit (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.053..0.053 rows=0 loops=1)"" -> Nested Loop Left Join (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.050..0.050 rows=0 loops=1)"" -> Nested Loop Left Join (cost=1038.00..2526.07 rows=1 width=154) (actual time=0.047..0.047 rows=0 loops=1)"" -> Nested Loop (cost=519.01..824.17 rows=1 width=114) (actual time=0.045..0.045 rows=0 loops=1)"" -> Nested Loop (cost=519.01..818.20 rows=1 width=114) (actual time=0.043..0.043 rows=0 loops=1)"" -> Nested Loop (cost=519.00..808.69 rows=1 width=106) (actual time=0.041..0.041 rows=0 loops=1)"" -> Nested Loop (cost=519.00..793.43 rows=1 width=106) (actual time=0.040..0.040 rows=0 loops=1)"" -> Nested Loop (cost=519.00..778.17 rows=1 width=98) (actual time=0.038..0.038 rows=0 loops=1)"" -> Nested Loop (cost=519.00..762.91 rows=1 width=98) (actual time=0.036..0.036 rows=0 loops=1)"" -> Nested Loop (cost=519.00..747.64 rows=1 width=98) (actual time=0.034..0.034 rows=0 loops=1)"" Join Filter: (("t"."st_ino")::bigint = ("fd"."st_ino_target")::bigint)"" -> Nested Loop (cost=519.00..732.38 rows=1 width=90) (actual time=0.031..0.031 rows=0 loops=1)"" -> Nested Loop (cost=519.00..718.94 rows=1 width=98) (actual time=0.029..0.029 rows=0 loops=1)"" Join Filter: (CASE WHEN ("sb"."constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name") THEN (SubPlan 4) ELSE NULL::"bytea" END IS NOT NULL)"" -> Nested Loop (cost=519.00..531.96 rows=1 width=20) (actual time=0.027..0.027 rows=0 loops=1)"" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"" -> Index Scan using "ix_inode_segments_nfs" on "inodes" "t" (cost=0.01..11.76 rows=1 width=29) (actual time=0.026..0.026 rows=0 loops=1)"" Index Cond: (("st_ctime")::timestamp without time zone < ("timezone"('UTC'::"text", "transaction_timestamp"()) - '2 years'::interval))"" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"" CTE stat_h"" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"" CTE stat_h_with_bits"" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"" SubPlan 6"" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.30 rows=1 width=78) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("t"."st_ino")::bigint)"" SubPlan 4"" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fd" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("p"."st_ino_target")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "mv" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino")::bigint)"" Filter: (("segment_index")::"text" = 'main.with_name'::"text")"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fi" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("mv"."st_ino")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fn" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fi"."st_ino")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "ni" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fn"."st_ino")::bigint)"" -> Index Scan using "ja_notes_pkey" on "ja_notes" "n" (cost=0.00..9.50 rows=1 width=16) (never executed)"" Index Cond: ("id" = ("ni"."segment_index")::integer)"" -> Index Only Scan using "ix_jobs_top_by_client" on "ja_jobs" "j" (cost=0.00..5.96 rows=1 width=16) (never executed)"" Index Cond: ("id" = "n"."jobid")"" Heap Fetches: 0"" -> Nested Loop (cost=519.00..1701.89 rows=1 width=48) (never executed)"" Join Filter: (("sb"."bits")::"bit" = (B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit"))"" -> Nested Loop (cost=519.00..1688.45 rows=1 width=88) (never executed)"" -> Nested Loop (cost=0.00..1168.36 rows=1 width=24) (never executed)"" -> Nested Loop (cost=0.00..1154.93 rows=1 width=32) (never executed)"" -> Nested Loop (cost=0.00..1139.67 rows=1 width=16) (never executed)"" -> Nested Loop (cost=0.00..968.55 rows=1 width=16) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..170.61 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94 rows=1 width=16) (never executed)"" Index Cond: ((("st_ino")::bigint = ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" = 'medium.with_name'::"text"))"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("mv"."st_ino_target")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"" CTE stat_h"" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"" CTE stat_h_with_bits"" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"" SubPlan 9"" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"" SubPlan 2"" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.42 rows=1 width=21) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> Nested Loop (cost=519.00..1702.00 rows=1 width=48) (never executed)"" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"" -> Nested Loop (cost=0.00..1181.79 rows=1 width=33) (never executed)"" -> Nested Loop (cost=0.00..1168.36 rows=1 width=24) (never executed)"" -> Nested Loop (cost=0.00..1154.93 rows=1 width=32) (never executed)"" -> Nested Loop (cost=0.00..1139.67 rows=1 width=16) (never executed)"" -> Nested Loop (cost=0.00..968.55 rows=1 width=16) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..170.61 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..19.94 rows=1 width=16) (never executed)"" Index Cond: ((("st_ino")::bigint = ("fi"."st_ino_target")::bigint) AND (("segment_index")::"text" = 'thumbnail.with_name'::"text"))"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..170.71 rows=40 width=16) (never executed)"" Index Cond: (("st_ino")::bigint = ("mv"."st_ino_target")::bigint)"" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..15.25 rows=1 width=16) (never executed)"" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.43 rows=1 width=8) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.42 rows=1 width=21) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (never executed)"" CTE stat_h"" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"" CTE stat_h_with_bits"" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"" SubPlan 12"" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"" SubPlan 3"" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"" SubPlan 1"" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)""Total runtime: 1.740 ms"

On 1/11/16, Saulo Merlo <smerlo50@outlook.com> wrote:

Ok, thanks Vitaly.
I need to create a TEXT or VARCHAR index.
and another one with timestamptz
How can I do?Thanks

How to create indexes of different types is written at [1]http://www.postgresql.org/docs/9.2/static/indexes-types.html.
But I thing you need something else. You have to create index on the
specified column(s). Type of data will be recognized by a column type.
Your primary goal is to avoid sequence scan of big tables. It is
important to read explain[2]http://www.postgresql.org/docs/9.2/static/using-explain.html and find _tables_ and _columns_ (or
_expressions_) where they appears in the "seq scan/filter" blocks to
decide whether it worth to create an index or not.
You can create index on expression[3]http://www.postgresql.org/docs/9.2/static/indexes-expressional.html where column(s) of the table or
constants are involved, but keep in mind it is impossible to create an
index using columns of different tables or using non-constants (e.g.
"now()").

Note that your two last posts doesn't have information what columns you need.

[1]: http://www.postgresql.org/docs/9.2/static/indexes-types.html
[2]: http://www.postgresql.org/docs/9.2/static/using-explain.html
[3]: http://www.postgresql.org/docs/9.2/static/indexes-expressional.html

P.S.: please, delete old (irrelevant) information which is not
necessary for answering.

From: smerlo50@outlook.com
To: clavadetscher@swisspug.org; vitaly.burovoy@gmail.com
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
Date: Mon, 11 Jan 2016 21:37:43 +0000

Hey guys..
How could I create a timestampandtz index?
CREATE TABLE gorfs.inode_segments
(
<<overquoting>>
)

--
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

Thanks Vitaly for all your help. I'll have a very deep look on the links you have provided. In the meantime, I'll also post here what I need.. IF you could help one more time, would be very very nice.
Thank you again.
This can either be nfs_file_path or nfs_migration_date (both new columns). Adding an index on either of these columns and using them in the clause should improve things greatly.
QUERY:
SELECT
main.inode_id AS file_id,
main.file_data AS main_binary,
main.node_full_path AS filename,
main.last_modified AS date_created,
medium.inode_id AS medium_id,
medium.file_data AS medium_binary,
thumbnail.inode_id AS thumbnail_id,
thumbnail.file_data AS thumbnail_binary
FROM
gorfs.nodes AS main
INNER JOIN
gorfs.inode_segments AS iseg ON iseg.st_ino = main.parent_inode_id
AND main.relative_path = 'main'
AND main.object_type = 'S_IFREG'
AND iseg.nfs_migration_date IS NULL
AND (main.last_modified <
(transaction_timestamp() AT TIME ZONE 'UTC' - '1 months' :: INTERVAL))
AND iseg.st_ino_target = main.inode_id
LEFT JOIN
gorfs.nodes AS medium
ON medium.parent_inode_id = main.parent_inode_id
AND medium.relative_path = 'medium'
AND medium.object_type = 'S_IFREG'
LEFT JOIN
gorfs.nodes AS thumbnail
ON thumbnail.parent_inode_id = main.parent_inode_id
AND thumbnail.relative_path = 'thumbnail'
AND thumbnail.object_type = 'S_IFREG'
LIMIT
100;
INDEX CREATED:CREATE INDEX CONCURRENTLY ix_inode_segments_nfs_file_path on gorfs.inode_segments USING btree ("full_path");
full_path:ALTER TABLE gorfs.inode_segments ADD COLUMN full_path "gorfs"."absolute_pathname";
EXPLAIN ANALYZE:

"Limit (cost=1935606.57..4178326.49 rows=1 width=170) (actual time=199195.079..315313.338 rows=100 loops=1)"
" -> Nested Loop Left Join (cost=1935606.57..4178326.49 rows=1 width=170) (actual time=199195.076..315313.089 rows=100 loops=1)"
" -> Nested Loop Left Join (cost=1935087.58..4177095.71 rows=1 width=138) (actual time=199195.015..315156.343 rows=100 loops=1)"
" -> Nested Loop (cost=1934568.58..4176379.93 rows=1 width=98) (actual time=199162.474..314565.271 rows=100 loops=1)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"
" Rows Removed by Join Filter: 34533"
" -> Nested Loop (cost=1934049.58..4175860.39 rows=1 width=103) (actual time=196125.245..314086.043 rows=34633 loops=1)"
" -> Nested Loop (cost=1934049.58..4175847.02 rows=1 width=86) (actual time=196125.213..305961.431 rows=34634 loops=1)"
" -> Hash Join (cost=1934049.58..4175833.65 rows=1 width=94) (actual time=196094.683..238436.508 rows=34634 loops=1)"
" Hash Cond: ((("p"."st_ino")::bigint = ("iseg"."st_ino")::bigint) AND (("p"."st_ino_target")::bigint = ("iseg"."st_ino_target")::bigint))"
" -> Seq Scan on "inode_segments" "p" (cost=0.00..2233425.84 rows=303935 width=78) (actual time=0.046..34047.515 rows=4466887 loops=1)"
" Filter: ((CASE WHEN (("st_ino_target")::bigint = 2) THEN NULL::character varying ELSE ("segment_index")::character varying END)::"text" = 'main'::"text")"
" Rows Removed by Filter: 25643122"
" -> Hash (cost=1929490.56..1929490.56 rows=303935 width=16) (actual time=195921.025..195921.025 rows=40682288 loops=1)"
" Buckets: 32768 Batches: 128 (originally 1) Memory Usage: 16385kB"
" -> Seq Scan on "inode_segments" "iseg" (cost=0.00..1929490.56 rows=303935 width=16) (actual time=0.002..112215.501 rows=60787096 loops=1)"
" Filter: ("nfs_migration_date" IS NULL)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.36 rows=1 width=8) (actual time=1.942..1.943 rows=1 loops=34634)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.36 rows=1 width=29) (actual time=0.226..0.228 rows=1 loops=34634)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" Filter: (("st_mtime")::timestamp without time zone < ("timezone"('UTC'::"text", "transaction_timestamp"()) - '1 mon'::interval))"
" Rows Removed by Filter: 0"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.52 rows=1 width=72) (actual time=0.004..0.009 rows=1 loops=34633)"
" Filter: ("constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name")"
" Rows Removed by Filter: 22"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (actual time=0.003..0.035 rows=23 loops=1)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (actual time=0.096..0.796 rows=23 loops=1)"
" SubPlan 5"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (actual time=0.023..0.024 rows=1 loops=23)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (actual time=0.009..0.013 rows=3 loops=23)"
" -> Nested Loop (cost=519.00..715.77 rows=1 width=48) (actual time=5.864..5.904 rows=1 loops=100)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"
" -> Nested Loop (cost=0.00..196.22 rows=1 width=33) (actual time=5.374..5.400 rows=1 loops=100)"
" -> Nested Loop (cost=0.00..182.86 rows=1 width=16) (actual time=4.802..4.809 rows=1 loops=100)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.36 rows=1 width=8) (actual time=0.008..0.010 rows=1 loops=100)"
" Index Cond: (("st_ino")::bigint = ("i"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "p" (cost=0.00..169.50 rows=1 width=16) (actual time=4.788..4.790 rows=1 loops=100)"
" Index Cond: (("st_ino")::bigint = ("i"."st_ino")::bigint)"
" Filter: ((CASE WHEN (("st_ino_target")::bigint = 2) THEN NULL::character varying ELSE ("segment_index")::character varying END)::"text" = 'thumbnail'::"text")"
" Rows Removed by Filter: 1"
" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.35 rows=1 width=21) (actual time=0.589..0.591 rows=1 loops=96)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.52 rows=1 width=72) (actual time=0.007..0.019 rows=1 loops=96)"
" Filter: ("constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name")"
" Rows Removed by Filter: 22"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (actual time=0.005..0.037 rows=23 loops=1)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (actual time=0.100..0.788 rows=23 loops=1)"
" SubPlan 11"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (actual time=0.023..0.024 rows=1 loops=23)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (actual time=0.008..0.012 rows=3 loops=23)"
" SubPlan 3"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (actual time=0.492..0.493 rows=1 loops=96)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (actual time=0.472..0.474 rows=1 loops=96)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> Nested Loop (cost=519.00..715.77 rows=1 width=48) (actual time=0.034..0.034 rows=0 loops=100)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"
" -> Nested Loop (cost=0.00..196.22 rows=1 width=33) (actual time=0.032..0.032 rows=0 loops=100)"
" -> Nested Loop (cost=0.00..182.86 rows=1 width=16) (actual time=0.029..0.029 rows=0 loops=100)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..13.36 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=100)"
" Index Cond: (("st_ino")::bigint = ("i"."st_ino")::bigint)"
" Filter: ((("st_ino")::bigint = 2) OR ((B'00000000000000001111000000000000'::"bit" & ("st_mode")::"bit") = B'00000000000000000100000000000000'::"bit"))"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "p" (cost=0.00..169.50 rows=1 width=16) (actual time=0.013..0.013 rows=0 loops=100)"
" Index Cond: (("st_ino")::bigint = ("i"."st_ino")::bigint)"
" Filter: ((CASE WHEN (("st_ino_target")::bigint = 2) THEN NULL::character varying ELSE ("segment_index")::character varying END)::"text" = 'medium'::"text")"
" Rows Removed by Filter: 2"
" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..13.35 rows=1 width=21) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.52 rows=1 width=72) (never executed)"
" Filter: ("constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name")"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (never executed)"
" CTE stat_h_with_bits"
" -> CTE Scan on "stat_h" "s" (cost=0.00..518.71 rows=23 width=68) (never executed)"
" SubPlan 8"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (never executed)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (never executed)"
" SubPlan 2"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (never executed)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (never executed)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" SubPlan 1"
" -> Aggregate (cost=171.66..171.67 rows=1 width=574) (actual time=1.523..1.524 rows=1 loops=100)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..171.55 rows=40 width=574) (actual time=1.512..1.514 rows=1 loops=100)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
"Total runtime: 315725.301 ms"

nfs_file_path - COLUMNALTER TABLE gorfs.inode_segments ADD COLUMN nfs_file_path "text";
nfs_migration_date - COLUMN - HAVE TO CREATE AN INDEX TO ITALTER TABLE gorfs.inode_segments ADD COLUMN nfs_migration_date timestamp without time zone;
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....
nfs_file_path "text",
nfs_migration_date timestamp without time zone,
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
);

UPDATED:
Index created:create index concurrently inode_segments_st_ino_target_pidx on gorfs.inode_segments (st_ino desc, st_ino_target desc) where nfs_migration_date is null;
NEW EXPLAIN ANALYZE:http://explain.depesz.com/s/Swu
I also am able to create a temporary table to store migrations, which may be the best option (no longer need to join new columns in query)
If you could help with that as well..Thank you
gorfs.nodes is a 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";
gorfs.inode_segments:

Table "gorfs.inode_segments" Column | Type | Modifiers--------------------+------------------------------+----------- st_ino | "gorfs"."ino_t" | not null segment_index | "gorfs"."pathname_component" | not null st_ino_target | "gorfs"."ino_t" | full_path | "gorfs"."absolute_pathname" | segment_data | "bytea" | nfs_migration_date | timestamp with time zone | nfs_file_path | "text" |Indexes: "pk_inode_segments" PRIMARY KEY, "btree" ("st_ino", "segment_index") "uc_no_duplicate_full_paths" UNIQUE CONSTRAINT, "btree" ("full_path") "inode_segments_st_ino_target_pidx" "btree" ("st_ino" DESC, "st_ino_target" DESC) WHERE "nfs_migration_date" IS NULL "ix_inode_segments_climb_tree" "btree" ("segment_index", "st_ino_target") "ix_inode_segments_filter_by_subtree" "btree" ("full_path" "varchar_pattern_ops") WHERE "full_path" IS NOT NULL "ix_inode_segments_full_path_resolution" "btree" ("st_ino", "full_path") "ix_inode_segments_gsdi_pk" "btree" (("st_ino"::"text"), ("segment_index"::"text")) "ix_inode_segments_ja_files_lookup" "btree" ((CASE WHEN "full_path"::"text" ~ '/[^/]*\.[^/]*$'::"text" THEN "upper"("regexp_replace"("full_path"::"text", '.*\.'::"text", ''::"text", 'g'::"text")) ELSE NULL::"text"END)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text") "ix_inode_segments_nfs_file_path" "btree" ("full_path") "ix_inode_segments_nfs_migration_date" "btree" ("nfs_migration_date") WHERE "nfs_migration_date" IS NULL "ix_inode_segments_nfs_st_ino" "btree" ("st_ino") "ix_inode_segments_notes_clientids" "btree" (("split_part"("full_path"::"text", '/'::"text", 4)::integer)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text") "ix_inode_segments_notes_fileids" "btree" (("split_part"("full_path"::"text", '/'::"text", 8)::integer)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text") "ix_inode_segments_notes_noteids" "btree" ((NULLIF("split_part"("full_path"::"text", '/'::"text", 6), 'unassigned'::"text")::integer)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text") "ix_inode_segments_segment_indexes" "btree" ("segment_index") "ix_inode_segments_st_ino_targets" "btree" ("st_ino_target") "ix_inode_segments_st_inos" "btree" ("st_ino")Check constraints: "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)Foreign-key constraints: "fk_host_inode_must_exist" FOREIGN KEY ("st_ino") REFERENCES "gorfs"."inodes"("st_ino") "fk_target_inode_must_exist" FOREIGN KEY ("st_ino_target") REFERENCES "gorfs"."inodes"("st_ino")Triggers: "a_iud_update_inode" AFTER INSERT OR DELETE OR UPDATE ON "gorfs"."inode_segments" FOR EACH ROW EXECUTE PROCEDURE "gorfs"."tf_inode_segments_update_inodes"() "a_u_update_children" AFTER UPDATE ON "gorfs"."inode_segments" FOR EACH ROW EXECUTE PROCEDURE "gorfs"."tf_inode_segments_update_children"() "b_iu_calculate_columns" BEFORE INSERT OR UPDATE ON "gorfs"."inode_segments" FOR EACH ROW EXECUTE PROCEDURE "gorfs"."tf_inode_segments_calculate_columns"() "ct_valid_data_layouts_only" AFTER INSERT ON "gorfs"."inode_segments" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "gorfs"."tf_inode_segments_valid_data_layouts_only"()
gorfs.nodes:
DBNAME=# \d gorfs.nodes teste5.txt View "gorfs.nodes" Column | Type | Modifiers--------------------+--------------------------------+----------- node_full_path | "gorfs"."absolute_pathname" | parent_inode_id | "gorfs"."ino_t" | inode_id | "gorfs"."ino_t" | relative_path | character varying | raw_mode | bigint | object_type | "gorfs"."mode_t_constant_name" | setuid | boolean | setgid | boolean | sticky | boolean | permissions | bit(9) | links_count | "gorfs"."nlink_t" | owner_uid | "gorfs"."uid_t" | owner_gid | "gorfs"."gid_t" | data_length | "gorfs"."off_t" | last_accessed | "gorfs"."time_t" | last_modified | "gorfs"."time_t" | last_changed | "gorfs"."time_t" | checksum_md5 | "md5_hash" | media_type | "text" | target | "text" | file_data | "bytea" | is_external | boolean | data_length_target | "gorfs"."off_t" |Triggers: "i_iud_action_changes" INSTEAD OF INSERT OR DELETE OR UPDATE ON "gorfs"."nodes" FOR EACH ROW EXECUTE PROCEDURE"gorfs"."tf_nodes_action_changes"()