explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KoPb

Settings
# exclusive inclusive rows x rows loops node
1. 2,004.195 786,448.457 ↑ 2.0 53 1

GroupAggregate (cost=4,828.70..4,859.97 rows=106 width=283) (actual time=783,860.907..786,448.457 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
2.          

Initplan (forGroupAggregate)

3. 0.016 0.041 ↑ 1.0 1 1

Aggregate (cost=1.25..1.26 rows=1 width=32) (actual time=0.040..0.041 rows=1 loops=1)

4. 0.025 0.025 ↓ 2.0 2 1

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

  • Filter: (user_uid = 'bc202eae-5827-11e8-b9aa-6f9198bc9a45'::uuid)
  • Rows Removed by Filter: 17
5. 9,873.712 784,444.221 ↓ 13,976.2 1,481,478 1

Sort (cost=4,827.44..4,827.70 rows=106 width=255) (actual time=783,014.452..784,444.221 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
6. 201,146.179 774,570.509 ↓ 13,976.2 1,481,478 1

Nested Loop (cost=141.14..4,823.87 rows=106 width=255) (actual time=3.031..774,570.509 rows=1,481,478 loops=1)

7. 280.539 525,390.138 ↓ 1,060.2 49,828 1

Nested Loop (cost=140.71..1,837.31 rows=47 width=239) (actual time=1.179..525,390.138 rows=49,828 loops=1)

8. 473.149 524,461.835 ↓ 1,060.2 49,828 1

Nested Loop (cost=140.43..1,822.81 rows=47 width=198) (actual time=1.168..524,461.835 rows=49,828 loops=1)

  • Join Filter: (cu_1.user_id = cu.user_id)
9. 301.590 411,078.438 ↓ 1,060.2 49,828 1

Nested Loop (cost=140.01..1,797.02 rows=47 width=174) (actual time=1.154..411,078.438 rows=49,828 loops=1)

10. 119.442 48,626.944 ↓ 1,060.2 49,828 1

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

11. 2,182.614 2,182.614 ↓ 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=0.760..2,182.614 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
12. 24,046.818 46,324.888 ↑ 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=15.948..32.948 rows=35 loops=1,406)

  • Recheck Cond: (create_pps_id = pp.pps_id)
  • Heap Blocks: exact=38807
13. 22,278.070 22,278.070 ↑ 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=15.845..15.845 rows=35 loops=1,406)

  • Index Cond: (create_pps_id = pp.pps_id)
14. 362,149.904 362,149.904 ↑ 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=6.775..7.268 rows=1 loops=49,828)

  • Index Cond: (user_id = cu_1.user_id)
15. 112,910.248 112,910.248 ↑ 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=1.963..2.266 rows=1 loops=49,828)

  • Index Cond: (user_id = u.user_id)
16. 647.764 647.764 ↑ 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.012..0.013 rows=1 loops=49,828)

  • Index Cond: (cashier_id = cu.create_cashier_id)
17. 48,034.192 48,034.192 ↑ 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.676..0.964 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 : 5.907 ms
Execution time : 786,528.545 ms