

EXPLIN ANALYZE QUERY PLAN WITH ORDER BY AND LIMIT 30

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1.14..649312.65 rows=30 width=1810) (actual time=3720.926..3721.481 rows=30 loops=1)
   ->  Nested Loop  (cost=1.14..110296384.59 rows=5096 width=1810) (actual time=3720.924..3721.472 rows=30 loops=1)
         ->  Nested Loop  (cost=0.71..110293650.27 rows=5208 width=1825) (actual time=3720.914..3721.316 rows=30 loops=1)
               ->  Index Scan using xxxxx_index on XXXX_item  (cost=0.42..110208665.39 rows=271414 width=1809) (actual time=1.502..3544.708 rows=125305 loops=1)
                     Filter: (((NOT deleted) OR (deleted IS NULL)) AND (SubPlan 6))
                     Rows Removed by Filter: 396691
                     SubPlan 6
                       ->  Index Only Scan using xxxx_pkey on cms_acl_permit acl_permit  (cost=194.66..202.72 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=521996)
                             Index Cond: ((xxxxx_id = xxxxx_item.acl_id) AND (xxxxx = 'browse'::text))
                             Filter: ((hashed SubPlan 3) OR ((xxxxxx_id = 'eeffa80a-2644-11e8-b467-0ed5f89f718b'::uuid) AND (hashed SubPlan 4)) OR (hashed SubPlan 5))
                             Rows Removed by Filter: 4
                             Heap Fetches: 599354
                             CTE xxxxx_ids
                               ->  Function Scan on unnest t  (cost=0.00..1.50 rows=100 width=32) (actual time=0.018..0.023 rows=6 loops=1)
                             CTE xxxxx_subq
                               ->  Index Only Scan using xxxxxx_key on xxxxxx_membership  (cost=0.42..123.57 rows=2874 width=16) (actual time=0.019..0.365 rows=2500 loops=1)
                                     Index Cond: (xxxxx_id = ANY ('{3edc1c1b-7402-4d16-b3e5-a1ba4e90d061,865033c7-a449-4c23-8d67-4c27bfdba20b,ae37ee44-0871-48be-9365-f6188191e587}':uuid[]))
                                     Heap Fetches: 0
                             SubPlan 3
                               ->  CTE Scan on xxxxx_ids  (cost=0.00..2.00 rows=100 width=16) (actual time=0.020..0.025 rows=6 loops=1)
                             SubPlan 4
                               ->  CTE Scan on xxxx_ids xxxx_ids_1  (cost=0.00..2.00 rows=100 width=16) (never executed)
                             SubPlan 5
                               ->  CTE Scan on xxxxx_subq  (cost=0.00..57.48 rows=2874 width=16) (actual time=0.019..0.831 rows=2500 loops=1)
               ->  Index Only Scan using xxxxxxxx_pkey on xxxxxx_table  (cost=0.29..0.30 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=125305)
                     Index Cond: (id = xxxx_item.id)
                     Heap Fetches: 0
         ->  Index Scan using xxxxxx_pkey on xxxxxx_table  (cost=0.42..0.52 rows=1 width=17) (actual time=0.005..0.005 rows=1 loops=30)
               Index Cond: (id = xxxx_item.id)
 Planning time: 1.407 ms
 Execution time: 3721.652 ms

EXPLAIN ANLAYZE QUERY PLAN WITH ORDER BY WITHOUT LIMIT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=2148137.73..2148150.47 rows=5096 width=1810) (actual time=166.679..167.340 rows=2500 loops=1)
   Sort Key: xxxx_item.name
   Sort Method: quicksort  Memory: 2632kB
   ->  Nested Loop  (cost=1.14..2143816.44 rows=5096 width=1810) (actual time=1.638..156.972 rows=2500 loops=1)
         Join Filter: (xxxxx_table.id = xxxxx_item.id)
         ->  Nested Loop  (cost=0.71..24721.30 rows=10415 width=33) (actual time=0.026..38.608 rows=10415 loops=1)
               ->  Index Only Scan using xxxxxx_pkey on xxxxxx_table  (cost=0.29..324.51 rows=10415 width=16) (actual time=0.016..1.375 rows=10415 loops=1)
                     Heap Fetches: 0
               ->  Index Scan using XXXXXX_pkey on xxxxx_table  (cost=0.42..2.33 rows=1 width=17) (actual time=0.003..0.003 rows=1 loops=10415)
                     Index Cond: (id = xxxxx_table.id)
         ->  Index Scan using xxxxx_item_pkey on xxxxx_item  (cost=0.42..203.45 rows=1 width=1809) (actual time=0.011..0.011 rows=0 loops=10415)
               Index Cond: (id = xxxxx_folder_table.id)
               Filter: (((NOT deleted) OR (deleted IS NULL)) AND (SubPlan 6))
               Rows Removed by Filter: 1
               SubPlan 6
                 ->  Index Only Scan using xxxxx_pkey on xxxxx_permit  (cost=194.66..202.72 rows=1 width=0) (actual time=0.007..0.007 rows=0 loops=10415)
                       Index Cond: ((acl_id = cms_item.acl_id) AND (permission = 'browse'::text))
                       Filter: ((hashed SubPlan 3) OR ((identity_id = 'eeffa80a-2644-11e8-b467-0ed5f89f718b'::uuid) AND (hashed SubPlan 4)) OR (hashed SubPlan 5))
                       Rows Removed by Filter: 4
                       Heap Fetches: 11897
                       CTE user_ids
                         ->  Function Scan on unnest t  (cost=0.00..1.50 rows=100 width=32) (actual time=0.010..0.013 rows=6 loops=1)
                       CTE xxxxx_subq
                         ->  Index Only Scan using xxxxx_key on xxxxxx_membership  (cost=0.42..123.57 rows=2874 width=16) (actual time=0.023..0.389 rows=2500 loops=1)
                               Index Cond: (xxxxxx_id = ANY ('{3edc1c1b-7402-4d16-b3e5-a1ba4e90d061,865033c7-a449-4c23-8d67-4c27bfdba20b,ae37ee44-0871-48be-9365-f6188191e587}'::uuid[]))
                               Heap Fetches: 0
                       SubPlan 3
                         ->  CTE Scan on xxxx_ids  (cost=0.00..2.00 rows=100 width=16) (actual time=0.012..0.017 rows=6 loops=1)
                       SubPlan 4
                         ->  CTE Scan on xxxx_ids xxxx_ids_1  (cost=0.00..2.00 rows=100 width=16) (never executed)
                       SubPlan 5
                         ->  CTE Scan on xxxxxxx_grp_subq  (cost=0.00..57.48 rows=2874 width=16) (actual time=0.023..0.869 rows=2500 loops=1)
 Planning time: 0.937 ms
 Execution time: 167.584 ms


EXPLAIN ANLAYZE QUERY PLAN WITH ORDER BY WITHOUT LIMIT 100
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2143847.84..2143848.09 rows=100 width=1810) (actual time=162.158..162.187 rows=100 loops=1)
   ->  Sort  (cost=2143847.84..2143860.58 rows=5096 width=1810) (actual time=162.156..162.182 rows=100 loops=1)
         Sort Key: xxxxx_item.name
         Sort Method: top-N heapsort  Memory: 126kB
         ->  Nested Loop  (cost=0.85..2143653.08 rows=5096 width=1810) (actual time=1.534..159.443 rows=2500 loops=1)
               Join Filter: (xXXXXXX.id = XXXXXX_item.id)
               ->  Nested Loop  (cost=0.42..24557.94 rows=10415 width=33) (actual time=0.020..41.570 rows=10415 loops=1)
                     ->  Seq Scan on xxxxxx_table  (cost=0.00..161.15 rows=10415 width=16) (actual time=0.004..1.022 rows=10415 loops=1)
                     ->  Index Scan using xxxxxx_pkey on xxxxx_folder_table  (cost=0.42..2.33 rows=1 width=17) (actual time=0.003..0.003 rows=1 loops=10415)
                           Index Cond: (id = xxxxxx_table.id)
               ->  Index Scan using cms_item_pkey on cms_item  (cost=0.42..203.45 rows=1 width=1809) (actual time=0.011..0.011 rows=0 loops=10415)
                     Index Cond: (id = cms_folder_table.id)
                     Filter: (((NOT deleted) OR (deleted IS NULL)) AND (SubPlan 6))
                     Rows Removed by Filter: 1
                     SubPlan 6
                       ->  Index Only Scan using xxxxxxx_pkey on xxxxx_permit xxxxx_permit  (cost=194.66..202.72 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=10415)
                             Index Cond: ((xxxxx_id = xxxxx_item.xxxx_id) AND (xxxxxn = 'browse'::text))
                             Filter: ((hashed SubPlan 3) OR ((xxxxxx_id = 'eeffa80a-2644-11e8-b467-0ed5f89f718b'::uuid) AND (hashed SubPlan 4)) OR (hashed SubPlan 5))
                             Rows Removed by Filter: 4
                             Heap Fetches: 11897
                             CTE user_ids
                               ->  Function Scan on unnest t  (cost=0.00..1.50 rows=100 width=32) (actual time=0.010..0.015 rows=6 loops=1)
                             CTE xxxxx_subq
                               ->  Index Only Scan using xxxxxx_key on xxxxx_membership  (cost=0.42..123.57 rows=2874 width=16) (actual time=0.022..0.379 rows=2500 loops=1)
                                     Index Cond: (xxxxx_id = ANY ('{3edc1c1b-7402-4d16-b3e5-a1ba4e90d061,865033c7-a449-4c23-8d67-4c27bfdba20b,ae37ee44-0871-48be-9365-f6188191e587}'::uuid[]))
                                     Heap Fetches: 0
                             SubPlan 3
                               ->  CTE Scan on user_ids  (cost=0.00..2.00 rows=100 width=16) (actual time=0.011..0.018 rows=6 loops=1)
                             SubPlan 4
                               ->  CTE Scan on user_ids user_ids_1  (cost=0.00..2.00 rows=100 width=16) (never executed)
                             SubPlan 5
                               ->  CTE Scan on user_grp_subq  (cost=0.00..57.48 rows=2874 width=16) (actual time=0.023..0.958 rows=2500 loops=1)
 Planning time: 0.993 ms
 Execution time: 162.349 ms
(34 rows)
 