Radial and Treemap views when combined with filters have very long running queries

Changing type to enhancement.

It's worth noting that uid 0 owns approx. 50% of the files on the filesystem in question (737,171 files out of 1,502,822). If you analyze the same query with uid 19198, owning only 88 files, you get the following result:

 Aggregate  (cost=6534.43..6534.44 rows=1 width=8)
   InitPlan
     ->  Index Scan using directory_info_pkey on directories  (cost=0.00..8.57 rows=1 width=4)
           Index Cond: (id = 65313)
     ->  Index Scan using directory_info_pkey on directories  (cost=0.00..8.57 rows=1 width=4)
           Index Cond: (id = 65313)
   ->  Nested Loop  (cost=145.12..6517.24 rows=18 width=8)
         ->  Bitmap Heap Scan on directories  (cost=145.12..1372.87 rows=274 width=4)
               Recheck Cond: ((lft >= $0) AND (lft <= $1))
               Filter: (server_id = 2)
               ->  Bitmap Index Scan on index_directories_on_lft  (cost=0.00..145.05 rows=452 width=0)
                     Index Cond: ((lft >= $0) AND (lft <= $1))
         ->  Index Scan using index_files_uid_directory_id on files  (cost=0.00..18.73 rows=4 width=12)
               Index Cond: ((files.uid = 19198) AND (files.directory_id = directories.id))
               Filter: ((name)::text ~~ '%'::text)
(15 rows)

This means that the sequential scan found in the query for uid 0 is probably justified - since half of the rows are subject to the query, might as well walk through all of them instead of using the index.

This can most likely be reduced to one query involving SUM by using SQL's GROUP BY and HAVING operators.

Also, dropping the path column from table files (which we can do once the sort-by-size for the flat view is committed) will probably help as the sequential scan should become faster.

The N queries submitted (for N directories in the "outer ring" of the radial diagram) CAN be combined into one statement along those lines:

 SELECT SUM(size) AS sum_size, lft 
   FROM files  
   JOIN directories ON files.directory_id = directories.id 
   WHERE (files.uid = 0) 
     AND directories.server_id=2 
     AND directories.level >= 
         ((SELECT level FROM directories WHERE id=123) + number_of_display_levels - 1) 
   GROUP BY directories.lft 
   HAVING directories.lft >= (SELECT lft FROM directories WHERE id=123)  
      AND directories.lft <= (SELECT rgt FROM directories WHERE id=123)

(The GROUP BY clause can probably be improved.)

This should speed up this part of the database interaction by factor N (but the results might need some post processing if the GROUP BY clause can't be optimized.)

The above optimization (including improved GROUP BY clause) is implemented in r1004. Please let me know if that helps.

Milestone Uncommited Work deleted