explain.depesz.com

PostgreSQL's explain analyze made readable

Result: v5ca

Settings
# exclusive inclusive rows x rows loops node
1. 0.228 1,889.890 ↑ 1.0 1 1

GroupAggregate (cost=22,347.29..22,347.39 rows=1 width=91) (actual time=1,889.890..1,889.890 rows=1 loops=1)

  • Output: (todo.id)::character varying, trans.label, count(DISTINCT item.id), todo.object_level, todo.sum, todo.id
  • Group Key: todo.id, trans.label
  • Buffers: shared hit=1,135,226
2. 0.336 1,889.662 ↓ 61.9 495 1

Sort (cost=22,347.29..22,347.31 rows=8 width=67) (actual time=1,889.626..1,889.662 rows=495 loops=1)

  • Output: trans.label, todo.id, item.id, todo.object_level, todo.sum
  • Sort Key: trans.label
  • Sort Method: quicksort Memory: 94kB
  • Buffers: shared hit=1,135,226
3. 2.532 1,889.326 ↓ 61.9 495 1

Hash Join (cost=2,252.08..22,347.17 rows=8 width=67) (actual time=191.972..1,889.326 rows=495 loops=1)

  • Output: trans.label, todo.id, item.id, todo.object_level, todo.sum
  • Inner Unique: true
  • Hash Cond: (debtor.principal_debtor_id = t_personal_field_ach.debtor_id)
  • Buffers: shared hit=1,135,226
4. 400.568 1,788.858 ↓ 1.9 7,856 1

Nested Loop (cost=1.82..20,044.22 rows=4,174 width=83) (actual time=0.438..1,788.858 rows=7,856 loops=1)

  • Output: todo.id, todo.object_level, todo.sum, debtor.principal_debtor_id, trans.label, item.id
  • Join Filter: ((debtor.id = statistics.debtor_id) AND (statistics.generation_date >= date(timezone((timezone.name)::text, now()))))
  • Rows Removed by Join Filter: 777,903
  • Buffers: shared hit=1,116,404
5. 4.613 115.654 ↓ 7.1 8,004 1

Nested Loop (cost=1.26..12,360.32 rows=1,135 width=130) (actual time=0.099..115.654 rows=8,004 loops=1)

  • Output: todo.id, todo.object_level, todo.sum, debtor.id, debtor.principal_debtor_id, trans.label, timezone.name, item.id, item.debtor_id
  • Buffers: shared hit=21,216
6. 0.643 84.217 ↓ 1.7 1,916 1

Nested Loop (cost=0.83..8,587.92 rows=1,102 width=98) (actual time=0.079..84.217 rows=1,916 loops=1)

  • Output: todo.id, todo.object_level, todo.sum, debtor.id, debtor.principal_debtor_id, trans.label, timezone.name
  • Join Filter: (todoorg.organisation_id = debtor.sub_organisation_id)
  • Buffers: shared hit=7,533
7. 0.003 0.054 ↑ 1.0 1 1

Nested Loop (cost=0.41..8.05 rows=1 width=98) (actual time=0.039..0.054 rows=1 loops=1)

  • Output: todo.id, todo.object_level, todo.sum, todoorg.organisation_id, trans.label, organisation.id, timezone.name
  • Inner Unique: true
  • Buffers: shared hit=8
8. 0.003 0.040 ↑ 1.0 1 1

Nested Loop (cost=0.14..6.00 rows=1 width=99) (actual time=0.027..0.040 rows=1 loops=1)

  • Output: todo.id, todo.object_level, todo.sum, todoorg.organisation_id, trans.label, organisation.id, organisation.timezone_id
  • Inner Unique: true
  • Join Filter: (todoorg.organisation_id = organisation.id)
  • Rows Removed by Join Filter: 3
  • Buffers: shared hit=5
9. 0.001 0.034 ↑ 1.0 1 1

Nested Loop (cost=0.14..4.91 rows=1 width=67) (actual time=0.022..0.034 rows=1 loops=1)

  • Output: todo.id, todo.object_level, todo.sum, todoorg.organisation_id, trans.label
  • Buffers: shared hit=4
10. 0.002 0.022 ↑ 1.0 1 1

Nested Loop (cost=0.14..3.54 rows=1 width=39) (actual time=0.015..0.022 rows=1 loops=1)

  • Output: todo.id, todo.object_level, todo.sum, todoorg.organisation_id
  • Buffers: shared hit=3
11. 0.010 0.010 ↑ 1.0 1 1

Index Scan using t_debtor_todo_list_pkey on bouyguestelecom.t_todo_list todo (cost=0.14..2.16 rows=1 width=23) (actual time=0.009..0.010 rows=1 loops=1)

  • Output: todo.id, todo.date_create, todo.date_update, todo.deleted, todo.code, todo.sum, todo.object_level, todo.todo_numeric_field_id, todo.organisation_id, todo.display_payer, todo.display_associate, todo.condition_rules, todo.module
  • Index Cond: (todo.id = 'a1b8f48f-b839-47d2-a0cf-197d6df16cba'::uuid)
  • Filter: ((todo.module)::text = 'COLLECTION'::text)
  • Buffers: shared hit=2
12. 0.010 0.010 ↑ 1.0 1 1

Seq Scan on bouyguestelecom.t_todo_organisation_list todoorg (cost=0.00..1.38 rows=1 width=32) (actual time=0.005..0.010 rows=1 loops=1)

  • Output: todoorg.organisation_id, todoorg.todo_id
  • Filter: (todoorg.todo_id = 'a1b8f48f-b839-47d2-a0cf-197d6df16cba'::uuid)
  • Rows Removed by Filter: 29
  • Buffers: shared hit=1
13. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on bouyguestelecom.t_todo_list_translation trans (cost=0.00..1.36 rows=1 width=44) (actual time=0.006..0.011 rows=1 loops=1)

  • Output: trans.id, trans.label, trans.id_language, trans.id_object
  • Filter: ((trans.id_object = 'a1b8f48f-b839-47d2-a0cf-197d6df16cba'::uuid) AND (trans.id_language = 'ff94ee3b-c08b-4902-a418-287be946c842'::uuid))
  • Rows Removed by Filter: 23
  • Buffers: shared hit=1
14. 0.003 0.003 ↑ 1.0 4 1

Seq Scan on bouyguestelecom.t_organisation organisation (cost=0.00..1.04 rows=4 width=32) (actual time=0.003..0.003 rows=4 loops=1)

  • Output: organisation.id, organisation.date_create, organisation.deleted, organisation.date_update, organisation.code, organisation.data_source, organisation.name, organisation.type, organisation.parent_organisation_id, organisation.id_logo, organisation.devise_id, organisation.country_id, organisation.broken_promise_period, organisation.root_organisation_id, organisation.feature_type, organisation.max_number_of_passwords, organisation.password_expiry_days, organisation.timezone_id, organisation.max_allowed_failed_login, organisation.user_session_timeout_minutes, organisation.partner_id_config, organisation.integrate_risk_from_file, organisation.portal_hyperlink_expiry, organisation.download_option
  • Buffers: shared hit=1
15. 0.011 0.011 ↑ 1.0 1 1

Index Scan using t_timezone_pkey on bouyguestelecom.t_timezone timezone (cost=0.28..2.04 rows=1 width=31) (actual time=0.011..0.011 rows=1 loops=1)

  • Output: timezone.id, timezone.date_create, timezone.date_update, timezone.deleted, timezone.name, timezone.region, timezone.raw_offset, timezone.hour_offset, timezone.minute_offset, timezone.time_adjustment_display
  • Index Cond: (timezone.id = organisation.timezone_id)
  • Buffers: shared hit=3
16. 83.520 83.520 ↓ 1.4 1,916 1

Index Scan using t_debtor_portfolio_id45_idx on bouyguestelecom.t_debtor debtor (cost=0.42..8,562.65 rows=1,378 width=48) (actual time=0.038..83.520 rows=1,916 loops=1)

  • Output: debtor.id, debtor.date_create, debtor.date_update, debtor.deleted, debtor.client_number, debtor.debtor_number, debtor.debtor_reference, debtor.payer_reference, debtor.business_unit_identifier, debtor.activity_code, debtor.debtor_name, debtor.telephone, debtor.language_code, debtor.sub_organisation_id, debtor.payment_type_id, debtor.commercial_id, debtor.payment_condition_id, debtor.portfolio_id, debtor.sum_orders, debtor.sum_delivery, debtor.sum_fae, debtor.chronological_strategy_id, debtor.sequential_strategy_id, debtor.parent_id, debtor.payer_debtor_id, debtor.multi_user_recovery, debtor.new_debtor, debtor.pause_strategy, debtor.type, debtor.risk_code, debtor.risk_id, debtor.customer_status, debtor.principal_debtor_id, debtor.effective_level, debtor.sounding_execution_debtor_id, debtor.risk_number, debtor.payer_profile, debtor.collection_risk_profile, debtor.branch_management, debtor.branch_id
  • Index Cond: (debtor.portfolio_id = ANY ('{922411db-bc67-4a13-af5e-7c49b69dcfe5,a9b381db-5e46-4a2b-a82b-787b9cdba3d0,ad2f1ffe-b275-473b-8272-0504f1bc7f16,b69bdbed-e59f-40d9-a7c7-469861685690,c0098b75-1ced-4e29-b5ea-695f41c26098,c3d30147-a189-41b3-8e15-ee4bc7da9581,cd570f0a-2de6-4a25-a117-403779861c79,e04ff510-04d5-45ad-9dbd-03f1359e64ef,ebdacdf0-a153-4887-ba60-045aaf9ed2cd,fc1b1fee-973c-4feb-a193-6ab6e1ebd008,00228991-d025-4c15-90c4-e75154f7b1f3,11c1f793-79b5-4a66-ba0f-260dc04e900b,185dd045-4bc3-4117-a577-567faae4764e,36b021ce-11ef-4736-92df-9deca74c2c65,3b620d65-9489-40ee-9aa7-0e75525f51e9,46e53466-13f0-4d6e-be8d-873373043528,55b719c2-20c9-49a5-a73a-e45dcdd9ed68,55d9e1fd-e25c-4e50-89f2-e243a1295c28,7007b3cb-9339-40ee-a874-4175d8f58ed2,72c794db-fc84-4460-9bfe-1c4795520c57,793e75e2-1006-4e1e-9363-a581dd52c45b}'::uuid[]))
  • Filter: ((NOT debtor.deleted) AND (debtor.effective_level = 3))
  • Rows Removed by Filter: 158,167
  • Buffers: shared hit=7,525
17. 26.824 26.824 ↓ 2.0 4 1,916

Index Scan using t_item_debtor_id_idx on bouyguestelecom.t_item item (cost=0.43..3.40 rows=2 width=32) (actual time=0.009..0.014 rows=4 loops=1,916)

  • Output: item.id, item.debtnum, item.cliprikey, item.debtor_reference_number, item.item_technical_key, item.item_invoice_number, item.item_purchase_order_number, item.item_journal_code, item.item_docdate, item.item_duedate, item.item_original_amount_debtor_currency, item.item_balance_amount_debtor_currency, item.currency_code, item.item_original_amount_oganisation_currency, item.item_balance_amount_organisation_currency, item.item_url, item.item_purchase_order_url, item.item_type, item.integration_date, item.modification_date, item.partial_payment_code, item.debtor_id, item.date_create, item.date_update, item.deleted, item.is_active, item.collected_amt, item.remaining_amt, item.overdue_amt, item.item_payment_code, item.item_payment_date, item.settlement_amount, item.credit_note_amount, item.miscellaneous_amount, item.item_document_type_id, item.organisation_id, item.item_commercial_id, item.status_code_id, item.current_sounding_execution_id, item.transient_type, item.lead_invoice, item.current_collection_level, item.current_collection_date, item.next_collection_level, item.next_collection_date, item.dispute_amount, item.associate_debtor_id, item.multi_user_recovery, item.new_item, item.payer_debtor_id, item.next_collection_date_no_limit, item.client_doctype, item.request_id, item.reject_reason, item.initial_collection_level, item.initial_collection_date, item.delay, item.branch_reference_number
  • Index Cond: ((item.debtor_id = debtor.id) AND ((item.item_type)::text = 'OPEN_ITEM'::text))
  • Buffers: shared hit=13,683
18. 1,272.636 1,272.636 ↓ 8.9 98 8,004

Index Scan using t_debtor_st_debtor_id47_idx on bouyguestelecom.t_debtor_statistics statistics (cost=0.56..6.52 rows=11 width=20) (actual time=0.030..0.159 rows=98 loops=8,004)

  • Output: statistics.id, statistics.date_create, statistics.deleted, statistics.date_update, statistics.debtor_id, statistics.organisation_id, statistics.total_overdue, statistics.total_not_due, statistics.global_outstanding, statistics.post_dated_payment, statistics.financial_amount, statistics.sum_orders, statistics.sum_delivery, statistics.sum_fae, statistics.global_amount, statistics.numeric_value01, statistics.numeric_value02, statistics.numeric_value03, statistics.numeric_value04, statistics.numeric_value05, statistics.numeric_value06, statistics.numeric_value07, statistics.numeric_value08, statistics.numeric_value09, statistics.numeric_value10, statistics.numeric_value11, statistics.numeric_value12, statistics.numeric_value13, statistics.numeric_value14, statistics.numeric_value15, statistics.numeric_value16, statistics.numeric_value17, statistics.numeric_value18, statistics.numeric_value19, statistics.numeric_value20, statistics.numeric_value21, statistics.numeric_value22, statistics.numeric_value23, statistics.numeric_value24, statistics.numeric_value25, statistics.numeric_value26, statistics.numeric_value27, statistics.numeric_value28, statistics.numeric_value29, statistics.numeric_value30, statistics.numeric_value31, statistics.numeric_value32, statistics.numeric_value33, statistics.numeric_value34, statistics.numeric_value35, statistics.numeric_value36, statistics.numeric_value37, statistics.numeric_value38, statistics.numeric_value39, statistics.numeric_value40, statistics.numeric_value41, statistics.numeric_value42, statistics.numeric_value43, statistics.numeric_value44, statistics.numeric_value45, statistics.numeric_value46, statistics.numeric_value47, statistics.numeric_value48, statistics.numeric_value49, statistics.numeric_value50, statistics.generation_date, statistics.type_statistic, statistics.generation_timestamp, statistics.undisputed_overdue_outstanding, statistics.turnover_12_months, statistics.turnover_daily, statistics.turnover_dso, statistics.turnover_bpdso, statistics.turnover_add, statistics.add_6_months
  • Index Cond: (statistics.debtor_id = item.debtor_id)
  • Filter: (statistics.undisputed_overdue_outstanding >= '10'::numeric)
  • Rows Removed by Filter: 33
  • Buffers: shared hit=1,095,188
19. 4.526 97.936 ↓ 63.8 19,916 1

Hash (cost=2,246.36..2,246.36 rows=312 width=16) (actual time=97.936..97.936 rows=19,916 loops=1)

  • Output: t_personal_field_ach.debtor_id
  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,190kB
  • Buffers: shared hit=18,822
20. 46.349 93.410 ↓ 63.8 19,916 1

Bitmap Heap Scan on bouyguestelecom.t_personal_field_ach (cost=1,928.90..2,246.36 rows=312 width=16) (actual time=50.421..93.410 rows=19,916 loops=1)

  • Output: t_personal_field_ach.debtor_id
  • Recheck Cond: ((immutable_unaccent('unaccent'::regdictionary, (t_personal_field_ach.text_value)::text) ~~* 'o'::text) AND (t_personal_field_ach.personal_field_id = 'bff33caf-b011-4513-a40f-fe6ce0af6307'::uuid))
  • Heap Blocks: exact=18,162
  • Buffers: shared hit=18,822
21. 1.871 47.061 ↓ 0.0 0 1

BitmapAnd (cost=1,928.90..1,928.90 rows=312 width=0) (actual time=47.061..47.061 rows=0 loops=1)

  • Buffers: shared hit=660
22. 6.441 6.441 ↓ 1.2 21,069 1

Bitmap Index Scan on trgm_idx_pf_ach_text_value (cost=0.00..149.24 rows=18,166 width=0) (actual time=6.441..6.441 rows=21,069 loops=1)

  • Index Cond: (immutable_unaccent('unaccent'::regdictionary, (t_personal_field_ach.text_value)::text) ~~* 'o'::text)
  • Buffers: shared hit=44
23. 38.749 38.749 ↓ 1.0 160,083 1

Bitmap Index Scan on t_personal__personal_field_122_idx (cost=0.00..1,779.25 rows=156,642 width=0) (actual time=38.749..38.749 rows=160,083 loops=1)

  • Index Cond: (t_personal_field_ach.personal_field_id = 'bff33caf-b011-4513-a40f-fe6ce0af6307'::uuid)
  • Buffers: shared hit=616
Planning time : 6.896 ms
Execution time : 1,890.090 ms