Search Postgresql Archives

Re: Slow Query - PostgreSQL 9.2

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux