explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ppar

Settings
# exclusive inclusive rows x rows loops node
1. 190.157 27,344.629 ↓ 1.0 8,104 1

Nested Loop (cost=1.00..144,916,108.94 rows=8,097 width=2,001) (actual time=37.715..27,344.629 rows=8,104 loops=1)

  • Buffers: shared hit=13,899,762 read=68,589
  • I/O Timings: read=5,134.838
2. 14.048 230.934 ↓ 1.0 8,104 1

Nested Loop Left Join (cost=0.85..8,886.19 rows=8,097 width=91) (actual time=0.496..230.934 rows=8,104 loops=1)

  • Buffers: shared hit=53,875 read=1,211
  • I/O Timings: read=129.487
3. 16.697 127.742 ↓ 1.0 8,104 1

Nested Loop Left Join (cost=0.57..4,916.61 rows=8,097 width=85) (actual time=0.487..127.742 rows=8,104 loops=1)

  • Buffers: shared hit=30,363 read=734
  • I/O Timings: read=67.907
4. 78.629 78.629 ↓ 1.0 8,104 1

Index Scan using pk_cbp_uss_user_seller on jst_est_establishment est (cost=0.28..1,431.41 rows=8,097 width=77) (actual time=0.471..78.629 rows=8,104 loops=1)

  • Buffers: shared hit=6,916 read=589
  • I/O Timings: read=56.393
5. 32.416 32.416 ↑ 1.0 1 8,104

Index Only Scan using pk_jst_use_user on jst_use_user juu (cost=0.29..0.43 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=8,104)

  • Index Cond: (use_user_id = est.est_use_reseller_id)
  • Heap Fetches: 7,864
  • Buffers: shared hit=23,447 read=145
  • I/O Timings: read=11.514
6. 89.144 89.144 ↑ 1.0 1 8,104

Index Scan using pk_cbp_add_address on cbp_add_address caa (cost=0.29..0.49 rows=1 width=22) (actual time=0.011..0.011 rows=1 loops=8,104)

  • Index Cond: (est.est_add_address_id = add_address_id)
  • Buffers: shared hit=23,512 read=477
  • I/O Timings: read=61.580
7. 16.208 16.208 ↑ 1.0 1 8,104

Index Scan using pk_cbp_bua_business_activity on cbp_bua_business_activity cbba (cost=0.14..0.16 rows=1 width=47) (actual time=0.002..0.002 rows=1 loops=8,104)

  • Index Cond: (bua_business_activity_id = est.est_bua_business_activity_id)
  • Buffers: shared hit=16,207 read=1
  • I/O Timings: read=0.492
8.          

SubPlan (for Nested Loop)

9. 64.832 9,708.592 ↑ 1.0 1 8,104

Aggregate (cost=3,509.53..3,509.55 rows=1 width=32) (actual time=1.198..1.198 rows=1 loops=8,104)

  • Buffers: shared hit=2,584,043 read=65,809
  • I/O Timings: read=4,940.092
10. 9,643.760 9,643.760 ↑ 15.9 16 8,104

Index Scan using ix_cbp_tra_transaction_tra_est_establishment_id on cbp_tra_transaction (cost=0.43..3,508.89 rows=255 width=5) (actual time=0.452..1.190 rows=16 loops=8,104)

  • Index Cond: (tra_est_establishment_id = est.est_establishment_id)
  • Filter: ((tra_status = ANY ('{1,5}'::integer[])) AND ((tra_dcol)::text = 'SalePOS'::text) AND (((tra_date_apresentation - '03:00:00'::interval))::date >= date_trunc('month'::text, ((now())::date)::timestamp with time zone)))
  • Rows Removed by Filter: 351
  • Buffers: shared hit=2,584,043 read=65,809
  • I/O Timings: read=4,940.092
11. 24.312 2,496.032 ↑ 1.0 1 8,104

Aggregate (cost=137.21..137.22 rows=1 width=32) (actual time=0.308..0.308 rows=1 loops=8,104)

  • Buffers: shared hit=239,411 read=29
  • I/O Timings: read=4.932
12. 24.312 2,471.720 ↓ 0.0 0 8,104

Bitmap Heap Scan on cbp_tra_transaction cbp_tra_transaction_1 (cost=133.17..137.20 rows=1 width=5) (actual time=0.305..0.305 rows=0 loops=8,104)

  • Recheck Cond: ((tra_est_establishment_id = est.est_establishment_id) AND ((tra_dcol)::text = 'SaleWeb'::text))
  • Filter: ((tra_status = ANY ('{1,5}'::integer[])) AND (((tra_date_apresentation - '03:00:00'::interval))::date >= date_trunc('month'::text, ((now())::date)::timestamp with time zone)))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=2,215
  • Buffers: shared hit=239,411 read=29
  • I/O Timings: read=4.932
13. 174.440 2,447.408 ↓ 0.0 0 8,104

BitmapAnd (cost=133.17..133.17 rows=1 width=0) (actual time=0.302..0.302 rows=0 loops=8,104)

  • Buffers: shared hit=237,196 read=29
  • I/O Timings: read=4.932
14. 380.888 380.888 ↑ 2.6 368 8,104

Bitmap Index Scan on ix_cbp_tra_transaction_tra_est_establishment_id (cost=0.00..19.48 rows=940 width=0) (actual time=0.047..0.047 rows=368 loops=8,104)

  • Index Cond: (tra_est_establishment_id = est.est_establishment_id)
  • Buffers: shared hit=32,485
15. 1,892.080 1,892.080 ↑ 1.0 2,724 7,060

Bitmap Index Scan on ix_cbp_tra_transaction (cost=0.00..113.44 rows=2,784 width=0) (actual time=0.268..0.268 rows=2,724 loops=7,060)

  • Index Cond: ((tra_dcol)::text = 'SaleWeb'::text)
  • Buffers: shared hit=204,711 read=29
  • I/O Timings: read=4.932
16. 24.312 2,633.800 ↑ 1.0 1 8,104

Aggregate (cost=3,508.97..3,508.98 rows=1 width=32) (actual time=0.325..0.325 rows=1 loops=8,104)

  • Buffers: shared hit=2,649,852
17. 2,609.488 2,609.488 ↑ 7.0 4 8,104

Index Scan using ix_cbp_tra_transaction_tra_est_establishment_id on cbp_tra_transaction cbp_tra_transaction_2 (cost=0.43..3,508.89 rows=28 width=5) (actual time=0.288..0.322 rows=4 loops=8,104)

  • Index Cond: (tra_est_establishment_id = est.est_establishment_id)
  • Filter: ((tra_status = ANY ('{1,5}'::integer[])) AND ((tra_dcol)::text = 'SaleTEF'::text) AND (((tra_date_apresentation - '03:00:00'::interval))::date >= date_trunc('month'::text, ((now())::date)::timestamp with time zone)))
  • Rows Removed by Filter: 363
  • Buffers: shared hit=2,649,852
18. 729.360 3,452.304 ↑ 1.0 1 8,104

Aggregate (cost=3,494.36..3,494.37 rows=1 width=32) (actual time=0.426..0.426 rows=1 loops=8,104)

  • Buffers: shared hit=2,649,852
19. 2,722.944 2,722.944 ↑ 2.6 296 8,104

Index Scan using ix_cbp_tra_transaction_tra_est_establishment_id on cbp_tra_transaction cbp_tra_transaction_3 (cost=0.43..3,492.44 rows=764 width=5) (actual time=0.032..0.336 rows=296 loops=8,104)

  • Index Cond: (tra_est_establishment_id = est.est_establishment_id)
  • Filter: ((tra_status = ANY ('{1,5}'::integer[])) AND ((tra_dcol)::text = 'SalePOS'::text))
  • Rows Removed by Filter: 71
  • Buffers: shared hit=2,649,852
20. 24.312 2,455.512 ↑ 1.0 1 8,104

Aggregate (cost=137.19..137.21 rows=1 width=32) (actual time=0.303..0.303 rows=1 loops=8,104)

  • Buffers: shared hit=239,440
21. 24.312 2,431.200 ↓ 0.0 0 8,104

Bitmap Heap Scan on cbp_tra_transaction cbp_tra_transaction_4 (cost=133.17..137.18 rows=1 width=5) (actual time=0.299..0.300 rows=0 loops=8,104)

  • Recheck Cond: ((tra_est_establishment_id = est.est_establishment_id) AND ((tra_dcol)::text = 'SaleWeb'::text))
  • Filter: (tra_status = 1)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=2,215
  • Buffers: shared hit=239,440
22. 170.264 2,406.888 ↓ 0.0 0 8,104

BitmapAnd (cost=133.17..133.17 rows=1 width=0) (actual time=0.297..0.297 rows=0 loops=8,104)

  • Buffers: shared hit=237,225
23. 372.784 372.784 ↑ 2.6 368 8,104

Bitmap Index Scan on ix_cbp_tra_transaction_tra_est_establishment_id (cost=0.00..19.48 rows=940 width=0) (actual time=0.046..0.046 rows=368 loops=8,104)

  • Index Cond: (tra_est_establishment_id = est.est_establishment_id)
  • Buffers: shared hit=32,485
24. 1,863.840 1,863.840 ↑ 1.0 2,724 7,060

Bitmap Index Scan on ix_cbp_tra_transaction (cost=0.00..113.44 rows=2,784 width=0) (actual time=0.264..0.264 rows=2,724 loops=7,060)

  • Index Cond: ((tra_dcol)::text = 'SaleWeb'::text)
  • Buffers: shared hit=204,740
25. 89.144 2,390.680 ↑ 1.0 1 8,104

Aggregate (cost=3,492.65..3,492.67 rows=1 width=32) (actual time=0.295..0.295 rows=1 loops=8,104)

  • Buffers: shared hit=2,649,852
26. 2,301.536 2,301.536 ↑ 2.4 34 8,104

Index Scan using ix_cbp_tra_transaction_tra_est_establishment_id on cbp_tra_transaction cbp_tra_transaction_5 (cost=0.43..3,492.44 rows=83 width=5) (actual time=0.241..0.284 rows=34 loops=8,104)

  • Index Cond: (tra_est_establishment_id = est.est_establishment_id)
  • Filter: ((tra_status = ANY ('{1,5}'::integer[])) AND ((tra_dcol)::text = 'SaleTEF'::text))
  • Rows Removed by Filter: 333
  • Buffers: shared hit=2,649,852
27. 802.296 3,460.408 ↑ 1.0 1 8,104

Aggregate (cost=3,492.22..3,492.24 rows=1 width=32) (actual time=0.426..0.427 rows=1 loops=8,104)

  • Buffers: shared hit=2,649,852
28. 2,658.112 2,658.112 ↑ 2.6 331 8,104

Index Scan using ix_cbp_tra_transaction_tra_est_establishment_id on cbp_tra_transaction cbp_tra_transaction_6 (cost=0.43..3,490.09 rows=849 width=5) (actual time=0.004..0.328 rows=331 loops=8,104)

  • Index Cond: (tra_est_establishment_id = est.est_establishment_id)
  • Filter: (tra_status = ANY ('{1,5}'::integer[]))
  • Rows Removed by Filter: 36
  • Buffers: shared hit=2,649,852
29. 16.448 48.624 ↓ 0.0 0 8,104

Nested Loop (cost=0.43..16.53 rows=1 width=16) (actual time=0.006..0.006 rows=0 loops=8,104)

  • Buffers: shared hit=28,268 read=5
  • I/O Timings: read=0.028
30. 24.312 24.312 ↑ 1.0 1 8,104

Index Scan using pk_jst_use_user on jst_use_user (cost=0.29..8.30 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=8,104)

  • Index Cond: (est.est_use_reseller_id = use_user_id)
  • Buffers: shared hit=23,939 read=2
  • I/O Timings: read=0.009
31. 7.864 7.864 ↓ 0.0 0 7,864

Index Scan using pk_cbp_usb_user_backoffice on cbp_usb_user_backoffice cuub (cost=0.14..8.16 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=7,864)

  • Index Cond: (usb_user_backoffice_id = jst_use_user.use_usb_specialist_id)
  • Buffers: shared hit=4,329 read=3
  • I/O Timings: read=0.019
32. 18.294 41.566 ↑ 1.0 1 5,938

Nested Loop (cost=0.43..16.53 rows=1 width=16) (actual time=0.006..0.007 rows=1 loops=5,938)

  • Buffers: shared hit=28,804 read=2
  • I/O Timings: read=0.545
33. 11.876 11.876 ↑ 1.0 1 5,938

Index Scan using pk_jst_use_user on jst_use_user jst_use_user_1 (cost=0.29..8.30 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=5,938)

  • Index Cond: (est.est_use_reseller_id = use_user_id)
  • Buffers: shared hit=17,432
34. 11.396 11.396 ↑ 1.0 1 5,698

Index Scan using pk_cbp_usb_user_backoffice on cbp_usb_user_backoffice cuub_1 (cost=0.14..8.16 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=5,698)

  • Index Cond: (usb_user_backoffice_id = jst_use_user_1.use_usb_analyst_id)
  • Buffers: shared hit=11,372 read=2
  • I/O Timings: read=0.545
35. 0.240 0.502 ↓ 0.0 0 251

Nested Loop (cost=0.43..16.53 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=251)

  • Buffers: shared hit=33
36. 0.251 0.251 ↓ 0.0 0 251

Index Scan using pk_jst_use_user on jst_use_user jst_use_user_2 (cost=0.29..8.30 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=251)

  • Index Cond: (est.est_use_reseller_id = use_user_id)
  • Buffers: shared hit=33
37. 0.011 0.011 ↓ 0.0 0 11

Index Scan using pk_cbp_usb_user_backoffice on cbp_usb_user_backoffice cuub_2 (cost=0.14..8.16 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=11)

  • Index Cond: (usb_user_backoffice_id = jst_use_user_2.use_usb_analyst_id)
38. 0.502 0.502 ↓ 0.0 0 251

Nested Loop (cost=0.43..16.53 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=251)

  • Buffers: shared hit=33
39. 0.000 0.000 ↓ 0.0 0 251

Index Scan using pk_jst_use_user on jst_use_user jst_use_user_3 (cost=0.29..8.30 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=251)

  • Index Cond: (est.est_use_reseller_id = use_user_id)
  • Buffers: shared hit=33
40. 0.000 0.000 ↓ 0.0 0 11

Index Scan using pk_cbp_usb_user_backoffice on cbp_usb_user_backoffice cuub_3 (cost=0.14..8.16 rows=1 width=24) (actual time=0.000..0.000 rows=0 loops=11)

  • Index Cond: (usb_user_backoffice_id = jst_use_user_3.use_usb_specialist_id)
41. 16.448 40.520 ↑ 1.0 1 8,104

Nested Loop (cost=0.43..16.53 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=8,104)

  • Buffers: shared hit=39,645
42. 16.208 16.208 ↑ 1.0 1 8,104

Index Scan using pk_jst_use_user on jst_use_user jst_use_user_4 (cost=0.29..8.30 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=8,104)

  • Index Cond: (est.est_use_reseller_id = use_user_id)
  • Buffers: shared hit=23,941
43. 7.864 7.864 ↑ 1.0 1 7,864

Index Scan using pk_cbp_usb_user_backoffice on cbp_usb_user_backoffice cuub_4 (cost=0.14..8.16 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=7,864)

  • Index Cond: (usb_user_backoffice_id = jst_use_user_4.use_usb_analyst_id)
  • Buffers: shared hit=15,704
44. 16.208 32.416 ↓ 0.0 0 8,104

Nested Loop (cost=0.43..16.53 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=8,104)

  • Buffers: shared hit=28,273
45. 16.208 16.208 ↑ 1.0 1 8,104

Index Scan using pk_jst_use_user on jst_use_user jst_use_user_5 (cost=0.29..8.30 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=8,104)

  • Index Cond: (est.est_use_reseller_id = use_user_id)
  • Buffers: shared hit=23,941
46. 0.000 0.000 ↓ 0.0 0 7,864

Index Scan using pk_cbp_usb_user_backoffice on cbp_usb_user_backoffice cuub_5 (cost=0.14..8.16 rows=1 width=24) (actual time=0.000..0.000 rows=0 loops=7,864)

  • Index Cond: (usb_user_backoffice_id = jst_use_user_5.use_usb_specialist_id)
  • Buffers: shared hit=4,332
47. 0.000 0.000 ↓ 0.0 0 8,104

Index Scan using pk_cbp_usb_user_backoffice on cbp_usb_user_backoffice (cost=0.14..8.16 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=8,104)

  • Index Cond: (usb_user_backoffice_id = est.est_usb_executive_id)
  • Buffers: shared hit=535 read=1
  • I/O Timings: read=0.006
48. 16.208 145.872 ↑ 1.0 1 8,104

Aggregate (cost=16.61..16.62 rows=1 width=8) (actual time=0.018..0.018 rows=1 loops=8,104)

  • Buffers: shared hit=41,780 read=1,523
  • I/O Timings: read=59.206
49. 16.048 129.664 ↑ 1.0 1 8,104

Nested Loop (cost=0.57..16.61 rows=1 width=4) (actual time=0.013..0.016 rows=1 loops=8,104)

  • Buffers: shared hit=41,780 read=1,523
  • I/O Timings: read=59.206
50. 72.936 72.936 ↑ 1.0 1 8,104

Index Scan using ix_jst_ure_user_request_ure_est_establishment_id on jst_ure_user_request (cost=0.29..8.30 rows=1 width=8) (actual time=0.007..0.009 rows=1 loops=8,104)

  • Index Cond: (ure_est_establishment_id = est.est_establishment_id)
  • Buffers: shared hit=22,526 read=421
  • I/O Timings: read=44.687
51. 40.680 40.680 ↑ 1.0 1 6,780

Index Scan using pk_jst_pro_product_pro_product_id on jst_pro_product jpp (cost=0.29..8.30 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=6,780)

  • Index Cond: (pro_product_id = jst_ure_user_request.ure_pro_product_id)
  • Buffers: shared hit=19,254 read=1,102
  • I/O Timings: read=14.519
Planning time : 60.845 ms
Execution time : 27,348.701 ms