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)