Large Binary Columns - Slow Query

The binary columns are large so I think that's why the query referencing them is slow.PostgreSQL 9.2Is there a way to speed it up, maybe compression on transfer? Some indexes has millions of rows...Maybe create an index to some specifics situations, and not general ones.
Thank you.
QUERY:SELECT 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
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 LIMIT 500;EXPLAIN ANALYZE:"Limit (cost=20243.45..1593866.18 rows=500 width=180) (actual time=210.036..44701.406 rows=500 loops=1)"
" -> Nested Loop Left Join (cost=20243.45..757747912.38 rows=240759 width=180) (actual time=210.036..44701.223 rows=500 loops=1)"
" -> Nested Loop Left Join (cost=19724.45..450144827.58 rows=240759 width=148) (actual time=196.881..44353.113 rows=490 loops=1)"
" -> Nested Loop (cost=19205.46..168833316.21 rows=240759 width=108) (actual time=196.239..43707.952 rows=486 loops=1)"
" -> Nested Loop (cost=19205.46..164947484.34 rows=354030 width=107) (actual time=196.233..43706.095 rows=486 loops=1)"
" -> Nested Loop (cost=19205.46..159233457.64 rows=520593 width=99) (actual time=196.223..43704.141 rows=486 loops=1)"
" -> Nested Loop (cost=19205.46..153056028.27 rows=987163 width=107) (actual time=196.202..43700.535 rows=486 loops=1)"
" Join Filter: ((("t"."st_ino")::bigint = ("p"."st_ino_target")::bigint) AND (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=19205.46..83831623.03 rows=1458893 width=36) (actual time=155.629..43307.564 rows=486 loops=1)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("f"."bits")::"bit")"
" Rows Removed by Join Filter: 10673"
" -> Nested Loop (cost=18686.46..83243898.69 rows=1458893 width=41) (actual time=155.432..43302.768 rows=486 loops=1)"
" -> Nested Loop (cost=18686.46..59543972.56 rows=2145267 width=41) (actual time=155.420..43300.521 rows=486 loops=1)"
" -> Merge Join (cost=18686.46..29848042.37 rows=4751151 width=16) (actual time=155.397..43289.955 rows=486 loops=1)"
" Merge Cond: (("fd"."st_ino")::bigint = ("mv"."st_ino_target")::bigint)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd" (cost=0.00..24356396.96 rows=83232691 width=16) (actual time=0.080..43016.366 rows=2037019 loops=1)"
" -> Index Scan using "ix_inode_segments_climb_tree" on "inode_segments" "mv" (cost=0.00..7695601.08 rows=6986452 width=16) (actual time=0.197..96.693 rows=487 loops=1)"
" Index Cond: (("segment_index")::"text" = 'main.with_name'::"text")"
" -> Index Scan using "pk_inodes" on "inodes" "t" (cost=0.00..6.24 rows=1 width=29) (actual time=0.020..0.020 rows=1 loops=486)"
" Index Cond: (("st_ino")::bigint = ("fd"."st_ino_target")::bigint)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "fi" (cost=0.00..11.04 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=486)"
" Index Cond: (("st_ino_target")::bigint = ("mv"."st_ino")::bigint)"
" -> Materialize (cost=519.00..519.97 rows=23 width=36) (actual time=0.001..0.003 rows=23 loops=486)"
" -> Subquery Scan on "f" (cost=519.00..519.86 rows=23 width=36) (actual time=0.138..0.452 rows=23 loops=1)"
" -> CTE Scan on "stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (actual time=0.137..0.447 rows=23 loops=1)"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (actual time=0.007..0.030 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.120..0.407 rows=23 loops=1)"
" SubPlan 6"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (actual time=0.013..0.013 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.009 rows=3 loops=23)"
" -> Index Scan using "ix_inode_segments_st_ino_targets" on "inode_segments" "p" (cost=0.00..10.97 rows=1 width=79) (actual time=0.002..0.002 rows=1 loops=486)"
" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"
" SubPlan 4"
" -> Aggregate (cost=36.46..36.47 rows=1 width=47) (actual time=0.712..0.712 rows=1 loops=486)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..36.33 rows=52 width=47) (actual time=0.003..0.004 rows=1 loops=486)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..6.25 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=486)"
" 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..10.97 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=486)"
" 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..10.97 rows=1 width=17) (actual time=0.003..0.003 rows=1 loops=486)"
" Index Cond: (("st_ino_target")::bigint = ("fn"."st_ino")::bigint)"
" -> Nested Loop (cost=519.00..1168.43 rows=1 width=48) (actual time=1.296..1.326 rows=1 loops=486)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"
" Rows Removed by Join Filter: 16"
" -> Nested Loop (cost=0.00..648.22 rows=1 width=33) (actual time=1.079..1.080 rows=1 loops=486)"
" -> Nested Loop (cost=0.00..641.97 rows=1 width=24) (actual time=1.076..1.077 rows=1 loops=486)"
" -> Nested Loop (cost=0.00..635.72 rows=1 width=32) (actual time=1.026..1.027 rows=1 loops=486)"
" -> Nested Loop (cost=0.00..624.74 rows=1 width=16) (actual time=0.981..0.982 rows=1 loops=486)"
" -> Nested Loop (cost=0.00..602.15 rows=1 width=16) (actual time=0.964..0.965 rows=1 loops=486)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..21.51 rows=52 width=16) (actual time=0.003..0.003 rows=1 loops=486)"
" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..11.16 rows=1 width=16) (actual time=0.947..0.947 rows=1 loops=493)"
" 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..22.07 rows=52 width=16) (actual time=0.023..0.023 rows=1 loops=345)"
" 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..10.97 rows=1 width=16) (actual time=0.062..0.062 rows=1 loops=345)"
" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..6.25 rows=1 width=8) (actual time=0.069..0.069 rows=1 loops=345)"
" 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..6.24 rows=1 width=21) (actual time=0.003..0.003 rows=1 loops=345)"
" 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) (actual time=0.002..0.010 rows=23 loops=345)"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (actual time=0.003..0.025 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.101..0.419 rows=23 loops=1)"
" SubPlan 9"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (actual time=0.012..0.012 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.008 rows=3 loops=23)"
" SubPlan 2"
" -> Aggregate (cost=36.46..36.47 rows=1 width=47) (actual time=0.314..0.314 rows=1 loops=345)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..36.33 rows=52 width=47) (actual time=0.012..0.034 rows=1 loops=345)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" -> Nested Loop (cost=519.00..1168.21 rows=1 width=48) (actual time=0.236..0.304 rows=1 loops=490)"
" Join Filter: ((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"
" Rows Removed by Join Filter: 16"
" -> Nested Loop (cost=0.00..648.01 rows=1 width=33) (actual time=0.174..0.206 rows=1 loops=490)"
" -> Nested Loop (cost=0.00..641.76 rows=1 width=24) (actual time=0.145..0.176 rows=1 loops=490)"
" -> Nested Loop (cost=0.00..635.50 rows=1 width=32) (actual time=0.099..0.125 rows=1 loops=490)"
" -> Nested Loop (cost=0.00..624.53 rows=1 width=16) (actual time=0.096..0.107 rows=1 loops=490)"
" -> Nested Loop (cost=0.00..602.15 rows=1 width=16) (actual time=0.059..0.069 rows=1 loops=490)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..21.51 rows=52 width=16) (actual time=0.004..0.004 rows=1 loops=490)"
" Index Cond: (("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inode_segments" on "inode_segments" "mv" (cost=0.00..11.16 rows=1 width=16) (actual time=0.053..0.062 rows=1 loops=503)"
" 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..21.86 rows=52 width=16) (actual time=0.050..0.051 rows=1 loops=355)"
" 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..10.97 rows=1 width=16) (actual time=0.003..0.023 rows=1 loops=355)"
" Index Cond: (("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"
" -> Index Scan using "pk_inodes" on "inodes" "i" (cost=0.00..6.25 rows=1 width=8) (actual time=0.069..0.069 rows=1 loops=355)"
" 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..6.24 rows=1 width=21) (actual time=0.039..0.040 rows=1 loops=355)"
" 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) (actual time=0.001..0.010 rows=23 loops=355)"
" CTE stat_h"
" -> Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (actual time=0.002..0.023 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.059..0.499 rows=23 loops=1)"
" SubPlan 12"
" -> Aggregate (cost=22.51..22.52 rows=1 width=32) (actual time=0.012..0.012 rows=1 loops=23)"
" -> Function Scan on "regexp_split_to_table" "digits" (cost=0.01..10.01 rows=1000 width=32) (actual time=0.007..0.008 rows=3 loops=23)"
" SubPlan 3"
" -> Aggregate (cost=36.46..36.47 rows=1 width=47) (actual time=0.117..0.117 rows=1 loops=355)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..36.33 rows=52 width=47) (actual time=0.039..0.040 rows=1 loops=355)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
" SubPlan 1"
" -> Aggregate (cost=36.46..36.47 rows=1 width=47) (actual time=0.250..0.251 rows=1 loops=500)"
" -> Index Scan using "ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..36.33 rows=52 width=47) (actual time=0.002..0.002 rows=1 loops=500)"
" Index Cond: (("st_ino")::bigint = ("p"."st_ino_target")::bigint)"
"Total runtime: 44702.582 ms"BLOAT:schemaname tblname idxname real_size bloat_size
gorfs inode_segments "ix_inode_segments_gsdi_pk" 4246921216 973275136
gorfs inode_segments "ix_inode_segments_ja_files_lookup" 184844288 59793408
gorfs inode_segments "ix_inode_segments_notes_clientids" 187555840 62504960
gorfs inode_segments "ix_inode_segments_notes_fileids" 247037952 121987072
gorfs inode_segments "ix_inode_segments_notes_noteids" 230473728 105422848

On Jan 10, 2016 21:50, "Saulo Merlo" <smerlo50@outlook.com> wrote:

The binary columns are large so I think that's why the query referencing

them is slow.

PostgreSQL 9.2

Is there a way to speed it up, maybe compression on transfer? Some

indexes has millions of rows...

Maybe create an index to some specifics situations, and not general ones.

Can you show us the table definitions?

What are you trying to do?

Thank you.

QUERY:

SELECT 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
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 LIMIT 500;

EXPLAIN ANALYZE:

"Limit (cost=20243.45..1593866.18 rows=500 width=180) (actual

time=210.036..44701.406 rows=500 loops=1)"

" -> Nested Loop Left Join (cost=20243.45..757747912.38 rows=240759

width=180) (actual time=210.036..44701.223 rows=500 loops=1)"

" -> Nested Loop Left Join (cost=19724.45..450144827.58

rows=240759 width=148) (actual time=196.881..44353.113 rows=490 loops=1)"

" -> Nested Loop (cost=19205.46..168833316.21 rows=240759

width=108) (actual time=196.239..43707.952 rows=486 loops=1)"

" -> Nested Loop (cost=19205.46..164947484.34

rows=354030 width=107) (actual time=196.233..43706.095 rows=486 loops=1)"

" -> Nested Loop (cost=19205.46..159233457.64

rows=520593 width=99) (actual time=196.223..43704.141 rows=486 loops=1)"

" -> Nested Loop

(cost=19205.46..153056028.27 rows=987163 width=107) (actual
time=196.202..43700.535 rows=486 loops=1)"

" Join Filter:

((("t"."st_ino")::bigint = ("p"."st_ino_target")::bigint) AND (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=19205.46..83831623.03 rows=1458893 width=36) (actual
time=155.629..43307.564 rows=486 loops=1)"

" Join Filter:

((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") =
("f"."bits")::"bit")"

" Rows Removed by Join Filter:

10673"

" -> Nested Loop

(cost=18686.46..83243898.69 rows=1458893 width=41) (actual
time=155.432..43302.768 rows=486 loops=1)"

" -> Nested Loop

(cost=18686.46..59543972.56 rows=2145267 width=41) (actual
time=155.420..43300.521 rows=486 loops=1)"

" -> Merge Join

(cost=18686.46..29848042.37 rows=4751151 width=16) (actual
time=155.397..43289.955 rows=486 loops=1)"

" Merge

Cond: (("fd"."st_ino")::bigint = ("mv"."st_ino_target")::bigint)"

" -> Index

Scan using "ix_inode_segments_st_inos" on "inode_segments" "fd"
(cost=0.00..24356396.96 rows=83232691 width=16) (actual
time=0.080..43016.366 rows=2037019 loops=1)"

" -> Index

Scan using "ix_inode_segments_climb_tree" on "inode_segments" "mv"
(cost=0.00..7695601.08 rows=6986452 width=16) (actual time=0.197..96.693
rows=487 loops=1)"

"

Index Cond: (("segment_index")::"text" = 'main.with_name'::"text")"

" -> Index Scan

using "pk_inodes" on "inodes" "t" (cost=0.00..6.24 rows=1 width=29)
(actual time=0.020..0.020 rows=1 loops=486)"

" Index

Cond: (("st_ino")::bigint = ("fd"."st_ino_target")::bigint)"

" -> Index Scan using

"ix_inode_segments_st_ino_targets" on "inode_segments" "fi"
(cost=0.00..11.04 rows=1 width=16) (actual time=0.003..0.004 rows=1
loops=486)"

" Index Cond:

(("st_ino_target")::bigint = ("mv"."st_ino")::bigint)"

" -> Materialize

(cost=519.00..519.97 rows=23 width=36) (actual time=0.001..0.003 rows=23
loops=486)"

" -> Subquery Scan on

"f" (cost=519.00..519.86 rows=23 width=36) (actual time=0.138..0.452
rows=23 loops=1)"

" -> CTE Scan on

"stat_h_with_bits" "sb" (cost=519.00..519.63 rows=23 width=72) (actual
time=0.137..0.447 rows=23 loops=1)"

" CTE stat_h"
" ->

Values Scan on "*VALUES*" (cost=0.00..0.29 rows=23 width=68) (actual
time=0.007..0.030 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.120..0.407 rows=23 loops=1)"

"

SubPlan 6"

"

-> Aggregate (cost=22.51..22.52 rows=1 width=32) (actual
time=0.013..0.013 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.009 rows=3 loops=23)"

" -> Index Scan using

"ix_inode_segments_st_ino_targets" on "inode_segments" "p"
(cost=0.00..10.97 rows=1 width=79) (actual time=0.002..0.002 rows=1
loops=486)"

" Index Cond:

(("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"

" SubPlan 4"
" -> Aggregate

(cost=36.46..36.47 rows=1 width=47) (actual time=0.712..0.712 rows=1
loops=486)"

" -> Index Scan using

"ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..36.33
rows=52 width=47) (actual time=0.003..0.004 rows=1 loops=486)"

" Index Cond:

(("st_ino")::bigint = ("p"."st_ino_target")::bigint)"

" -> Index Scan using "pk_inodes" on

"inodes" "i" (cost=0.00..6.25 rows=1 width=8) (actual time=0.006..0.006
rows=1 loops=486)"

" 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..10.97 rows=1 width=16) (actual time=0.003..0.003 rows=1
loops=486)"

" 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..10.97 rows=1 width=17) (actual time=0.003..0.003 rows=1
loops=486)"

" Index Cond: (("st_ino_target")::bigint =

("fn"."st_ino")::bigint)"

" -> Nested Loop (cost=519.00..1168.43 rows=1 width=48)

(actual time=1.296..1.326 rows=1 loops=486)"

" Join Filter:

((B'00000000000000001111000000000000'::"bit" & ("t"."st_mode")::"bit") =
("sb"."bits")::"bit")"

" Rows Removed by Join Filter: 16"
" -> Nested Loop (cost=0.00..648.22 rows=1 width=33)

(actual time=1.079..1.080 rows=1 loops=486)"

" -> Nested Loop (cost=0.00..641.97 rows=1

width=24) (actual time=1.076..1.077 rows=1 loops=486)"

" -> Nested Loop (cost=0.00..635.72

rows=1 width=32) (actual time=1.026..1.027 rows=1 loops=486)"

" -> Nested Loop

(cost=0.00..624.74 rows=1 width=16) (actual time=0.981..0.982 rows=1
loops=486)"

" -> Nested Loop

(cost=0.00..602.15 rows=1 width=16) (actual time=0.964..0.965 rows=1
loops=486)"

" -> Index Scan using

"ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..21.51
rows=52 width=16) (actual time=0.003..0.003 rows=1 loops=486)"

" Index Cond:

(("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"

" -> Index Scan using

"pk_inode_segments" on "inode_segments" "mv" (cost=0.00..11.16 rows=1
width=16) (actual time=0.947..0.947 rows=1 loops=493)"

" 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..22.07
rows=52 width=16) (actual time=0.023..0.023 rows=1 loops=345)"

" 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..10.97 rows=1 width=16) (actual time=0.062..0.062 rows=1
loops=345)"

" Index Cond:

(("st_ino_target")::bigint = ("fd"."st_ino_target")::bigint)"

" -> Index Scan using "pk_inodes" on

"inodes" "i" (cost=0.00..6.25 rows=1 width=8) (actual time=0.069..0.069
rows=1 loops=345)"

" 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..6.24 rows=1 width=21) (actual time=0.003..0.003 rows=1
loops=345)"

" 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) (actual time=0.002..0.010 rows=23
loops=345)"

" CTE stat_h"
" -> Values Scan on "*VALUES*"

(cost=0.00..0.29 rows=23 width=68) (actual time=0.003..0.025 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.101..0.419 rows=23
loops=1)"

" SubPlan 9"
" -> Aggregate (cost=22.51..22.52

rows=1 width=32) (actual time=0.012..0.012 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.008 rows=3 loops=23)"

" SubPlan 2"
" -> Aggregate (cost=36.46..36.47 rows=1 width=47)

(actual time=0.314..0.314 rows=1 loops=345)"

" -> Index Scan using

"ix_inode_segments_st_inos" on "inode_segments" "fs" (cost=0.00..36.33
rows=52 width=47) (actual time=0.012..0.034 rows=1 loops=345)"

" Index Cond: (("st_ino")::bigint =

("p"."st_ino_target")::bigint)"

" -> Nested Loop (cost=519.00..1168.21 rows=1 width=48) (actual

time=0.236..0.304 rows=1 loops=490)"

" Join Filter: ((B'00000000000000001111000000000000'::"bit"

& ("t"."st_mode")::"bit") = ("sb"."bits")::"bit")"

" Rows Removed by Join Filter: 16"
" -> Nested Loop (cost=0.00..648.01 rows=1 width=33)

(actual time=0.174..0.206 rows=1 loops=490)"

" -> Nested Loop (cost=0.00..641.76 rows=1 width=24)

(actual time=0.145..0.176 rows=1 loops=490)"

" -> Nested Loop (cost=0.00..635.50 rows=1

width=32) (actual time=0.099..0.125 rows=1 loops=490)"

" -> Nested Loop (cost=0.00..624.53

rows=1 width=16) (actual time=0.096..0.107 rows=1 loops=490)"

" -> Nested Loop

(cost=0.00..602.15 rows=1 width=16) (actual time=0.059..0.069 rows=1
loops=490)"

" -> Index Scan using

"ix_inode_segments_st_inos" on "inode_segments" "fi" (cost=0.00..21.51
rows=52 width=16) (actual time=0.004..0.004 rows=1 loops=490)"

" Index Cond:

(("st_ino")::bigint = ("fn"."st_ino_target")::bigint)"

" -> Index Scan using

"pk_inode_segments" on "inode_segments" "mv" (cost=0.00..11.16 rows=1
width=16) (actual time=0.053..0.062 rows=1 loops=503)"

" Index Cond:

((("st_ino")::bigint = ("fi"."st_ino_target")::bigint) AND
(("segment_index")::"text" = 'thumbnail.with_name'::"text

Show quoted text

...