explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2Wgl

Settings
# exclusive inclusive rows x rows loops node
1. 0.225 923.489 ↑ 1.0 1 1

GroupAggregate (cost=34,503.91..34,504.00 rows=1 width=91) (actual time=923.489..923.489 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=263,726 read=43, temp read=5 written=5
2. 1.669 923.264 ↓ 61.9 495 1

Sort (cost=34,503.91..34,503.93 rows=8 width=67) (actual time=923.215..923.264 rows=495 loops=1)

  • Output: trans.label, todo.id, item.id, todo.object_level, todo.sum
  • Sort Key: trans.label
  • Sort Method: external sort Disk: 40kB
  • Buffers: shared hit=263,726 read=43, temp read=5 written=5
3. 10.103 921.595 ↓ 61.9 495 1

Nested Loop (cost=1,930.73..34,503.79 rows=8 width=67) (actual time=29.460..921.595 rows=495 loops=1)

  • Output: trans.label, todo.id, item.id, todo.object_level, todo.sum
  • Buffers: shared hit=263,726 read=43
4. 524.946 553.004 ↓ 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=28.235..553.004 rows=19,916 loops=1)

  • Output: t_personal_field_ach.id, t_personal_field_ach.date_create, t_personal_field_ach.date_update, t_personal_field_ach.deleted, t_personal_field_ach.clientnum, t_personal_field_ach.debtor_reference_number, t_personal_field_ach.integration_personal_field_code, t_personal_field_ach.debtor_id, t_personal_field_ach.date_value, t_personal_field_ach.monetary_value, t_personal_field_ach.text_multi_line_value, t_personal_field_ach.numerical_value, t_personal_field_ach.list_value, t_personal_field_ach.text_value, t_personal_field_ach.lib_discriminator_personal_field, t_personal_field_ach.personal_field_id, t_personal_field_ach.contact_login, t_personal_field_ach.debtor_number, t_personal_field_ach.is_blocking_situation
  • 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))
  • Rows Removed by Index Recheck: 731,507
  • Heap Blocks: exact=428 lossy=18,401
  • Buffers: shared hit=19,446 read=43
5. 0.403 28.058 ↓ 0.0 0 1

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

  • Buffers: shared hit=660
6. 5.832 5.832 ↓ 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=5.832..5.832 rows=21,069 loops=1)

  • Index Cond: (immutable_unaccent('unaccent'::regdictionary, (t_personal_field_ach.text_value)::text) ~~* 'o'::text)
  • Buffers: shared hit=44
7. 21.823 21.823 ↓ 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=21.823..21.823 rows=160,083 loops=1)

  • Index Cond: (t_personal_field_ach.personal_field_id = 'bff33caf-b011-4513-a40f-fe6ce0af6307'::uuid)
  • Buffers: shared hit=616
8. 19.916 358.488 ↓ 0.0 0 19,916

Nested Loop (cost=1.83..103.38 rows=1 width=83) (actual time=0.018..0.018 rows=0 loops=19,916)

  • Output: todo.id, todo.object_level, todo.sum, debtor.principal_debtor_id, trans.label, item.id
  • Join Filter: (debtor.sub_organisation_id = todoorg.organisation_id)
  • Buffers: shared hit=244,280
9. 19.916 219.076 ↑ 1.0 1 19,916

Nested Loop (cost=0.41..8.30 rows=1 width=98) (actual time=0.008..0.011 rows=1 loops=19,916)

  • Output: todo.id, todo.object_level, todo.sum, todoorg.organisation_id, trans.label, organisation.id, timezone.name
  • Inner Unique: true
  • Buffers: shared hit=159,328
10. 0.000 179.244 ↑ 1.0 1 19,916

Nested Loop (cost=0.14..6.00 rows=1 width=99) (actual time=0.007..0.009 rows=1 loops=19,916)

  • 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=99,580
11. 0.000 159.328 ↑ 1.0 1 19,916

Nested Loop (cost=0.14..4.91 rows=1 width=67) (actual time=0.005..0.008 rows=1 loops=19,916)

  • Output: todo.id, todo.object_level, todo.sum, todoorg.organisation_id, trans.label
  • Buffers: shared hit=79,664
12. 19.916 99.580 ↑ 1.0 1 19,916

Nested Loop (cost=0.14..3.54 rows=1 width=39) (actual time=0.003..0.005 rows=1 loops=19,916)

  • Output: todo.id, todo.object_level, todo.sum, todoorg.organisation_id
  • Buffers: shared hit=59,748
13. 19.916 19.916 ↑ 1.0 1 19,916

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.001..0.001 rows=1 loops=19,916)

  • 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=39,832
14. 59.748 59.748 ↑ 1.0 1 19,916

Seq Scan on bouyguestelecom.t_todo_organisation_list todoorg (cost=0.00..1.38 rows=1 width=32) (actual time=0.001..0.003 rows=1 loops=19,916)

  • 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=19,916
15. 59.748 59.748 ↑ 1.0 1 19,916

Seq Scan on bouyguestelecom.t_todo_list_translation trans (cost=0.00..1.36 rows=1 width=44) (actual time=0.002..0.003 rows=1 loops=19,916)

  • 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=19,916
16. 19.916 19.916 ↑ 1.0 4 19,916

Seq Scan on bouyguestelecom.t_organisation organisation (cost=0.00..1.04 rows=4 width=32) (actual time=0.000..0.001 rows=4 loops=19,916)

  • 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=19,916
17. 19.916 19.916 ↑ 1.0 1 19,916

Index Scan using t_timezone_pkey on bouyguestelecom.t_timezone timezone (cost=0.28..2.29 rows=1 width=31) (actual time=0.001..0.001 rows=1 loops=19,916)

  • 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=59,748
18. 0.000 119.496 ↓ 0.0 0 19,916

Nested Loop (cost=1.42..95.06 rows=1 width=52) (actual time=0.006..0.006 rows=0 loops=19,916)

  • Output: debtor.sub_organisation_id, debtor.principal_debtor_id, statistics.generation_date, item.id
  • Buffers: shared hit=84,952
19. 119.496 119.496 ↓ 0.0 0 19,916

Index Scan using t_debtor_principal_debtor_id_idx on bouyguestelecom.t_debtor debtor (cost=0.42..2.47 rows=1 width=48) (actual time=0.006..0.006 rows=0 loops=19,916)

  • 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.principal_debtor_id = t_personal_field_ach.debtor_id)
  • Filter: ((NOT debtor.deleted) AND (debtor.effective_level = 3) AND (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[])))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=79,823
20. 1.512 9.776 ↓ 2.0 2 208

Nested Loop (cost=1.00..92.59 rows=1 width=52) (actual time=0.033..0.047 rows=2 loops=208)

  • Output: statistics.debtor_id, statistics.generation_date, item.id, item.debtor_id
  • Join Filter: (debtor.id = statistics.debtor_id)
  • Buffers: shared hit=5,129
21. 3.120 3.120 ↓ 1.5 3 208

Index Scan using t_item_debtor_id_idx on bouyguestelecom.t_item item (cost=0.43..3.47 rows=2 width=32) (actual time=0.011..0.015 rows=3 loops=208)

  • 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=1,267
22. 5.144 5.144 ↑ 4.0 1 643

Index Scan using t_debtor_st_debtor_id47_idx on bouyguestelecom.t_debtor_statistics statistics (cost=0.57..44.51 rows=4 width=20) (actual time=0.007..0.008 rows=1 loops=643)

  • 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) AND (statistics.generation_date >= date(timezone((timezone.name)::text, now()))))
  • Filter: (statistics.undisputed_overdue_outstanding >= '10'::numeric)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=3,862
Planning time : 6.753 ms
Execution time : 923.791 ms