Search Postgresql Archives

Re: Slow Query - PostgreSQL 9.2

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

 



UPDATED LAST EMAIL


From: smerlo50@xxxxxxxxxxx
To: clavadetscher@xxxxxxxxxxxx; vitaly.burovoy@xxxxxxxxx
CC: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: 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@xxxxxxxxxxx
To: clavadetscher@xxxxxxxxxxxx; vitaly.burovoy@xxxxxxxxx
CC: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: 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@xxxxxxxxxxx
To: clavadetscher@xxxxxxxxxxxx; vitaly.burovoy@xxxxxxxxx
CC: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: 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@xxxxxxxxxxx
To: clavadetscher@xxxxxxxxxxxx; vitaly.burovoy@xxxxxxxxx
CC: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: 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@xxxxxxxxxxx
To: clavadetscher@xxxxxxxxxxxx; vitaly.burovoy@xxxxxxxxx
CC: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: 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"




> From: clavadetscher@xxxxxxxxxxxx
> To: smerlo50@xxxxxxxxxxx; vitaly.burovoy@xxxxxxxxx
> CC: pgsql-general@xxxxxxxxxxxxxx
> Subject: Re: Slow Query - PostgreSQL 9.2
> Date: Mon, 11 Jan 2016 08:33:41 +0100
>
> Hello
>
> > -----Original Message-----
> > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Saulo Merlo
> > Sent: Montag, 11. Januar 2016 08:12
> > To: Vitaly Burovoy <vitaly.burovoy@xxxxxxxxx>
> > Cc: pgsql-general@xxxxxxxxxxxxxx
> > Subject: Re: Slow Query - PostgreSQL 9.2
> >
> > gorgs.inode_segments:
> >
> >
> > -- Table: gorfs.inode_segments
> >
> > -- DROP TABLE gorfs.inode_segments;
> >
> > CREATE TABLE gorfs.inode_segments
> > (
> > st_ino "gorfs"."ino_t" NOT NULL, -- Inode number the segment belongs to. alongside segment_index, it forms
> > the table's primary key to ensure uniqueness per relevant scope
> > segment_index "gorfs"."pathname_component" NOT NULL, -- See st_no's column description for further details.
> > The meaning of this column varies based on the host inode type:...
> > st_ino_target "gorfs"."ino_t", -- Target inode number. Meaningful for directory inode segments (objects in
> > the directory)
> > full_path "gorfs"."absolute_pathname", -- Exploded absolute canonical path for quick lookups. Meaningful
> > only for directory inode segments (objects in the directory)
> > segment_data "bytea", -- Actual data segment. Meaningful only for S_IFLNK and S_IFREG....
> > CONSTRAINT pk_inode_segments PRIMARY KEY ("st_ino", "segment_index"),
> > CONSTRAINT fk_host_inode_must_exist FOREIGN KEY (st_ino)
> > REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
> > ON UPDATE NO ACTION ON DELETE NO ACTION,
> > CONSTRAINT fk_target_inode_must_exist FOREIGN KEY (st_ino_target)
> > REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
> > ON UPDATE NO ACTION ON DELETE NO ACTION,
> > CONSTRAINT uc_no_duplicate_full_paths UNIQUE ("full_path"),
> > CONSTRAINT cc_only_root_can_be_its_own_parent CHECK ("st_ino_target" IS NULL OR "st_ino"::bigint <>
> > "st_ino_target"::bigint OR "st_ino"::bigint = 2)
> > )
> > WITH (
> > OIDS=FALSE
> > );
>
> There is no field st_ctime.
>
> >
> > S_IFSOCK: 0: no data to store, no records here
> > S_IFLNK: 1: contains the link target (see columns comments for details).
> > S_IFREG: 0+: actual data segments, up to 64MB each (see columns comments for details)
> > S_IFBLK: 0: no data to store, no records here
> > S_IFDIR: 0+: one record per object name in the directory
> > S_IFCHR: 0: no data to store, no records here
> > S_IFIFO: 0: no data to store, no records here
> > ';
> > -- Index: gorfs.ix_inode_segments_climb_tree
> >
> > -- DROP INDEX gorfs.ix_inode_segments_climb_tree;
> >
> > CREATE INDEX ix_inode_segments_climb_tree
> > ON gorfs.inode_segments
> > USING btree
> > ("segment_index" COLLATE pg_catalog."default", "st_ino_target");
> >
> > -- Index: gorfs.ix_inode_segments_filter_by_subtree
> >
> > -- DROP INDEX gorfs.ix_inode_segments_filter_by_subtree;
> >
> > CREATE INDEX ix_inode_segments_filter_by_subtree
> > ON gorfs.inode_segments
> > USING btree
> > ("full_path" COLLATE pg_catalog."default" varchar_pattern_ops)
> > WHERE "full_path" IS NOT NULL;
> > COMMENT ON INDEX gorfs.ix_inode_segments_filter_by_subtree
> > IS 'Allows looking for left-anchored paths (either regex or LIKE).
> > WARNING: as of 9.2 the index is not used when the comparison term is a non deterministic function (STABLE or
> > VOLATILE).
> > See http://www.postgresql.org/message-id/5451D6C4.7040308@xxxxxxxx
> > ';
> >
> > -- 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: Slow Query - PostgreSQL 9.2
> > > From: vitaly.burovoy@xxxxxxxxx
> > > To: smerlo50@xxxxxxxxxxx
> > > CC: pgsql-general@xxxxxxxxxxxxxx
> > >
> > > On 1/10/16, Saulo Merlo <smerlo50@xxxxxxxxxxx> 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: Slow Query - PostgreSQL 9.2
> > > >> From: vitaly.burovoy@xxxxxxxxx
> > > >> To: smerlo50@xxxxxxxxxxx
> > > >> CC: pgsql-general@xxxxxxxxxxxxxx
> > > >>
> > > >> On 1/10/16, Saulo Merlo <smerlo50@xxxxxxxxxxx> 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: Slow Query - PostgreSQL 9.2
> > > >> >> From: vitaly.burovoy@xxxxxxxxx
> > > >> >> To: smerlo50@xxxxxxxxxxx
> > > >> >> CC: pgsql-general@xxxxxxxxxxxxxx
> > > >> >>
> > > >> >> On 1/10/16, Vitaly Burovoy <vitaly.burovoy@xxxxxxxxx> wrote:
> > > >> >> > On 1/10/16, Saulo Merlo <smerlo50@xxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
> > > To make changes to your subscription:
> > > http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

[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