explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3U3S

Settings
# exclusive inclusive rows x rows loops node
1. 0.492 212,485.695 ↑ 2.0 53 1

Sort (cost=4,863.53..4,863.80 rows=106 width=283) (actual time=212,485.674..212,485.695 rows=53 loops=1)

  • Sort Key: pp.city_name, (ordered_pps_partners_name_2(cu_1.create_pps_id)), pc.name, ((cu.create_date)::date)
  • Sort Method: quicksort Memory: 52kB
2.          

Initplan (forSort)

3. 0.014 0.063 ↑ 1.0 1 1

Aggregate (cost=1.25..1.26 rows=1 width=32) (actual time=0.062..0.063 rows=1 loops=1)

4. 0.049 0.049 ↓ 2.0 2 1

Seq Scan on users_nodes un (cost=0.00..1.24 rows=1 width=4) (actual time=0.048..0.049 rows=2 loops=1)

  • Filter: (user_uid = 'bc202eae-5827-11e8-b9aa-6f9198bc9a45'::uuid)
  • Rows Removed by Filter: 17
5. 1,965.305 212,485.140 ↑ 2.0 53 1

GroupAggregate (cost=4,827.44..4,858.71 rows=106 width=283) (actual time=209,958.705..212,485.140 rows=53 loops=1)

  • Group Key: cbh.clid_client_id, (ordered_pps_partners_name_2(cu_1.create_pps_id)), pp.path, u.last_name, u.first_name, pp.city_name, cu.create_date, pc.name
  • Filter: (((min(cbh.pay_date))::date >= '2019-07-24'::date) AND ((min(cbh.pay_date))::date <= '2019-08-06'::date))
  • Rows Removed by Filter: 2373
6. 9,524.749 210,519.835 ↓ 13,976.2 1,481,478 1

Sort (cost=4,827.44..4,827.70 rows=106 width=255) (actual time=209,103.711..210,519.835 rows=1,481,478 loops=1)

  • Sort Key: cbh.clid_client_id, (ordered_pps_partners_name_2(cu_1.create_pps_id)), pp.path, u.last_name, u.first_name, pp.city_name, cu.create_date, pc.name
  • Sort Method: external merge Disk: 401440kB
7. 194,130.479 200,995.086 ↓ 13,976.2 1,481,478 1

Nested Loop (cost=141.14..4,823.87 rows=106 width=255) (actual time=7.080..200,995.086 rows=1,481,478 loops=1)

8. 120.907 3,326.819 ↓ 1,060.2 49,828 1

Nested Loop (cost=140.71..1,837.31 rows=47 width=239) (actual time=2.161..3,326.819 rows=49,828 loops=1)

9. 131.102 3,006.600 ↓ 1,060.2 49,828 1

Nested Loop (cost=140.43..1,822.81 rows=47 width=198) (actual time=2.099..3,006.600 rows=49,828 loops=1)

  • Join Filter: (cu_1.user_id = cu.user_id)
10. 80.141 2,128.078 ↓ 1,060.2 49,828 1

Nested Loop (cost=140.01..1,797.02 rows=47 width=174) (actual time=2.033..2,128.078 rows=49,828 loops=1)

11. 49.603 403.613 ↓ 1,060.2 49,828 1

Nested Loop (cost=139.59..1,519.97 rows=47 width=140) (actual time=1.939..403.613 rows=49,828 loops=1)

12. 36.254 36.254 ↓ 1,406.0 1,406 1

Index Scan using pps_points_path_gist_idx on pps_points pp (cost=0.28..8.30 rows=1 width=132) (actual time=1.124..36.254 rows=1,406 loops=1)

  • Index Cond: ((path ~ '*.86706235|334453720|253075784|162964333|419364077|252368866|341324532|345178434|377752910|432257281|488712085|263159429|144444745|416405915|488711223|263159444|359974495|578233242|626314317|654493574|654493598|263159487|398959222|399193842|263159488|415921833|578234155|263159519|140661128|410538485|531350360|562489164|263159527|412435579|415295173|415484190|452313748|263159563|419364020|419364021|419364022|263159642|412422012|414502255|414502256|263160052|244717904|411622394|411622395|654436923|263160063|413479482|413479483|472419281|263160095|415016524|425533978|626314404|289439215|419932319|306314529|347364550|390146722|419404407|447336095|472416333|472419868|472429329|491433453|492730497|495564504|497986733|504884480|504884507|504884513|546426779|546426806|546426823|578292638|594166657|670740592|670741393|670808845|306314649|419404213|337090879|408999491|578279666|578279711|419408005|488634396|488635776|594168296|570071006|618673402|597262671|654493532|654493620|27302835|256585665|359695747|136398445|253075762|329523757|355002224|483944428|452313749|331271503|331271491|387170628|399714621|399714623|488634452|626311702|367672899|289440095|410631090|418882619|419267076|86706695|419267077|578286882|478663440|488634468|504882677|499948847|647381790|419404202.*'::lquery) AND (path ~ ((('*.'::text || $0) || '.*'::text))::lquery))
  • Filter: (NOT (path ~ '*.136398445.*'::lquery))
  • Rows Removed by Filter: 28
13. 241.832 317.756 ↑ 10.6 35 1,406

Bitmap Heap Scan on cupis_users cu_1 (cost=139.30..1,507.94 rows=372 width=16) (actual time=0.067..0.226 rows=35 loops=1,406)

  • Recheck Cond: (create_pps_id = pp.pps_id)
  • Heap Blocks: exact=38807
14. 75.924 75.924 ↑ 10.6 35 1,406

Bitmap Index Scan on cupis_users_create_pps_id_idx (cost=0.00..139.21 rows=372 width=0) (actual time=0.054..0.054 rows=35 loops=1,406)

  • Index Cond: (create_pps_id = pp.pps_id)
15. 1,644.324 1,644.324 ↑ 1.0 1 49,828

Index Scan using users_user_id_idx on users u (cost=0.43..5.88 rows=1 width=34) (actual time=0.030..0.033 rows=1 loops=49,828)

  • Index Cond: (user_id = cu_1.user_id)
16. 747.420 747.420 ↑ 1.0 1 49,828

Index Scan using cupis_users_pkey on cupis_users cu (cost=0.42..0.54 rows=1 width=24) (actual time=0.014..0.015 rows=1 loops=49,828)

  • Index Cond: (user_id = u.user_id)
17. 199.312 199.312 ↑ 1.0 1 49,828

Index Scan using pps_cashiers_i1 on pps_cashiers pc (cost=0.28..0.30 rows=1 width=57) (actual time=0.004..0.004 rows=1 loops=49,828)

  • Index Cond: (cashier_id = cu.create_cashier_id)
18. 3,537.788 3,537.788 ↑ 13.8 30 49,828

Index Scan using client_balance_history_clid_client_id_idx on client_balance_history cbh (cost=0.43..58.83 rows=415 width=16) (actual time=0.016..0.071 rows=30 loops=49,828)

  • Index Cond: (clid_client_id = cu_1.user_id)
  • Filter: (sum >= '2000'::double precision)
  • Rows Removed by Filter: 14
Planning time : 8.586 ms
Execution time : 212,559.569 ms