Thread: Slow Query - PostgreSQL 9.2

Slow Query - PostgreSQL 9.2

From

Saulo Merlo

Date:

11 January 2016, 07:31:23

I've got a slow query.. I'd like to make it faster.. Make add an index?
Query:

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

EXPLAIN ANALYZE:

"Limit  (cost=1556.99..1336437.30 rows=100 width=186) (actual time=94987.261..94987.261 rows=0 loops=1)"
"  ->  Nested Loop Left Join  (cost=1556.99..579473097.84 rows=43410 width=186) (actual time=94987.257..94987.257 rows=0 loops=1)"
"        ->  Nested Loop Left Join  (cost=1038.00..483232645.16 rows=43410 width=154) (actual time=94987.255..94987.255 rows=0 loops=1)"
"              ->  Nested Loop  (cost=519.00..409353299.84 rows=43410 width=114) (actual time=94987.252..94987.252 rows=0 loops=1)"
"                    ->  Nested Loop  (cost=519.00..409094090.84 rows=43410 width=114) (actual time=94987.250..94987.250 rows=0 loops=1)"
"                          ->  Nested Loop  (cost=519.00..408681283.16 rows=43410 width=106) (actual time=94987.247..94987.247 rows=0 loops=1)"
"                                ->  Nested Loop  (cost=519.00..407691740.11 rows=64840 width=106) (actual time=94987.244..94987.244 rows=0 loops=1)"
"                                      ->  Nested Loop  (cost=519.00..406213713.19 rows=96848 width=98) (actual time=94987.241..94987.241 rows=0 loops=1)"
"                                            ->  Nested Loop  (cost=519.00..403641904.83 rows=191391 width=106) (actual time=94987.239..94987.239 rows=0 loops=1)"
"                                                  Join Filter: (CASE WHEN ("f"."constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name") THEN (SubPlan 4) ELSE NULL::"bytea" END IS NOT NULL)"
"                                                  ->  Nested Loop  (cost=519.00..349935407.61 rows=287309 width=36) (actual time=94987.236..94987.236 rows=0 loops=1)"
"                                                        Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("f"."bits")::"bit")"
"                                                        ->  Nested Loop  (cost=0.00..349819245.82 rows=287309 width=41) (actual time=94987.233..94987.233 rows=0 loops=1)"
"                                                              ->  Nested Loop  (cost=0.00..343269999.71 rows=429140 width=41) (actual time=94987.231..94987.231 rows=0 loops=1)"
"                                                                    ->  Nested Loop  (cost=0.00..206165095.07 rows=8982354 width=41) (actual time=94987.228..94987.228 rows=0 loops=1)"
"                                                                          ->  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))"
"                                                                                Rows Removed by Filter: 40683998"
"                                                                          ->  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 = ("t"."st_ino")::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)"
"                                                        ->  Materialize  (cost=519.00..519.97 rows=23 width=36) (never executed)"
"                                                              ->  Subquery Scan on "f"  (cost=519.00..519.86 rows=23 width=36) (never executed)"
"                                                                    ->  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.25 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" "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: 94989.208 ms"

What could I do to make it faster? Thank you.

Re: Slow Query - PostgreSQL 9.2

From

Vitaly Burovoy

Date:

11 January 2016, 08:10:33

On 1/10/16, Saulo Merlo <smerlo50@outlook.com> wrote:
> I've got a slow query.. I'd like to make it faster.. Make add an index?
> Query:
> SELECT j.clientid AS client_id,
>        ni.segment_index AS note_id,
>        f.inode_id AS file_id,
>        f.node_full_path AS filename,
>        f.last_changed AS date_created,
>        f.file_data AS main_binary,
>        medium.inode_id AS medium_id,
>        medium.file_data AS medium_binary,
>        thumbnail.inode_id AS thumbnail_id,
>        thumbnail.file_data AS thumbnail_binary
> FROM gorfs.nodes AS f
> INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.inode_id
> INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
> AND mv.segment_index = 'main.with_name'
> INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino
> INNER JOIN gorfs.inode_segments AS fn ON fn.st_ino_target = fi.st_ino
> INNER JOIN gorfs.inode_segments AS ni ON ni.st_ino_target = fn.st_ino
> INNER JOIN public.ja_notes AS n ON n.id = CAST(ni.segment_index AS INTEGER)
> INNER JOIN public.ja_jobs AS j ON j.id = n.jobid
> LEFT JOIN
>   (SELECT f.inode_id,
>           f.file_data,
>           fi.st_ino
>    FROM gorfs.nodes AS f
>    INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.inode_id
>    INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
>    AND mv.segment_index = 'medium.with_name'
>    INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino) AS
> medium ON medium.st_ino = fn.st_ino_target
> LEFT JOIN
>   (SELECT f.inode_id,
>           f.file_data,
>           fi.st_ino
>    FROM gorfs.nodes AS f
>    INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.inode_id
>    INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
>    AND mv.segment_index = 'thumbnail.with_name'
>    INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino) AS
> thumbnail ON thumbnail.st_ino = fn.st_ino_target
> WHERE f.file_data IS NOT NULL
>   AND ((transaction_timestamp() AT TIME ZONE 'UTC') > (f.last_changed + '24
> months' :: INTERVAL)) LIMIT 100;

> <<overquoting>>
> "Total runtime: 94989.208 ms"What could I do to make it faster? Thank you.

At least you can add an index:
CREATE INDEX ON gorfs.nodes(last_changed)

and rewrite part of WHERE clause to:
(f.last_changed < (transaction_timestamp() AT TIME ZONE 'UTC' - '24
months'::INTERVAL))

It allows to decrease the slowest part of your query (sequence
scanning of a table, all 13.5M rows):
> -> Seq Scan on "inodes" "t"  (cost=0.00..1411147.24 rows=13416537 width=29) (actual time=94987.224..94987.224 rows=0
loops=1)
>      Filter: ("timezone"('UTC'::"text", "transaction_timestamp"()) > (("st_ctime")::timestamp without time zone + '2
years'::interval))

compare that time to the one in the topmost row of EXPLAIN:
> Limit  (cost=1556.99..1336437.30 rows=100 width=186) (actual time=94987.261..94987.261 rows=0 loops=1)

--
Best regards,
Vitaly Burovoy

Re: Slow Query - PostgreSQL 9.2

From

Vitaly Burovoy

Date:

11 January 2016, 08:23:07

On 1/10/16, Vitaly Burovoy <vitaly.burovoy@gmail.com> wrote:
> On 1/10/16, Saulo Merlo <smerlo50@outlook.com> wrote:
>> I've got a slow query.. I'd like to make it faster.. Make add an index?
>> Query:
>> SELECT
>>   <<overquoting>>
>> FROM gorfs.nodes AS f
>>   <<overquoting>>
>> WHERE f.file_data IS NOT NULL
>>   AND ((transaction_timestamp() AT TIME ZONE 'UTC') > (f.last_changed +
>> '24
>> months' :: INTERVAL)) LIMIT 100;
>
>> <<overquoting>>
>> "Total runtime: 94989.208 ms"What could I do to make it faster? Thank
>> you.
>
> At least you can add an index:
> CREATE INDEX ON gorfs.nodes(last_changed)
>
> and rewrite part of WHERE clause to:
> (f.last_changed < (transaction_timestamp() AT TIME ZONE 'UTC' - '24
> months'::INTERVAL))
>
> It allows to decrease the slowest part of your query (sequence
> scanning of a table, all 13.5M rows):
>> -> Seq Scan on "inodes" "t"  (cost=0.00..1411147.24 rows=13416537
>> width=29) (actual time=94987.224..94987.224 rows=0 loops=1)
>>      Filter: ("timezone"('UTC'::"text", "transaction_timestamp"()) >
>> (("st_ctime")::timestamp without time zone + '2 years'::interval))
>
> compare that time to the one in the topmost row of EXPLAIN:
>> Limit  (cost=1556.99..1336437.30 rows=100 width=186) (actual
>> time=94987.261..94987.261 rows=0 loops=1)

Hmm. It seems that gorfs.nodes is a view.
So creating index should be something like (I have no idea that schema
name for it):
CREATE INDEX ON _schema_name_.inodes(st_ctime)

--
Best regards,
Vitaly Burovoy

Re: Slow Query - PostgreSQL 9.2

From

Saulo Merlo

Date:

11 January 2016, 09:27:28

Hi Vitaly,

Yep... gorfs.nodes is a view.

And the schema is: gorfs.inode_segments

So...

CREATE INDEX index_name ON gorfs.inode_segments(st_ctime)

Is that correct? It would be "st_ctime"?

I've rewriten the query as well. Thank you for that!

Thank you

Lucas

> Date: Sun, 10 Jan 2016 21:23:01 -0800
> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
> From: vitaly.burovoy@gmail.com
> To: smerlo50@outlook.com
> CC: pgsql-general@postgresql.org
>
> On 1/10/16, Vitaly Burovoy <vitaly.burovoy@gmail.com> wrote:
> > On 1/10/16, Saulo Merlo <smerlo50@outlook.com> wrote:
> >> I've got a slow query.. I'd like to make it faster.. Make add an index?
> >> Query:
> >> SELECT
> >> <<overquoting>>
> >> FROM gorfs.nodes AS f
> >> <<overquoting>>
> >> WHERE f.file_data IS NOT NULL
> >> AND ((transaction_timestamp() AT TIME ZONE 'UTC') > (f.last_changed +
> >> '24
> >> months' :: INTERVAL)) LIMIT 100;
> >
> >> <<overquoting>>
> >> "Total runtime: 94989.208 ms"What could I do to make it faster? Thank
> >> you.
> >
> > At least you can add an index:
> > CREATE INDEX ON gorfs.nodes(last_changed)
> >
> > and rewrite part of WHERE clause to:
> > (f.last_changed < (transaction_timestamp() AT TIME ZONE 'UTC' - '24
> > months'::INTERVAL))
> >
> > It allows to decrease the slowest part of your query (sequence
> > scanning of a table, all 13.5M rows):
> >> -> Seq Scan on "inodes" "t" (cost=0.00..1411147.24 rows=13416537
> >> width=29) (actual time=94987.224..94987.224 rows=0 loops=1)
> >> Filter: ("timezone"('UTC'::"text", "transaction_timestamp"()) >
> >> (("st_ctime")::timestamp without time zone + '2 years'::interval))
> >
> > compare that time to the one in the topmost row of EXPLAIN:
> >> Limit (cost=1556.99..1336437.30 rows=100 width=186) (actual
> >> time=94987.261..94987.261 rows=0 loops=1)
>
> Hmm. It seems that gorfs.nodes is a view.
> So creating index should be something like (I have no idea that schema
> name for it):
> CREATE INDEX ON _schema_name_.inodes(st_ctime)
>
> --
> Best regards,
> Vitaly Burovoy
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Re: Slow Query - PostgreSQL 9.2

From

Vitaly Burovoy

Date:

11 January 2016, 09:43:25

On 1/10/16, Saulo Merlo <smerlo50@outlook.com> wrote:
> Hi Vitaly,
>
> Yep... gorfs.nodes is a view.
> And the schema is: gorfs.inode_segments
> So... CREATE INDEX index_name ON gorfs.inode_segments(st_ctime)
> Is that correct? It would be "st_ctime"?
If "inodes" is an alias for "gorfs.inode_segments" in your view, yes,
the above DDL is OK. According to EXPLAIN's "Filter" row the column
involving in comparison is st_ctime.

Hint: you can create the index without blocking table using "CREATE
INDEX CONCURRENTLY":
http://www.postgresql.org/docs/9.2/static/sql-createindex.html

> I've rewriten the query as well. Thank you for that!
>
> Thank you
> Lucas

>> Date: Sun, 10 Jan 2016 21:23:01 -0800
>> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
>> From: vitaly.burovoy@gmail.com
>> To: smerlo50@outlook.com
>> CC: pgsql-general@postgresql.org
>>
>> On 1/10/16, Vitaly Burovoy <vitaly.burovoy@gmail.com> wrote:
>> > On 1/10/16, Saulo Merlo <smerlo50@outlook.com> wrote:
>> >> I've got a slow query.. I'd like to make it faster.. Make add an
>> >> index?
>> >> Query:
>> >> SELECT
>> >>   <<overquoting>>
>> >> FROM gorfs.nodes AS f
>> >>   <<overquoting>>
>> >> WHERE f.file_data IS NOT NULL
>> >>   AND ((transaction_timestamp() AT TIME ZONE 'UTC') > (f.last_changed
>> >> +
>> >> '24
>> >> months' :: INTERVAL)) LIMIT 100;
>> >
>> >> <<overquoting>>
>> >> "Total runtime: 94989.208 ms"What could I do to make it faster? Thank
>> >> you.
>> >
>> > At least you can add an index:
>> > CREATE INDEX ON gorfs.nodes(last_changed)
>> >
>> > and rewrite part of WHERE clause to:
>> > (f.last_changed < (transaction_timestamp() AT TIME ZONE 'UTC' - '24
>> > months'::INTERVAL))
>> >
>> > It allows to decrease the slowest part of your query (sequence
>> > scanning of a table, all 13.5M rows):
>> >> -> Seq Scan on "inodes" "t"  (cost=0.00..1411147.24 rows=13416537
>> >> width=29) (actual time=94987.224..94987.224 rows=0 loops=1)
>> >>      Filter: ("timezone"('UTC'::"text", "transaction_timestamp"()) >
>> >> (("st_ctime")::timestamp without time zone + '2 years'::interval))
>> >
>> > compare that time to the one in the topmost row of EXPLAIN:
>> >> Limit  (cost=1556.99..1336437.30 rows=100 width=186) (actual
>> >> time=94987.261..94987.261 rows=0 loops=1)
>>
>> Hmm. It seems that gorfs.nodes is a view.
>> So creating index should be something like (I have no idea that schema
>> name for it):
>> CREATE INDEX ON _schema_name_.inodes(st_ctime)

--
Best regards,
Vitaly Burovoy

Re: Slow Query - PostgreSQL 9.2

From

Saulo Merlo

Date:

11 January 2016, 09:52:21

CREATE INDEX CONCURRENTLY index_name ON gorfs.inode_segments(st_ctime);
ERROR: column "st_ctime" does not exist

Look the error I've got

Lucas

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

Re: Slow Query - PostgreSQL 9.2

From

Vitaly Burovoy

Date:

11 January 2016, 10:04:29

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

Please, post a definition of a table and a view (and all intermediate
views if any).

Via psql it can be done via:
\d gorfs.inode_segments
\d+ gorfs.nodes

--
Best regards,
Vitaly Burovoy

Re: Slow Query - PostgreSQL 9.2

From

Saulo Merlo

Date:

11 January 2016, 10:11:51

gorgs.inode_segments:

-- Table: gorfs.inode_segments

-- DROP TABLE gorfs.inode_segments;

CREATE TABLE gorfs.inode_segments
(
st_ino "gorfs"."ino_t" NOT NULL, -- Inode number the segment belongs to. alongside segment_index, it forms the table's primary key to ensure uniqueness per relevant scope
segment_index "gorfs"."pathname_component" NOT NULL, -- See st_no's column description for further details. The meaning of this column varies based on the host inode type:...
st_ino_target "gorfs"."ino_t", -- Target inode number. Meaningful for directory inode segments (objects in the directory)
full_path "gorfs"."absolute_pathname", -- Exploded absolute canonical path for quick lookups. Meaningful only for directory inode segments (objects in the directory)
segment_data "bytea", -- Actual data segment. Meaningful only for S_IFLNK and S_IFREG....
CONSTRAINT pk_inode_segments PRIMARY KEY ("st_ino", "segment_index"),
CONSTRAINT fk_host_inode_must_exist FOREIGN KEY (st_ino)
REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_target_inode_must_exist FOREIGN KEY (st_ino_target)
REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT uc_no_duplicate_full_paths UNIQUE ("full_path"),
CONSTRAINT cc_only_root_can_be_its_own_parent CHECK ("st_ino_target" IS NULL OR "st_ino"::bigint <> "st_ino_target"::bigint OR "st_ino"::bigint = 2)
)
WITH (
OIDS=FALSE
);

S_IFSOCK: 0: no data to store, no records here
S_IFLNK: 1: contains the link target (see columns comments for details).
S_IFREG: 0+: actual data segments, up to 64MB each (see columns comments for details)
S_IFBLK: 0: no data to store, no records here
S_IFDIR: 0+: one record per object name in the directory
S_IFCHR: 0: no data to store, no records here
S_IFIFO: 0: no data to store, no records here
';
-- Index: gorfs.ix_inode_segments_climb_tree

-- DROP INDEX gorfs.ix_inode_segments_climb_tree;

CREATE INDEX ix_inode_segments_climb_tree
ON gorfs.inode_segments
USING btree
("segment_index" COLLATE pg_catalog."default", "st_ino_target");

-- Index: gorfs.ix_inode_segments_filter_by_subtree

-- DROP INDEX gorfs.ix_inode_segments_filter_by_subtree;

CREATE INDEX ix_inode_segments_filter_by_subtree
ON gorfs.inode_segments
USING btree
("full_path" COLLATE pg_catalog."default" varchar_pattern_ops)
WHERE "full_path" IS NOT NULL;
COMMENT ON INDEX gorfs.ix_inode_segments_filter_by_subtree
IS 'Allows looking for left-anchored paths (either regex or LIKE).
WARNING: as of 9.2 the index is not used when the comparison term is a non deterministic function (STABLE or VOLATILE).
See http://www.postgresql.org/message-id/5451D6C4.7040308@vuole.me
';

-- Index: gorfs.ix_inode_segments_full_path_resolution

-- DROP INDEX gorfs.ix_inode_segments_full_path_resolution;

CREATE INDEX ix_inode_segments_full_path_resolution
ON gorfs.inode_segments
USING btree
("st_ino", "full_path" COLLATE pg_catalog."default");

-- Index: gorfs.ix_inode_segments_gsdi_pk

-- DROP INDEX gorfs.ix_inode_segments_gsdi_pk;

CREATE INDEX ix_inode_segments_gsdi_pk
ON gorfs.inode_segments
USING btree
(("st_ino"::"text") COLLATE pg_catalog."default", ("segment_index"::"text") COLLATE pg_catalog."default");

-- Index: gorfs.ix_inode_segments_ja_files_lookup

-- DROP INDEX gorfs.ix_inode_segments_ja_files_lookup;

CREATE INDEX ix_inode_segments_ja_files_lookup
ON gorfs.inode_segments
USING btree
((
CASE
WHEN "full_path"::"text" ~ '/[^/]*\.[^/]*$'::"text" THEN "upper"("regexp_replace"("full_path"::"text", '.*\.'::"text", ''::"text", 'g'::"text"))
ELSE NULL::"text"
END) COLLATE pg_catalog."default")
WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text");

-- Index: gorfs.ix_inode_segments_notes_clientids

-- DROP INDEX gorfs.ix_inode_segments_notes_clientids;

CREATE INDEX ix_inode_segments_notes_clientids
ON gorfs.inode_segments
USING btree
(("split_part"("full_path"::"text", '/'::"text", 4)::integer))
WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text");

-- Index: gorfs.ix_inode_segments_notes_fileids

-- DROP INDEX gorfs.ix_inode_segments_notes_fileids;

CREATE INDEX ix_inode_segments_notes_fileids
ON gorfs.inode_segments
USING btree
(("split_part"("full_path"::"text", '/'::"text", 8)::integer))
WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text");

-- Index: gorfs.ix_inode_segments_notes_noteids

-- DROP INDEX gorfs.ix_inode_segments_notes_noteids;

CREATE INDEX ix_inode_segments_notes_noteids
ON gorfs.inode_segments
USING btree
((NULLIF("split_part"("full_path"::"text", '/'::"text", 6), 'unassigned'::"text")::integer))
WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text");

-- Index: gorfs.ix_inode_segments_segment_indexes

-- DROP INDEX gorfs.ix_inode_segments_segment_indexes;

CREATE INDEX ix_inode_segments_segment_indexes
ON gorfs.inode_segments
USING btree
("segment_index" COLLATE pg_catalog."default");

-- Index: gorfs.ix_inode_segments_st_ino_targets

-- DROP INDEX gorfs.ix_inode_segments_st_ino_targets;

CREATE INDEX ix_inode_segments_st_ino_targets
ON gorfs.inode_segments
USING btree
("st_ino_target");

-- Index: gorfs.ix_inode_segments_st_inos

-- DROP INDEX gorfs.ix_inode_segments_st_inos;

CREATE INDEX ix_inode_segments_st_inos
ON gorfs.inode_segments
USING btree
("st_ino");

-- Trigger: a_iud_update_inode on gorfs.inode_segments

-- DROP TRIGGER a_iud_update_inode ON gorfs.inode_segments;

CREATE TRIGGER a_iud_update_inode
AFTER INSERT OR UPDATE OR DELETE
ON gorfs.inode_segments
FOR EACH ROW
EXECUTE PROCEDURE gorfs.tf_inode_segments_update_inodes();
COMMENT ON TRIGGER a_iud_update_inode ON gorfs.inode_segments IS 'See invoked function';

-- Trigger: a_u_update_children on gorfs.inode_segments

-- DROP TRIGGER a_u_update_children ON gorfs.inode_segments;

CREATE TRIGGER a_u_update_children
AFTER UPDATE
ON gorfs.inode_segments
FOR EACH ROW
EXECUTE PROCEDURE gorfs.tf_inode_segments_update_children();

-- Trigger: b_iu_calculate_columns on gorfs.inode_segments

-- DROP TRIGGER b_iu_calculate_columns ON gorfs.inode_segments;

CREATE TRIGGER b_iu_calculate_columns
BEFORE INSERT OR UPDATE
ON gorfs.inode_segments
FOR EACH ROW
EXECUTE PROCEDURE gorfs.tf_inode_segments_calculate_columns();
COMMENT ON TRIGGER b_iu_calculate_columns ON gorfs.inode_segments IS 'See invoked function';

-- Trigger: ct_valid_data_layouts_only on gorfs.inode_segments

-- DROP TRIGGER ct_valid_data_layouts_only ON gorfs.inode_segments;

CREATE CONSTRAINT TRIGGER ct_valid_data_layouts_only
AFTER INSERT
ON gorfs.inode_segments
FOR EACH ROW
EXECUTE PROCEDURE gorfs.tf_inode_segments_valid_data_layouts_only();
COMMENT ON TRIGGER ct_valid_data_layouts_only ON gorfs.inode_segments IS 'See invoked function';

gorfs.noes:

-- View: gorfs.nodes

-- DROP VIEW gorfs.nodes;

CREATE OR REPLACE VIEW gorfs.nodes AS
SELECT "p"."full_path" AS "node_full_path", "h"."st_ino" AS "parent_inode_id",
"t"."st_ino" AS "inode_id",
CASE
WHEN "p"."st_ino_target"::bigint = 2 THEN NULL::character varying
ELSE "p"."segment_index"::character varying
END AS "relative_path",
"t"."st_mode"::bigint AS "raw_mode", "f"."constant_name" AS "object_type",
("gorfs"."mode_t_bits"('S_ISUID'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode"::"bit")::integer <> 0 AS "setuid",
("gorfs"."mode_t_bits"('S_ISGID'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode"::"bit")::integer <> 0 AS "setgid",
("gorfs"."mode_t_bits"('S_ISVTX'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode"::"bit")::integer <> 0 AS "sticky",
"right"("concat"((("gorfs"."mode_t_bits"('S_IRWXU'::"gorfs"."mode_t_constant_name")::"bit" | "gorfs"."mode_t_bits"('S_IRWXG'::"gorfs"."mode_t_constant_name")::"bit") | "gorfs"."mode_t_bits"('S_IRWXO'::"gorfs"."mode_t_constant_name")::"bit") & "t"."st_mode"::"bit"), 9)::bit(9) AS "permissions",
"t"."st_nlink" AS "links_count", "t"."st_uid" AS "owner_uid",
"t"."st_gid" AS "owner_gid", "t"."st_size" AS "data_length",
"t"."st_atime" AS "last_accessed", "t"."st_mtime" AS "last_modified",
"t"."st_ctime" AS "last_changed", "t"."checksum_md5",
("mst"."media_type" || '/'::"text") || "mst"."subtype_string"::"text" AS "media_type",
CASE
WHEN "f"."constant_name" = 'S_IFLNK'::"gorfs"."mode_t_constant_name" THEN ( SELECT "convert_from"("ls"."segment_data", 'UTF8'::"name") AS "convert_from"
FROM "gorfs"."inode_segments" "ls"
WHERE "ls"."st_ino"::bigint = "p"."st_ino_target"::bigint)
ELSE NULL::"text"
END AS "target",
CASE
WHEN "f"."constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name" THEN ( SELECT "string_agg"("fs"."segment_data", ''::"bytea" ORDER BY "fs"."segment_index") AS "string_agg"
FROM "gorfs"."inode_segments" "fs"
WHERE "fs"."st_ino"::bigint = "p"."st_ino_target"::bigint)
ELSE NULL::"bytea"
END AS "file_data",
"t"."external_size" IS NOT NULL AS "is_external",
"t"."external_size" AS "data_length_target"
FROM "gorfs"."inode_segments" "p"
JOIN "gorfs"."dir_inodes" "h" ON "h"."st_ino"::bigint = "p"."st_ino"::bigint
JOIN "gorfs"."inodes" "t" ON "t"."st_ino"::bigint = "p"."st_ino_target"::bigint
JOIN "gorfs"."mode_t_flags"() "f"("constant_name", "description", "bits", "bits_octal", "bits_hex", "bits_decimal") ON "f"."bits"::"bit" = ("gorfs"."mode_t_bits"('S_IFMT'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode"::"bit")
LEFT JOIN "media_subtypes" "mst" ON "mst"."media_subtype_id" = "t"."media_subtype_id";

-- Trigger: i_iud_action_changes on gorfs.nodes

-- DROP TRIGGER i_iud_action_changes ON gorfs.nodes;

CREATE TRIGGER i_iud_action_changes
INSTEAD OF INSERT OR UPDATE OR DELETE
ON gorfs.nodes
FOR EACH ROW
EXECUTE PROCEDURE gorfs.tf_nodes_action_changes();

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

Re: Slow Query - PostgreSQL 9.2

From

Vitaly Burovoy

Date:

11 January 2016, 10:33:50

On 1/10/16, Saulo Merlo <smerlo50@outlook.com> wrote:
> gorgs.inode_segments:
> <<overquoting>>
>
> gorfs.nodes:
> -- View: gorfs.nodes
> -- DROP VIEW gorfs.nodes;
> CREATE OR REPLACE VIEW gorfs.nodes AS
>  SELECT
> <<overquoting>>
>     "t"."st_ctime" AS "last_changed", ...
> <<overquoting>>
>    FROM "gorfs"."inode_segments" "p"
>    JOIN "gorfs"."dir_inodes" "h" ON "h"."st_ino"::bigint = "p"."st_ino"::bigint
>    JOIN "gorfs"."inodes" "t" ON "t"."st_ino"::bigint = "p"."st_ino_target"::bigint
>    JOIN "gorfs"."mode_t_flags"() "f"(...) ON ...
>    LEFT JOIN "media_subtypes" "mst" ON "mst"."media_subtype_id" = "t"."media_subtype_id";
> <<overquoting>>

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

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

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

--
Best regards,
Vitaly Burovoy

Re: Slow Query - PostgreSQL 9.2

From

"Charles Clavadetscher"

Date:

11 January 2016, 10:34:14

Hello

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Saulo Merlo
> Sent: Montag, 11. Januar 2016 08:12
> To: Vitaly Burovoy <vitaly.burovoy@gmail.com>
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
>
> gorgs.inode_segments:
>
>
>     -- Table: gorfs.inode_segments
>
>     -- DROP TABLE gorfs.inode_segments;
>
>     CREATE TABLE gorfs.inode_segments
>     (
>       st_ino "gorfs"."ino_t" NOT NULL, -- Inode number the segment belongs to. alongside segment_index, it forms
> the table's primary key to ensure uniqueness per relevant scope
>       segment_index "gorfs"."pathname_component" NOT NULL, -- See st_no's column description for further details.
> The meaning of this column varies based on the host inode type:...
>       st_ino_target "gorfs"."ino_t", -- Target inode number. Meaningful for directory inode segments (objects in
> the directory)
>       full_path "gorfs"."absolute_pathname", -- Exploded absolute canonical path for quick lookups. Meaningful
> only for directory inode segments (objects in the directory)
>       segment_data "bytea", -- Actual data segment. Meaningful only for S_IFLNK and S_IFREG....
>       CONSTRAINT pk_inode_segments PRIMARY KEY ("st_ino", "segment_index"),
>       CONSTRAINT fk_host_inode_must_exist FOREIGN KEY (st_ino)
>           REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
>           ON UPDATE NO ACTION ON DELETE NO ACTION,
>       CONSTRAINT fk_target_inode_must_exist FOREIGN KEY (st_ino_target)
>           REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
>           ON UPDATE NO ACTION ON DELETE NO ACTION,
>       CONSTRAINT uc_no_duplicate_full_paths UNIQUE ("full_path"),
>       CONSTRAINT cc_only_root_can_be_its_own_parent CHECK ("st_ino_target" IS NULL OR "st_ino"::bigint <>
> "st_ino_target"::bigint OR "st_ino"::bigint = 2)
>     )
>     WITH (
>       OIDS=FALSE
>     );

There is no field st_ctime.

>
>     S_IFSOCK:   0:   no data to store, no records here
>     S_IFLNK:    1:   contains the link target (see columns comments for details).
>     S_IFREG:    0+:  actual data segments, up to 64MB each  (see columns comments for details)
>     S_IFBLK:    0:   no data to store, no records here
>     S_IFDIR:    0+:  one record per object name in the directory
>     S_IFCHR:    0:   no data to store, no records here
>     S_IFIFO:    0:   no data to store, no records here
>     ';
>     -- Index: gorfs.ix_inode_segments_climb_tree
>
>     -- DROP INDEX gorfs.ix_inode_segments_climb_tree;
>
>     CREATE INDEX ix_inode_segments_climb_tree
>       ON gorfs.inode_segments
>       USING btree
>       ("segment_index" COLLATE pg_catalog."default", "st_ino_target");
>
>     -- Index: gorfs.ix_inode_segments_filter_by_subtree
>
>     -- DROP INDEX gorfs.ix_inode_segments_filter_by_subtree;
>
>     CREATE INDEX ix_inode_segments_filter_by_subtree
>       ON gorfs.inode_segments
>       USING btree
>       ("full_path" COLLATE pg_catalog."default" varchar_pattern_ops)
>       WHERE "full_path" IS NOT NULL;
>     COMMENT ON INDEX gorfs.ix_inode_segments_filter_by_subtree
>       IS 'Allows looking for left-anchored paths (either regex or LIKE).
>     WARNING: as of 9.2 the index is not used when the comparison term is a non deterministic function (STABLE or
> VOLATILE).
>     See http://www.postgresql.org/message-id/5451D6C4.7040308@vuole.me
>     ';
>
>     -- Index: gorfs.ix_inode_segments_full_path_resolution
>
>     -- DROP INDEX gorfs.ix_inode_segments_full_path_resolution;
>
>     CREATE INDEX ix_inode_segments_full_path_resolution
>       ON gorfs.inode_segments
>       USING btree
>       ("st_ino", "full_path" COLLATE pg_catalog."default");
>
>     -- Index: gorfs.ix_inode_segments_gsdi_pk
>
>     -- DROP INDEX gorfs.ix_inode_segments_gsdi_pk;
>
>     CREATE INDEX ix_inode_segments_gsdi_pk
>       ON gorfs.inode_segments
>       USING btree
>       (("st_ino"::"text") COLLATE pg_catalog."default", ("segment_index"::"text") COLLATE pg_catalog."default");
>
>     -- Index: gorfs.ix_inode_segments_ja_files_lookup
>
>     -- DROP INDEX gorfs.ix_inode_segments_ja_files_lookup;
>
>     CREATE INDEX ix_inode_segments_ja_files_lookup
>       ON gorfs.inode_segments
>       USING btree
>       ((
>     CASE
>         WHEN "full_path"::"text" ~ '/[^/]*\.[^/]*$'::"text" THEN "upper"("regexp_replace"("full_path"::"text",
> '.*\.'::"text", ''::"text", 'g'::"text"))
>         ELSE NULL::"text"
>     END) COLLATE pg_catalog."default")
>       WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text");
>
>     -- Index: gorfs.ix_inode_segments_notes_clientids
>
>     -- DROP INDEX gorfs.ix_inode_segments_notes_clientids;
>
>     CREATE INDEX ix_inode_segments_notes_clientids
>       ON gorfs.inode_segments
>       USING btree
>       (("split_part"("full_path"::"text", '/'::"text", 4)::integer))
>       WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text");
>
>     -- Index: gorfs.ix_inode_segments_notes_fileids
>
>     -- DROP INDEX gorfs.ix_inode_segments_notes_fileids;
>
>     CREATE INDEX ix_inode_segments_notes_fileids
>       ON gorfs.inode_segments
>       USING btree
>       (("split_part"("full_path"::"text", '/'::"text", 8)::integer))
>       WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text");
>
>     -- Index: gorfs.ix_inode_segments_notes_noteids
>
>     -- DROP INDEX gorfs.ix_inode_segments_notes_noteids;
>
>     CREATE INDEX ix_inode_segments_notes_noteids
>       ON gorfs.inode_segments
>       USING btree
>       ((NULLIF("split_part"("full_path"::"text", '/'::"text", 6), 'unassigned'::"text")::integer))
>       WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text");
>
>     -- Index: gorfs.ix_inode_segments_segment_indexes
>
>     -- DROP INDEX gorfs.ix_inode_segments_segment_indexes;
>
>     CREATE INDEX ix_inode_segments_segment_indexes
>       ON gorfs.inode_segments
>       USING btree
>       ("segment_index" COLLATE pg_catalog."default");
>
>     -- Index: gorfs.ix_inode_segments_st_ino_targets
>
>     -- DROP INDEX gorfs.ix_inode_segments_st_ino_targets;
>
>     CREATE INDEX ix_inode_segments_st_ino_targets
>       ON gorfs.inode_segments
>       USING btree
>       ("st_ino_target");
>
>     -- Index: gorfs.ix_inode_segments_st_inos
>
>     -- DROP INDEX gorfs.ix_inode_segments_st_inos;
>
>     CREATE INDEX ix_inode_segments_st_inos
>       ON gorfs.inode_segments
>       USING btree
>       ("st_ino");
>
>
>     -- Trigger: a_iud_update_inode on gorfs.inode_segments
>
>     -- DROP TRIGGER a_iud_update_inode ON gorfs.inode_segments;
>
>     CREATE TRIGGER a_iud_update_inode
>       AFTER INSERT OR UPDATE OR DELETE
>       ON gorfs.inode_segments
>       FOR EACH ROW
>       EXECUTE PROCEDURE gorfs.tf_inode_segments_update_inodes();
>     COMMENT ON TRIGGER a_iud_update_inode ON gorfs.inode_segments IS 'See invoked function';
>
>     -- Trigger: a_u_update_children on gorfs.inode_segments
>
>     -- DROP TRIGGER a_u_update_children ON gorfs.inode_segments;
>
>     CREATE TRIGGER a_u_update_children
>       AFTER UPDATE
>       ON gorfs.inode_segments
>       FOR EACH ROW
>       EXECUTE PROCEDURE gorfs.tf_inode_segments_update_children();
>
>     -- Trigger: b_iu_calculate_columns on gorfs.inode_segments
>
>     -- DROP TRIGGER b_iu_calculate_columns ON gorfs.inode_segments;
>
>     CREATE TRIGGER b_iu_calculate_columns
>       BEFORE INSERT OR UPDATE
>       ON gorfs.inode_segments
>       FOR EACH ROW
>       EXECUTE PROCEDURE gorfs.tf_inode_segments_calculate_columns();
>     COMMENT ON TRIGGER b_iu_calculate_columns ON gorfs.inode_segments IS 'See invoked function';
>
>     -- Trigger: ct_valid_data_layouts_only on gorfs.inode_segments
>
>     -- DROP TRIGGER ct_valid_data_layouts_only ON gorfs.inode_segments;
>
>     CREATE CONSTRAINT TRIGGER ct_valid_data_layouts_only
>       AFTER INSERT
>       ON gorfs.inode_segments
>       FOR EACH ROW
>       EXECUTE PROCEDURE gorfs.tf_inode_segments_valid_data_layouts_only();
>     COMMENT ON TRIGGER ct_valid_data_layouts_only ON gorfs.inode_segments IS 'See invoked function';
>
>
>
> gorfs.noes:
>
>
>     -- View: gorfs.nodes
>
>     -- DROP VIEW gorfs.nodes;
>
>     CREATE OR REPLACE VIEW gorfs.nodes AS
>      SELECT "p"."full_path" AS "node_full_path", "h"."st_ino" AS "parent_inode_id",
>         "t"."st_ino" AS "inode_id",
>             CASE
>                 WHEN "p"."st_ino_target"::bigint = 2 THEN NULL::character varying
>                 ELSE "p"."segment_index"::character varying
>             END AS "relative_path",
>         "t"."st_mode"::bigint AS "raw_mode", "f"."constant_name" AS "object_type",
>         ("gorfs"."mode_t_bits"('S_ISUID'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode"::"bit")::integer
> <> 0 AS "setuid",
>         ("gorfs"."mode_t_bits"('S_ISGID'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode"::"bit")::integer
> <> 0 AS "setgid",
>         ("gorfs"."mode_t_bits"('S_ISVTX'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode"::"bit")::integer
> <> 0 AS "sticky",
>         "right"("concat"((("gorfs"."mode_t_bits"('S_IRWXU'::"gorfs"."mode_t_constant_name")::"bit" |
> "gorfs"."mode_t_bits"('S_IRWXG'::"gorfs"."mode_t_constant_name")::"bit") |
> "gorfs"."mode_t_bits"('S_IRWXO'::"gorfs"."mode_t_constant_name")::"bit") & "t"."st_mode"::"bit"), 9)::bit(9) AS
> "permissions",
>         "t"."st_nlink" AS "links_count", "t"."st_uid" AS "owner_uid",
>         "t"."st_gid" AS "owner_gid", "t"."st_size" AS "data_length",
>         "t"."st_atime" AS "last_accessed", "t"."st_mtime" AS "last_modified",
>         "t"."st_ctime" AS "last_changed", "t"."checksum_md5",
>         ("mst"."media_type" || '/'::"text") || "mst"."subtype_string"::"text" AS "media_type",
>             CASE
>                 WHEN "f"."constant_name" = 'S_IFLNK'::"gorfs"."mode_t_constant_name" THEN ( SELECT
> "convert_from"("ls"."segment_data", 'UTF8'::"name") AS "convert_from"
>                    FROM "gorfs"."inode_segments" "ls"
>                   WHERE "ls"."st_ino"::bigint = "p"."st_ino_target"::bigint)
>                 ELSE NULL::"text"
>             END AS "target",
>             CASE
>                 WHEN "f"."constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name" THEN ( SELECT
> "string_agg"("fs"."segment_data", ''::"bytea" ORDER BY "fs"."segment_index") AS "string_agg"
>                    FROM "gorfs"."inode_segments" "fs"
>                   WHERE "fs"."st_ino"::bigint = "p"."st_ino_target"::bigint)
>                 ELSE NULL::"bytea"
>             END AS "file_data",
>         "t"."external_size" IS NOT NULL AS "is_external",
>         "t"."external_size" AS "data_length_target"
>        FROM "gorfs"."inode_segments" "p"
>        JOIN "gorfs"."dir_inodes" "h" ON "h"."st_ino"::bigint = "p"."st_ino"::bigint
>        JOIN "gorfs"."inodes" "t" ON "t"."st_ino"::bigint = "p"."st_ino_target"::bigint
>        JOIN "gorfs"."mode_t_flags"() "f"("constant_name", "description", "bits", "bits_octal", "bits_hex",
> "bits_decimal") ON "f"."bits"::"bit" = ("gorfs"."mode_t_bits"('S_IFMT'::"gorfs"."mode_t_constant_name")::"bit" &
> "t"."st_mode"::"bit")
>        LEFT JOIN "media_subtypes" "mst" ON "mst"."media_subtype_id" = "t"."media_subtype_id";
>
>     -- Trigger: i_iud_action_changes on gorfs.nodes
>
>     -- DROP TRIGGER i_iud_action_changes ON gorfs.nodes;
>
>     CREATE TRIGGER i_iud_action_changes
>       INSTEAD OF INSERT OR UPDATE OR DELETE
>       ON gorfs.nodes
>       FOR EACH ROW
>       EXECUTE PROCEDURE gorfs.tf_nodes_action_changes();

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

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

Re: Slow Query - PostgreSQL 9.2

From

Saulo Merlo

Date:

11 January 2016, 10:48:29

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@swisspug.org
> To: smerlo50@outlook.com; vitaly.burovoy@gmail.com
> CC: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
> Date: Mon, 11 Jan 2016 08:33:41 +0100
>
> Hello
>
> > -----Original Message-----
> > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Saulo Merlo
> > Sent: Montag, 11. Januar 2016 08:12
> > To: Vitaly Burovoy <vitaly.burovoy@gmail.com>
> > Cc: pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
> >
> > gorgs.inode_segments:
> >
> >
> > -- Table: gorfs.inode_segments
> >
> > -- DROP TABLE gorfs.inode_segments;
> >
> > CREATE TABLE gorfs.inode_segments
> > (
> > st_ino "gorfs"."ino_t" NOT NULL, -- Inode number the segment belongs to. alongside segment_index, it forms
> > the table's primary key to ensure uniqueness per relevant scope
> > segment_index "gorfs"."pathname_component" NOT NULL, -- See st_no's column description for further details.
> > The meaning of this column varies based on the host inode type:...
> > st_ino_target "gorfs"."ino_t", -- Target inode number. Meaningful for directory inode segments (objects in
> > the directory)
> > full_path "gorfs"."absolute_pathname", -- Exploded absolute canonical path for quick lookups. Meaningful
> > only for directory inode segments (objects in the directory)
> > segment_data "bytea", -- Actual data segment. Meaningful only for S_IFLNK and S_IFREG....
> > CONSTRAINT pk_inode_segments PRIMARY KEY ("st_ino", "segment_index"),
> > CONSTRAINT fk_host_inode_must_exist FOREIGN KEY (st_ino)
> > REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
> > ON UPDATE NO ACTION ON DELETE NO ACTION,
> > CONSTRAINT fk_target_inode_must_exist FOREIGN KEY (st_ino_target)
> > REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
> > ON UPDATE NO ACTION ON DELETE NO ACTION,
> > CONSTRAINT uc_no_duplicate_full_paths UNIQUE ("full_path"),
> > CONSTRAINT cc_only_root_can_be_its_own_parent CHECK ("st_ino_target" IS NULL OR "st_ino"::bigint <>
> > "st_ino_target"::bigint OR "st_ino"::bigint = 2)
> > )
> > WITH (
> > OIDS=FALSE
> > );
>
> There is no field st_ctime.
>
> >
> > S_IFSOCK: 0: no data to store, no records here
> > S_IFLNK: 1: contains the link target (see columns comments for details).
> > S_IFREG: 0+: actual data segments, up to 64MB each (see columns comments for details)
> > S_IFBLK: 0: no data to store, no records here
> > S_IFDIR: 0+: one record per object name in the directory
> > S_IFCHR: 0: no data to store, no records here
> > S_IFIFO: 0: no data to store, no records here
> > ';
> > -- Index: gorfs.ix_inode_segments_climb_tree
> >
> > -- DROP INDEX gorfs.ix_inode_segments_climb_tree;
> >
> > CREATE INDEX ix_inode_segments_climb_tree
> > ON gorfs.inode_segments
> > USING btree
> > ("segment_index" COLLATE pg_catalog."default", "st_ino_target");
> >
> > -- Index: gorfs.ix_inode_segments_filter_by_subtree
> >
> > -- DROP INDEX gorfs.ix_inode_segments_filter_by_subtree;
> >
> > CREATE INDEX ix_inode_segments_filter_by_subtree
> > ON gorfs.inode_segments
> > USING btree
> > ("full_path" COLLATE pg_catalog."default" varchar_pattern_ops)
> > WHERE "full_path" IS NOT NULL;
> > COMMENT ON INDEX gorfs.ix_inode_segments_filter_by_subtree
> > IS 'Allows looking for left-anchored paths (either regex or LIKE).
> > WARNING: as of 9.2 the index is not used when the comparison term is a non deterministic function (STABLE or
> > VOLATILE).
> > See http://www.postgresql.org/message-id/5451D6C4.7040308@vuole.me
> > ';
> >
> > -- Index: gorfs.ix_inode_segments_full_path_resolution
> >
> > -- DROP INDEX gorfs.ix_inode_segments_full_path_resolution;
> >
> > CREATE INDEX ix_inode_segments_full_path_resolution
> > ON gorfs.inode_segments
> > USING btree
> > ("st_ino", "full_path" COLLATE pg_catalog."default");
> >
> > -- Index: gorfs.ix_inode_segments_gsdi_pk
> >
> > -- DROP INDEX gorfs.ix_inode_segments_gsdi_pk;
> >
> > CREATE INDEX ix_inode_segments_gsdi_pk
> > ON gorfs.inode_segments
> > USING btree
> > (("st_ino"::"text") COLLATE pg_catalog."default", ("segment_index"::"text") COLLATE pg_catalog."default");
> >
> > -- Index: gorfs.ix_inode_segments_ja_files_lookup
> >
> > -- DROP INDEX gorfs.ix_inode_segments_ja_files_lookup;
> >
> > CREATE INDEX ix_inode_segments_ja_files_lookup
> > ON gorfs.inode_segments
> > USING btree
> > ((
> > CASE
> > WHEN "full_path"::"text" ~ '/[^/]*\.[^/]*$'::"text" THEN "upper"("regexp_replace"("full_path"::"text",
> > '.*\.'::"text", ''::"text", 'g'::"text"))
> > ELSE NULL::"text"
> > END) COLLATE pg_catalog."default")
> > WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text");
> >
> > -- Index: gorfs.ix_inode_segments_notes_clientids
> >
> > -- DROP INDEX gorfs.ix_inode_segments_notes_clientids;
> >
> > CREATE INDEX ix_inode_segments_notes_clientids
> > ON gorfs.inode_segments
> > USING btree
> > (("split_part"("full_path"::"text", '/'::"text", 4)::integer))
> > WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text");
> >
> > -- Index: gorfs.ix_inode_segments_notes_fileids
> >
> > -- DROP INDEX gorfs.ix_inode_segments_notes_fileids;
> >
> > CREATE INDEX ix_inode_segments_notes_fileids
> > ON gorfs.inode_segments
> > USING btree
> > (("split_part"("full_path"::"text", '/'::"text", 8)::integer))
> > WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text");
> >
> > -- Index: gorfs.ix_inode_segments_notes_noteids
> >
> > -- DROP INDEX gorfs.ix_inode_segments_notes_noteids;
> >
> > CREATE INDEX ix_inode_segments_notes_noteids
> > ON gorfs.inode_segments
> > USING btree
> > ((NULLIF("split_part"("full_path"::"text", '/'::"text", 6), 'unassigned'::"text")::integer))
> > WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text");
> >
> > -- Index: gorfs.ix_inode_segments_segment_indexes
> >
> > -- DROP INDEX gorfs.ix_inode_segments_segment_indexes;
> >
> > CREATE INDEX ix_inode_segments_segment_indexes
> > ON gorfs.inode_segments
> > USING btree
> > ("segment_index" COLLATE pg_catalog."default");
> >
> > -- Index: gorfs.ix_inode_segments_st_ino_targets
> >
> > -- DROP INDEX gorfs.ix_inode_segments_st_ino_targets;
> >
> > CREATE INDEX ix_inode_segments_st_ino_targets
> > ON gorfs.inode_segments
> > USING btree
> > ("st_ino_target");
> >
> > -- Index: gorfs.ix_inode_segments_st_inos
> >
> > -- DROP INDEX gorfs.ix_inode_segments_st_inos;
> >
> > CREATE INDEX ix_inode_segments_st_inos
> > ON gorfs.inode_segments
> > USING btree
> > ("st_ino");
> >
> >
> > -- Trigger: a_iud_update_inode on gorfs.inode_segments
> >
> > -- DROP TRIGGER a_iud_update_inode ON gorfs.inode_segments;
> >
> > CREATE TRIGGER a_iud_update_inode
> > AFTER INSERT OR UPDATE OR DELETE
> > ON gorfs.inode_segments
> > FOR EACH ROW
> > EXECUTE PROCEDURE gorfs.tf_inode_segments_update_inodes();
> > COMMENT ON TRIGGER a_iud_update_inode ON gorfs.inode_segments IS 'See invoked function';
> >
> > -- Trigger: a_u_update_children on gorfs.inode_segments
> >
> > -- DROP TRIGGER a_u_update_children ON gorfs.inode_segments;
> >
> > CREATE TRIGGER a_u_update_children
> > AFTER UPDATE
> > ON gorfs.inode_segments
> > FOR EACH ROW
> > EXECUTE PROCEDURE gorfs.tf_inode_segments_update_children();
> >
> > -- Trigger: b_iu_calculate_columns on gorfs.inode_segments
> >
> > -- DROP TRIGGER b_iu_calculate_columns ON gorfs.inode_segments;
> >
> > CREATE TRIGGER b_iu_calculate_columns
> > BEFORE INSERT OR UPDATE
> > ON gorfs.inode_segments
> > FOR EACH ROW
> > EXECUTE PROCEDURE gorfs.tf_inode_segments_calculate_columns();
> > COMMENT ON TRIGGER b_iu_calculate_columns ON gorfs.inode_segments IS 'See invoked function';
> >
> > -- Trigger: ct_valid_data_layouts_only on gorfs.inode_segments
> >
> > -- DROP TRIGGER ct_valid_data_layouts_only ON gorfs.inode_segments;
> >
> > CREATE CONSTRAINT TRIGGER ct_valid_data_layouts_only
> > AFTER INSERT
> > ON gorfs.inode_segments
> > FOR EACH ROW
> > EXECUTE PROCEDURE gorfs.tf_inode_segments_valid_data_layouts_only();
> > COMMENT ON TRIGGER ct_valid_data_layouts_only ON gorfs.inode_segments IS 'See invoked function';
> >
> >
> >
> > gorfs.noes:
> >
> >
> > -- View: gorfs.nodes
> >
> > -- DROP VIEW gorfs.nodes;
> >
> > CREATE OR REPLACE VIEW gorfs.nodes AS
> > SELECT "p"."full_path" AS "node_full_path", "h"."st_ino" AS "parent_inode_id",
> > "t"."st_ino" AS "inode_id",
> > CASE
> > WHEN "p"."st_ino_target"::bigint = 2 THEN NULL::character varying
> > ELSE "p"."segment_index"::character varying
> > END AS "relative_path",
> > "t"."st_mode"::bigint AS "raw_mode", "f"."constant_name" AS "object_type",
> > ("gorfs"."mode_t_bits"('S_ISUID'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode"::"bit")::integer
> > <> 0 AS "setuid",
> > ("gorfs"."mode_t_bits"('S_ISGID'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode"::"bit")::integer
> > <> 0 AS "setgid",
> > ("gorfs"."mode_t_bits"('S_ISVTX'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode"::"bit")::integer
> > <> 0 AS "sticky",
> > "right"("concat"((("gorfs"."mode_t_bits"('S_IRWXU'::"gorfs"."mode_t_constant_name")::"bit" |
> > "gorfs"."mode_t_bits"('S_IRWXG'::"gorfs"."mode_t_constant_name")::"bit") |
> > "gorfs"."mode_t_bits"('S_IRWXO'::"gorfs"."mode_t_constant_name")::"bit") & "t"."st_mode"::"bit"), 9)::bit(9) AS
> > "permissions",
> > "t"."st_nlink" AS "links_count", "t"."st_uid" AS "owner_uid",
> > "t"."st_gid" AS "owner_gid", "t"."st_size" AS "data_length",
> > "t"."st_atime" AS "last_accessed", "t"."st_mtime" AS "last_modified",
> > "t"."st_ctime" AS "last_changed", "t"."checksum_md5",
> > ("mst"."media_type" || '/'::"text") || "mst"."subtype_string"::"text" AS "media_type",
> > CASE
> > WHEN "f"."constant_name" = 'S_IFLNK'::"gorfs"."mode_t_constant_name" THEN ( SELECT
> > "convert_from"("ls"."segment_data", 'UTF8'::"name") AS "convert_from"
> > FROM "gorfs"."inode_segments" "ls"
> > WHERE "ls"."st_ino"::bigint = "p"."st_ino_target"::bigint)
> > ELSE NULL::"text"
> > END AS "target",
> > CASE
> > WHEN "f"."constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name" THEN ( SELECT
> > "string_agg"("fs"."segment_data", ''::"bytea" ORDER BY "fs"."segment_index") AS "string_agg"
> > FROM "gorfs"."inode_segments" "fs"
> > WHERE "fs"."st_ino"::bigint = "p"."st_ino_target"::bigint)
> > ELSE NULL::"bytea"
> > END AS "file_data",
> > "t"."external_size" IS NOT NULL AS "is_external",
> > "t"."external_size" AS "data_length_target"
> > FROM "gorfs"."inode_segments" "p"
> > JOIN "gorfs"."dir_inodes" "h" ON "h"."st_ino"::bigint = "p"."st_ino"::bigint
> > JOIN "gorfs"."inodes" "t" ON "t"."st_ino"::bigint = "p"."st_ino_target"::bigint
> > JOIN "gorfs"."mode_t_flags"() "f"("constant_name", "description", "bits", "bits_octal", "bits_hex",
> > "bits_decimal") ON "f"."bits"::"bit" = ("gorfs"."mode_t_bits"('S_IFMT'::"gorfs"."mode_t_constant_name")::"bit" &
> > "t"."st_mode"::"bit")
> > LEFT JOIN "media_subtypes" "mst" ON "mst"."media_subtype_id" = "t"."media_subtype_id";
> >
> > -- Trigger: i_iud_action_changes on gorfs.nodes
> >
> > -- DROP TRIGGER i_iud_action_changes ON gorfs.nodes;
> >
> > CREATE TRIGGER i_iud_action_changes
> > INSTEAD OF INSERT OR UPDATE OR DELETE
> > ON gorfs.nodes
> > FOR EACH ROW
> > EXECUTE PROCEDURE gorfs.tf_nodes_action_changes();
>
> The value of st_ctime comes from table gorfs.inodes. So build the index on that.
>
> >
> >
> >
> >
> > > Date: Sun, 10 Jan 2016 23:04:20 -0800
> > > Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
> > > From: vitaly.burovoy@gmail.com
> > > To: smerlo50@outlook.com
> > > CC: pgsql-general@postgresql.org
> > >
> > > On 1/10/16, Saulo Merlo <smerlo50@outlook.com> wrote:
> > > > CREATE INDEX CONCURRENTLY index_name ON gorfs.inode_segments(st_ctime);
> > > > ERROR: column "st_ctime" does not exist
> > > > Look the error I've got
> > > >
> > > > Lucas
> > > >
> > > >> Date: Sun, 10 Jan 2016 22:43:21 -0800
> > > >> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
> > > >> From: vitaly.burovoy@gmail.com
> > > >> To: smerlo50@outlook.com
> > > >> CC: pgsql-general@postgresql.org
> > > >>
> > > >> On 1/10/16, Saulo Merlo <smerlo50@outlook.com> wrote:
> > > >> > Hi Vitaly,
> > > >> >
> > > >> > Yep... gorfs.nodes is a view.
> > > >> > And the schema is: gorfs.inode_segments
> > > >> > So... CREATE INDEX index_name ON gorfs.inode_segments(st_ctime)
> > > >> > Is that correct? It would be "st_ctime"?
> > > >> If "inodes" is an alias for "gorfs.inode_segments" in your view, yes,
> > > >> the above DDL is OK. According to EXPLAIN's "Filter" row the column
> > > >> involving in comparison is st_ctime.
> > > >>
> > > >> Hint: you can create the index without blocking table using "CREATE
> > > >> INDEX CONCURRENTLY":
> > > >> http://www.postgresql.org/docs/9.2/static/sql-createindex.html
> > > >>
> > > >> > I've rewriten the query as well. Thank you for that!
> > > >> >
> > > >> > Thank you
> > > >> > Lucas
> > > >>
> > > >> >> Date: Sun, 10 Jan 2016 21:23:01 -0800
> > > >> >> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
> > > >> >> From: vitaly.burovoy@gmail.com
> > > >> >> To: smerlo50@outlook.com
> > > >> >> CC: pgsql-general@postgresql.org
> > > >> >>
> > > >> >> On 1/10/16, Vitaly Burovoy <vitaly.burovoy@gmail.com> wrote:
> > > >> >> > On 1/10/16, Saulo Merlo <smerlo50@outlook.com> wrote:
> > > >> >> >> I've got a slow query.. I'd like to make it faster.. Make add an
> > > >> >> >> index?
> > > >> >> >> Query:
> > > >> >> >> SELECT
> > > >> >> >> <<overquoting>>
> > > >> >> >> FROM gorfs.nodes AS f
> > > >> >> >> <<overquoting>>
> > > >> >> >> WHERE f.file_data IS NOT NULL
> > > >> >> >> AND ((transaction_timestamp() AT TIME ZONE 'UTC') >
> > > >> >> >> (f.last_changed
> > > >> >> >> +
> > > >> >> >> '24
> > > >> >> >> months' :: INTERVAL)) LIMIT 100;
> > > >> >> >
> > > >> >> >> <<overquoting>>
> > > >> >> >> "Total runtime: 94989.208 ms"What could I do to make it faster?
> > > >> >> >> Thank
> > > >> >> >> you.
> > > >> >> >
> > > >> >> > At least you can add an index:
> > > >> >> > CREATE INDEX ON gorfs.nodes(last_changed)
> > > >> >> >
> > > >> >> > and rewrite part of WHERE clause to:
> > > >> >> > (f.last_changed < (transaction_timestamp() AT TIME ZONE 'UTC' - '24
> > > >> >> > months'::INTERVAL))
> > > >> >> >
> > > >> >> > It allows to decrease the slowest part of your query (sequence
> > > >> >> > scanning of a table, all 13.5M rows):
> > > >> >> >> -> Seq Scan on "inodes" "t" (cost=0.00..1411147.24 rows=13416537
> > > >> >> >> width=29) (actual time=94987.224..94987.224 rows=0 loops=1)
> > > >> >> >> Filter: ("timezone"('UTC'::"text", "transaction_timestamp"())
> > > >> >> >> >
> > > >> >> >> (("st_ctime")::timestamp without time zone + '2 years'::interval))
> > > >> >> >
> > > >> >> > compare that time to the one in the topmost row of EXPLAIN:
> > > >> >> >> Limit (cost=1556.99..1336437.30 rows=100 width=186) (actual
> > > >> >> >> time=94987.261..94987.261 rows=0 loops=1)
> > > >> >>
> > > >> >> Hmm. It seems that gorfs.nodes is a view.
> > > >> >> So creating index should be something like (I have no idea that schema
> > > >> >> name for it):
> > > >> >> CREATE INDEX ON _schema_name_.inodes(st_ctime)
> > >
> > > Please, post a definition of a table and a view (and all intermediate
> > > views if any).
> > >
> > > Via psql it can be done via:
> > > \d gorfs.inode_segments
> > > \d+ gorfs.nodes
> > >
> > > --
> > > Best regards,
> > > Vitaly Burovoy
> > >
> > >
> > > --
> > > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > > To make changes to your subscription:
> > > http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Re: Slow Query - PostgreSQL 9.2

From

Saulo Merlo

Date:

11 January 2016, 23:03:01

Still getting a sloooow one..

Any thoughts?

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

How could I do that?

Lucas

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

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

Thank you!

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

Re: Slow Query - PostgreSQL 9.2

From

Saulo Merlo

Date:

11 January 2016, 23:13:45

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;

Still getting a sloooow one..

Any thoughts?

How could I do that?

Lucas

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"

Re: Slow Query - PostgreSQL 9.2

From

Vitaly Burovoy

Date:

12 January 2016, 00:37:53

On 1/11/16, Saulo Merlo <smerlo50@outlook.com> wrote:
> NEW QUERY:
>
> SELECT
> <<overquoting>>
> WHERE f.nfs_file_path IS NULL
>   AND ((transaction_timestamp() AT TIME ZONE \'UTC\') > (f.st_mtime+ \'' . $fileMigrationMonthAge . ' months\' ::
INTERVAL))LIMIT 100; 
>
> From: smerlo50@outlook.com
> To: clavadetscher@swisspug.org; vitaly.burovoy@gmail.com
> CC: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
> Date: Mon, 11 Jan 2016 20:02:54 +0000
>> Still getting a sloooow one..
>> Any thoughts?
>>
>> My hypothesis is; the 1 clause that will always be used is in the WHERE statement below. This can either be
nfs_file_pathor nfs_migration_date (both new columns). Adding an index on either of these columns and using them in the
clauseshould improve things greatly. 
>>
>> How could I do that?
>> Lucas
>>
>> "Limit  (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.051..0.051 rows=0 loops=1)"
>> <<overquoting>>
>> "Total runtime: 1.395 ms"

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

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

--
Best regards,
Vitaly Burovoy

Re: Slow Query - PostgreSQL 9.2

From

Saulo Merlo

Date:

12 January 2016, 00:37:55

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

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;

Still getting a sloooow one..

Any thoughts?

How could I do that?

Lucas

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"

Re: Slow Query - PostgreSQL 9.2

From

Saulo Merlo

Date:

12 January 2016, 00:58:53

Ok, thanks Vitaly.

I need to create a TEXT or CARCHAR index..

and another one with timestamptz

How can I do?

Thanks

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

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;

Still getting a sloooow one..

Any thoughts?

How could I do that?

Lucas

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"

Re: Slow Query - PostgreSQL 9.2

From

Saulo Merlo

Date:

12 January 2016, 01:05:58

UPDATED LAST EMAIL

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"

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

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;

Still getting a sloooow one..

Any thoughts?

How could I do that?

Lucas

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"

Re: Slow Query - PostgreSQL 9.2

From

Vitaly Burovoy

Date:

12 January 2016, 02:01:13

On 1/11/16, Saulo Merlo <smerlo50@outlook.com> wrote:
> Ok, thanks Vitaly.
> I need to create a TEXT or VARCHAR index.
> and another one with timestamptz
> How can I do?Thanks

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

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

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

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

>
> From: smerlo50@outlook.com
> To: clavadetscher@swisspug.org; vitaly.burovoy@gmail.com
> CC: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
> Date: Mon, 11 Jan 2016 21:37:43 +0000
>
> Hey guys..
> How could I create a timestampandtz index?
> CREATE TABLE gorfs.inode_segments
> (
>  <<overquoting>>
> )

--
Best regards,
Vitaly Burovoy

Re: Slow Query - PostgreSQL 9.2

From

Saulo Merlo

Date:

12 January 2016, 02:17:50

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

Re: Slow Query - PostgreSQL 9.2

From

Saulo Merlo

Date:

13 January 2016, 00:28:42

UPDATED:

Index created:

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

NEW EXPLAIN ANALYZE:

http://explain.depesz.com/s/Swu

I also am able to create a temporary table to store migrations, which may be the best option (no longer need to join new columns in query)

If you could help with that as well..

Thank you

gorfs.nodes is a view:

CREATE OR REPLACE VIEW gorfs.nodes AS
SELECT "p"."full_path" AS "node_full_path", "h"."st_ino" AS "parent_inode_id",
"t"."st_ino" AS "inode_id",
CASE
WHEN "p"."st_ino_target"::bigint = 2 THEN NULL::character varying
ELSE "p"."segment_index"::character varying
END AS "relative_path",
"t"."st_mode"::bigint AS "raw_mode", "f"."constant_name" AS "object_type",
("gorfs"."mode_t_bits"('S_ISUID'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode"::"bit")::integer <> 0 AS "setuid",
("gorfs"."mode_t_bits"('S_ISGID'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode"::"bit")::integer <> 0 AS "setgid",
("gorfs"."mode_t_bits"('S_ISVTX'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode"::"bit")::integer <> 0 AS "sticky",
"right"("concat"((("gorfs"."mode_t_bits"('S_IRWXU'::"gorfs"."mode_t_constant_name")::"bit" | "gorfs"."mode_t_bits"('S_IRWXG'::"gorfs"."mode_t_constant_name")::"bit") | "gorfs"."mode_t_bits"('S_IRWXO'::"gorfs"."mode_t_constant_name")::"bit") & "t"."st_mode"::"bit"), 9)::bit(9) AS "permissions",
"t"."st_nlink" AS "links_count", "t"."st_uid" AS "owner_uid",
"t"."st_gid" AS "owner_gid", "t"."st_size" AS "data_length",
"t"."st_atime" AS "last_accessed", "t"."st_mtime" AS "last_modified",
"t"."st_ctime" AS "last_changed", "t"."checksum_md5",
("mst"."media_type" || '/'::"text") || "mst"."subtype_string"::"text" AS "media_type",
CASE
WHEN "f"."constant_name" = 'S_IFLNK'::"gorfs"."mode_t_constant_name" THEN ( SELECT "convert_from"("ls"."segment_data", 'UTF8'::"name") AS "convert_from"
FROM "gorfs"."inode_segments" "ls"
WHERE "ls"."st_ino"::bigint = "p"."st_ino_target"::bigint)
ELSE NULL::"text"
END AS "target",
CASE
WHEN "f"."constant_name" = 'S_IFREG'::"gorfs"."mode_t_constant_name" THEN ( SELECT "string_agg"("fs"."segment_data", ''::"bytea" ORDER BY "fs"."segment_index") AS "string_agg"
FROM "gorfs"."inode_segments" "fs"
WHERE "fs"."st_ino"::bigint = "p"."st_ino_target"::bigint)
ELSE NULL::"bytea"
END AS "file_data",
"t"."external_size" IS NOT NULL AS "is_external",
"t"."external_size" AS "data_length_target"
FROM "gorfs"."inode_segments" "p"
JOIN "gorfs"."dir_inodes" "h" ON "h"."st_ino"::bigint = "p"."st_ino"::bigint
JOIN "gorfs"."inodes" "t" ON "t"."st_ino"::bigint = "p"."st_ino_target"::bigint
JOIN "gorfs"."mode_t_flags"() "f"("constant_name", "description", "bits", "bits_octal", "bits_hex", "bits_decimal") ON "f"."bits"::"bit" = ("gorfs"."mode_t_bits"('S_IFMT'::"gorfs"."mode_t_constant_name")::"bit" & "t"."st_mode"::"bit")
LEFT JOIN "media_subtypes" "mst" ON "mst"."media_subtype_id" = "t"."media_subtype_id";

gorfs.inode_segments:

Table "gorfs.inode_segments"
Column | Type | Modifiers
--------------------+------------------------------+-----------
st_ino | "gorfs"."ino_t" | not null
segment_index | "gorfs"."pathname_component" | not null
st_ino_target | "gorfs"."ino_t" |
full_path | "gorfs"."absolute_pathname" |
segment_data | "bytea" |
nfs_migration_date | timestamp with time zone |
nfs_file_path | "text" |
Indexes:
"pk_inode_segments" PRIMARY KEY, "btree" ("st_ino", "segment_index")
"uc_no_duplicate_full_paths" UNIQUE CONSTRAINT, "btree" ("full_path")
"inode_segments_st_ino_target_pidx" "btree" ("st_ino" DESC, "st_ino_target" DESC) WHERE "nfs_migration_date" IS NULL
"ix_inode_segments_climb_tree" "btree" ("segment_index", "st_ino_target")
"ix_inode_segments_filter_by_subtree" "btree" ("full_path" "varchar_pattern_ops") WHERE "full_path" IS NOT NULL
"ix_inode_segments_full_path_resolution" "btree" ("st_ino", "full_path")
"ix_inode_segments_gsdi_pk" "btree" (("st_ino"::"text"), ("segment_index"::"text"))
"ix_inode_segments_ja_files_lookup" "btree" ((
CASE
WHEN "full_path"::"text" ~ '/[^/]*\.[^/]*$'::"text" THEN "upper"("regexp_replace"("full_path"::"text", '.*\.'::"text", ''::"text", 'g'::"text"))
ELSE NULL::"text"
END)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text")
"ix_inode_segments_nfs_file_path" "btree" ("full_path")
"ix_inode_segments_nfs_migration_date" "btree" ("nfs_migration_date") WHERE "nfs_migration_date" IS NULL
"ix_inode_segments_nfs_st_ino" "btree" ("st_ino")
"ix_inode_segments_notes_clientids" "btree" (("split_part"("full_path"::"text", '/'::"text", 4)::integer)) WHERE "gorfs"."is_kaminski_note_path"("full_path"
::"text")
"ix_inode_segments_notes_fileids" "btree" (("split_part"("full_path"::"text", '/'::"text", 8)::integer)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::
"text")
"ix_inode_segments_notes_noteids" "btree" ((NULLIF("split_part"("full_path"::"text", '/'::"text", 6), 'unassigned'::"text")::integer)) WHERE "gorfs"."is_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"()

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