Large Binary Columns - Slow Query - Mailing list pgsql-admin

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.

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