explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jP0

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 6,841.842 ↑ 1.0 101 1

Limit (cost=636,920.79..636,921.05 rows=101 width=303) (actual time=6,841.823..6,841.842 rows=101 loops=1)

  • Output: thread.id, thread.topic_type, thread.topic_value, thread.read_roles, thread.write_roles, thread.date_created, thread.date_updated, thread.anonymized, (json_agg(DISTINCT ent.*)), (json_agg(json_build_object('id', organization.id, 'type', organization.type, 'display_name', organization.display_name, 'provider_id', organization.provider_id, 'has_read_thread', thread_participant_organization.has_read_thread)))
2. 75.296 6,841.831 ↑ 910.9 101 1

Sort (cost=636,920.79..637,150.79 rows=91,998 width=303) (actual time=6,841.820..6,841.831 rows=101 loops=1)

  • Output: thread.id, thread.topic_type, thread.topic_value, thread.read_roles, thread.write_roles, thread.date_created, thread.date_updated, thread.anonymized, (json_agg(DISTINCT ent.*)), (json_agg(json_build_object('id', organization.id, 'type', organization.type, 'display_name', organization.display_name, 'provider_id', organization.provider_id, 'has_read_thread', thread_participant_organization.has_read_thread)))
  • Sort Key: thread.date_updated DESC NULLS LAST, thread.id DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 191kB
3. 2,566.902 6,766.535 ↓ 3.1 284,290 1

GroupAggregate (cost=628,568.20..633,398.09 rows=91,998 width=303) (actual time=4,022.449..6,766.535 rows=284,290 loops=1)

  • Output: thread.id, thread.topic_type, thread.topic_value, thread.read_roles, thread.write_roles, thread.date_created, thread.date_updated, thread.anonymized, json_agg(DISTINCT ent.*), json_agg(json_build_object('id', organization.id, 'type', organization.type, 'display_name', organization.display_name, 'provider_id', organization.provider_id, 'has_read_thread', thread_participant_organization.has_read_thread))
  • Group Key: thread.id
4. 1,144.167 4,199.633 ↓ 3.1 852,870 1

Sort (cost=628,568.20..629,258.18 rows=275,994 width=385) (actual time=4,022.361..4,199.633 rows=852,870 loops=1)

  • Output: thread.id, thread.topic_type, thread.topic_value, thread.read_roles, thread.write_roles, thread.date_created, thread.date_updated, thread.anonymized, ent.*, organization.id, organization.type, organization.display_name, organization.provider_id, thread_participant_organization.has_read_thread
  • Sort Key: thread.id DESC NULLS LAST
  • Sort Method: external merge Disk: 318888kB
5. 793.878 3,055.466 ↓ 3.1 852,870 1

Hash Join (cost=433,541.49..505,514.24 rows=275,994 width=385) (actual time=1,737.109..3,055.466 rows=852,870 loops=1)

  • Output: thread.id, thread.topic_type, thread.topic_value, thread.read_roles, thread.write_roles, thread.date_created, thread.date_updated, thread.anonymized, ent.*, organization.id, organization.type, organization.display_name, organization.provider_id, thread_participant_organization.has_read_thread
  • Inner Unique: true
  • Hash Cond: ((thread_participant_organization.organization_type = organization.type) AND (thread_participant_organization.organization_id = organization.id))
6. 432.717 2,180.682 ↓ 3.1 852,870 1

Hash Join (cost=421,878.89..465,169.67 rows=275,994 width=331) (actual time=1,655.230..2,180.682 rows=852,870 loops=1)

  • Output: thread.id, thread.topic_type, thread.topic_value, thread.read_roles, thread.write_roles, thread.date_created, thread.date_updated, thread.anonymized, thread_participant_organization.has_read_thread, thread_participant_organization.organization_type, thread_participant_organization.organization_id, ent.*
  • Hash Cond: (thread_participant_organization.thread_id = thread.id)
7. 92.888 92.888 ↑ 1.0 852,870 1

Seq Scan on inbox.thread_participant_organization (cost=0.00..17,057.70 rows=852,870 width=50) (actual time=0.052..92.888 rows=852,870 loops=1)

  • Output: thread_participant_organization.has_read_thread, thread_participant_organization.thread_id, thread_participant_organization.organization_type, thread_participant_organization.organization_id
8. 108.376 1,655.077 ↓ 3.1 284,290 1

Hash (cost=417,044.91..417,044.91 rows=91,998 width=297) (actual time=1,655.077..1,655.077 rows=284,290 loops=1)

  • Output: thread.id, thread.topic_type, thread.topic_value, thread.read_roles, thread.write_roles, thread.date_created, thread.date_updated, thread.anonymized, ent.*
  • Buckets: 16384 (originally 16384) Batches: 32 (originally 8) Memory Usage: 3969kB
9. 237.775 1,546.701 ↓ 3.1 284,290 1

Hash Left Join (cost=24,073.47..417,044.91 rows=91,998 width=297) (actual time=194.985..1,546.701 rows=284,290 loops=1)

  • Output: thread.id, thread.topic_type, thread.topic_value, thread.read_roles, thread.write_roles, thread.date_created, thread.date_updated, thread.anonymized, ent.*
  • Inner Unique: true
  • Hash Cond: ((thread_entity.entity_type = ent.type) AND (thread_entity.entity_id = ent.id))
10. 197.085 1,191.733 ↓ 3.1 284,290 1

Hash Left Join (cost=10,988.52..393,397.97 rows=91,998 width=261) (actual time=75.935..1,191.733 rows=284,290 loops=1)

  • Output: thread.id, thread.topic_type, thread.topic_value, thread.read_roles, thread.write_roles, thread.date_created, thread.date_updated, thread.anonymized, thread_entity.entity_type, thread_entity.entity_id
  • Hash Cond: (thread.id = thread_entity.thread_id)
11. 350.452 919.032 ↓ 3.1 284,290 1

Seq Scan on inbox.thread (cost=0.00..372,992.47 rows=91,998 width=239) (actual time=0.071..919.032 rows=284,290 loops=1)

  • Output: thread.id, thread.topic_type, thread.topic_value, thread.read_roles, thread.write_roles, thread.date_created, thread.date_updated, thread.anonymized
  • Filter: ((NOT thread.anonymized) AND ((SubPlan 1) && '{ROLE_MPS_OFFER_READ,ROLE_MPS_MODEL_READ,ROLE_MPS_MODEL_WRITE,ROLE_OP_TECHNICAL_SETTING_READ,ROLE_MPS_CATALOG_SEARCH,ROLE_OP_ORDER_DOCUMENT_WRITE,ROLE_OP_SHOP_WRITE,ROLE_MPS_CATALOG_WRITE,ROLE_OP_PRODUCT_BULK_ACTION_READ,ROLE_OP_BILLING_CYCLE_WRITE,ROLE_OP_OPERATOR_MCI_READ,ROLE_OP_OPERATOR_MCM_WRITE,ROLE_OP_INVOICE_WRITE,ROLE_OP_SHOP_BANK_WRITE,ROLE_OP_SUPERVISOR_READ,ROLE_OP_SUPERVISOR_WRITE,ROLE_MPS_LOCATION_WRITE,ROLE_OP_SUPERVISOR_APPLICATION_SETTING_WRITE,ROLE_OP_DASHBOARD_READ,ROLE_OP_EMAIL_LISTING_READ,ROLE_OP_BUSINESS_SETTING_READ,ROLE_OP_OPERATOR_ACCESS,ROLE_OP_ORDER_EXPORT,ROLE_OP_INVOICE_READ,ROLE_OP_BATCH_ADMIN_READ,ROLE_MPS_DASHBOARD,ROLE_OP_ORDER_ADDITIONAL_FIELD_WRITE,ROLE_OP_ORDER_EVALUATION_READ,ROLE_OP_OPERATOR_MCM_READ,ROLE_OP_OPERATOR_MCI_WRITE,ROLE_MPS_CATEGORY_SEARCH,ROLE_OP_ORDER_WRITE,ROLE_MPS_OFFER_WRITE,ROLE_MPS_MODEL_SEARCH,ROLE_OP_ORDER_INCIDENT_WRITE,ROLE_OP_PROMOTION_READ,ROLE_OP_RECALL_AND_REMINDER_WRITE,ROLE_OP_CATEGORY_AUTOCOMPLETE,ROLE_OP_TEST_SHOP_CONNECTOR,ROLE_MPS_CATEGORY_WRITE,ROLE_MPS_CUSTOM_ATTRIBUTE_READ,ROLE_MPS_ORDER_INCIDENT_WRITE,ROLE_OP_API_ACCESS,ROLE_MPS_CATALOG_READ,ROLE_MPS_LOCATION_READ,ROLE_OP_PRODUCT_BULK_ACTION_WRITE,ROLE_MPS_CATEGORY_READ,ROLE_OP_QUOTE_WRITE,ROLE_OP_OFFER_READ,ROLE_OP_BRAND_AUTOCOMPLETE,ROLE_OP_BATCH_ADMIN_WRITE,ROLE_OP_PAYMENT_VOUCHER_READ,ROLE_MPS_ORDER_CUSTOM_ATTRIBUTE_EDIT,ROLE_OP_ORDER_EVALUATION_WRITE,ROLE_OP_CATEGORY_WRITE,ROLE_OP_SHOP_MCI_READ,ROLE_OP_BILLING_CYCLE_READ,ROLE_MPS_ORDER_READ,ROLE_MPS_OFFER_MESSAGE_WRITE,ROLE_OP_CUSTOMER_READ,ROLE_OP_RECALL_AND_REMINDER_READ,ROLE_OP_TRANSACTION_LOG_READ,ROLE_OP_ORDER_READ,ROLE_OP_SHOP_AUTOCOMPLETE,ROLE_MPS_CUSTOM_ATTRIBUTE_WRITE,ROLE_OP_BUSINESS_SETTING_WRITE,ROLE_OP_OFFER_WRITE,ROLE_OP_SHOP_MCI_WRITE,ROLE_MPS_ORDER_MESSAGE_WRITE,ROLE_OP_PRODUCT_WRITE,ROLE_MPS_ORDER_WRITE,ROLE_OP_TECHNICAL_SETTING_WRITE,ROLE_OP_EMAIL_DETAIL_READ,ROLE_OP_FRONT_API_KEY_WRITE,ROLE_OP_PROMOTION_WRITE,ROLE_MPS_ORDER_EXPORT,ROLE_OP_ORDER_MESSAGE_WRITE,ROLE_OP...}'::text[]))
12.          

SubPlan (forSeq Scan)

13. 284.290 568.580 ↑ 1.0 1 284,290

Aggregate (cost=1.25..1.26 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=284,290)

  • Output: array_agg(x.value)
14. 284.290 284.290 ↑ 25.0 4 284,290

Function Scan on pg_catalog.jsonb_array_elements_text x (cost=0.00..1.00 rows=100 width=32) (actual time=0.001..0.001 rows=4 loops=284,290)

  • Output: x.value
  • Function Call: jsonb_array_elements_text(thread.read_roles)
15. 49.239 75.616 ↑ 1.0 284,290 1

Hash (cost=5,212.90..5,212.90 rows=284,290 width=38) (actual time=75.616..75.616 rows=284,290 loops=1)

  • Output: thread_entity.thread_id, thread_entity.entity_type, thread_entity.entity_id
  • Buckets: 65536 Batches: 8 Memory Usage: 2937kB
16. 26.377 26.377 ↑ 1.0 284,290 1

Seq Scan on inbox.thread_entity (cost=0.00..5,212.90 rows=284,290 width=38) (actual time=0.006..26.377 rows=284,290 loops=1)

  • Output: thread_entity.thread_id, thread_entity.entity_type, thread_entity.entity_id
17. 57.621 117.193 ↑ 1.0 284,278 1

Hash (cost=5,211.78..5,211.78 rows=284,278 width=80) (actual time=117.193..117.193 rows=284,278 loops=1)

  • Output: ent.*, ent.type, ent.id
  • Buckets: 65536 Batches: 16 Memory Usage: 2415kB
18. 59.572 59.572 ↑ 1.0 284,278 1

Seq Scan on inbox.entity ent (cost=0.00..5,211.78 rows=284,278 width=80) (actual time=0.008..59.572 rows=284,278 loops=1)

  • Output: ent.*, ent.type, ent.id
19. 58.145 80.906 ↑ 1.0 217,544 1

Hash (cost=5,424.44..5,424.44 rows=217,544 width=87) (actual time=80.906..80.906 rows=217,544 loops=1)

  • Output: organization.id, organization.type, organization.display_name, organization.provider_id
  • Buckets: 32768 Batches: 8 Memory Usage: 3474kB
20. 22.761 22.761 ↑ 1.0 217,544 1

Seq Scan on inbox.organization (cost=0.00..5,424.44 rows=217,544 width=87) (actual time=0.014..22.761 rows=217,544 loops=1)

  • Output: organization.id, organization.type, organization.display_name, organization.provider_id
Planning time : 7.453 ms
Execution time : 6,878.862 ms