Slow Query - PostgreSQL 9.2

Поиск
Список
Период
Сортировка
Искать

Slow Query - PostgreSQL 9.2

От:
Saulo Merlo <smerlo50@outlook.com>
Дата:
I've been trying to make this Query faster, but I had no success.

Do you guys have an idea about what else I can do?
I'll post below what I have done.

Thank you.

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 - COLUMN
ALTER TABLE gorfs.inode_segments ADD COLUMN nfs_file_path "text";

nfs_migration_date - COLUMN - HAVE TO CREATE AN INDEX TO IT
ALTER 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
);



Sent from my phone

Re: Slow Query - PostgreSQL 9.2

От:
bricklen <bricklen@gmail.com>
Дата:

On Tue, Jan 12, 2016 at 12:58 AM, Saulo Merlo <smerlo50@outlook.com> wrote:
I've been trying to make this Query faster, but I had no success.

Do you guys have an idea about what else I can do?


It would be helpful to supply the output of "\d gorfs.nodes" and "\d gorfs.inode_segments" so we can see the actual indexes and constraints that exist.
Also, pasting your EXPLAIN plan into http://explain.depesz.com/ and submitting the link in your post is usually easier to read than pasting it into an email.

Try the following partial indexes (they may or may not already exist, the structure of the tables is incomplete)

create index concurrently inode_segments_st_ino_st_ino_target_pidx
on gorfs.inode_segments (st_ino desc, st_ino_target desc)
where nfs_migration_data is null;

-- if there is no index on gorfs.nodes.last_modified, test a partial index:
create index concurrently nodes_last_modified_rel_path_obj_type_pidx
on gorfs.nodes (last_modified desc)
where relative_path = 'main'
and object_type = 'S_IFREG';

vacuum analyze verbose gorfs.nodes;
vacuum analyze verbose gorfs.inode_segments;


EXPLAIN (analyze, buffers)
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 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')
WHERE 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))
LIMIT 100;

Re: Slow Query - PostgreSQL 9.2

От:
Saulo Merlo <smerlo50@outlook.com>
Дата:
Hi Bricklen.. Thank you for your help... Thank you so much...

It would be helpful to supply the output of "\d gorfs.nodes" and "\d gorfs.inode_segments" so we can see the actual indexes and constraints that exist.

Remember that gorfs.nodes is a view.

I've created the INDEX you told me to:
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;


It seems  on the EXPLAIN ANALYZE that it wasn't used!

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"()

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_kam
inski_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_se
gments_valid_data_layouts_only"()


Also, pasting your EXPLAIN plan into http://explain.depesz.com/ and submitting the link in your post is usually easier to read than pasting it into an email.

Ok. I'll do  it.


create index concurrently nodes_last_modified_rel_path_obj_type_pidx
on gorfs.nodes (last_modified desc)
where relative_path = 'main'
and object_type = 'S_IFREG';

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

Re: Slow Query - PostgreSQL 9.2

От:
Saulo Merlo <smerlo50@outlook.com>
Дата:
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



From: smerlo50@outlook.com
To: bricklen@gmail.com
CC: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Slow Query - PostgreSQL 9.2
Date: Tue, 12 Jan 2016 20:28:53 +0000

Hi Bricklen.. Thank you for your help... Thank you so much...

It would be helpful to supply the output of "\d gorfs.nodes" and "\d gorfs.inode_segments" so we can see the actual indexes and constraints that exist.

Remember that gorfs.nodes is a view.

I've created the INDEX you told me to:
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;


It seems  on the EXPLAIN ANALYZE that it wasn't used!

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"()

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_kam
inski_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_se
gments_valid_data_layouts_only"()


Also, pasting your EXPLAIN plan into http://explain.depesz.com/ and submitting the link in your post is usually easier to read than pasting it into an email.

Ok. I'll do  it.


create index concurrently nodes_last_modified_rel_path_obj_type_pidx
on gorfs.nodes (last_modified desc)
where relative_path = 'main'
and object_type = 'S_IFREG';

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";
FAQ