explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E3Q

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 60,723.492 ↓ 41.0 41 1

Limit (cost=820,721.24..820,721.24 rows=1 width=12) (actual time=60,723.487..60,723.492 rows=41 loops=1)

  • ' '::text, 'g'::text))), ''::text) ~~ '%CAFE%'::text)))
2. 0.019 60,723.488 ↓ 41.0 41 1

Sort (cost=820,721.24..820,721.24 rows=1 width=12) (actual time=60,723.486..60,723.488 rows=41 loops=1)

  • Sort Key: data.id DESC
  • Sort Method: quicksort Memory: 26kB
3. 0.005 60,723.469 ↓ 41.0 41 1

Subquery Scan on data (cost=820,721.19..820,721.23 rows=1 width=12) (actual time=60,723.444..60,723.469 rows=41 loops=1)

  • Filter: (data.row_number = 1)
4. 0.025 60,723.464 ↓ 41.0 41 1

WindowAgg (cost=820,721.19..820,721.22 rows=1 width=20) (actual time=60,723.443..60,723.464 rows=41 loops=1)

5. 0.119 60,723.439 ↓ 41.0 41 1

Sort (cost=820,721.19..820,721.20 rows=1 width=12) (actual time=60,723.436..60,723.439 rows=41 loops=1)

  • Sort Key: acquirer_communications.transaction_id, acquirer_communications.id
  • Sort Method: quicksort Memory: 26kB
6. 2.641 60,723.320 ↓ 41.0 41 1

Nested Loop (cost=1,004.72..820,721.18 rows=1 width=12) (actual time=380.906..60,723.320 rows=41 loops=1)

  • Join Filter: ((NULLIF(upper(btrim(regexp_replace(acquirer_communications.dynamic_descriptor, '\s+'::text, ' '::text, 'g'::text))), ''::text) ~~ '%CAFE%'::text) OR ((NULLIF(upper(btrim(regexp_replace(acquirer_communications.dynamic_descriptor, '\s+'::text, ' '::text, 'g'::text))), ''::text) IS NULL) AND (NULLIF(upper(btrim(regexp_replace((business_details.business_name)::text, '\s+'::text,
  • Rows Removed by Join Filter: 24
7. 2.157 59,953.321 ↓ 1,437.0 1,437 1

Nested Loop (cost=1,004.15..820,570.22 rows=1 width=23) (actual time=189.124..59,953.321 rows=1,437 loops=1)

8. 0.000 59,719.807 ↓ 1,437.0 1,437 1

Nested Loop (cost=1,003.72..820,562.63 rows=1 width=8) (actual time=188.627..59,719.807 rows=1,437 loops=1)

9. 0.000 59,185.246 ↓ 1,437.0 1,437 1

Gather (cost=1,003.14..820,554.88 rows=1 width=8) (actual time=188.460..59,185.246 rows=1,437 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
10. 1.801 60,164.160 ↓ 479.0 479 3 / 3

Nested Loop (cost=3.14..819,554.78 rows=1 width=8) (actual time=110.739..60,164.160 rows=479 loops=3)

  • Join Filter: ((merchants.merchant_code)::text = (transaction_receipts.merchant_id)::text)
11. 8.628 59,824.664 ↓ 1.3 479 3 / 3

Hash Join (cost=2.45..816,411.20 rows=361 width=49) (actual time=108.743..59,824.664 rows=479 loops=3)

  • Hash Cond: (merchants.country_id = countries.id)
12. 21.121 59,816.017 ↑ 1.4 8,862 3 / 3

Nested Loop (cost=1.01..816,373.49 rows=12,289 width=53) (actual time=2.390..59,816.017 rows=8,862 loops=3)

13. 58,022.429 58,022.429 ↑ 1.4 8,862 3 / 3

Parallel Index Scan using transactions_server_time_created_at_idx on transactions (cost=0.57..721,059.94 rows=12,289 width=45) (actual time=2.271..58,022.429 rows=8,862 loops=3)

  • Index Cond: ((server_time_created_at >= '2019-05-14 00:00:00'::timestamp without time zone) AND (server_time_created_at < '2019-05-17 00:00:00'::timestamp without time zone))
  • Filter: (((tx_result)::text = '11'::text) AND (amount = '12'::numeric))
  • Rows Removed by Filter: 863,139
14. 1,772.467 1,772.467 ↑ 1.0 1 26,587 / 3

Index Scan using merchants_pkey on merchants (cost=0.43..7.76 rows=1 width=16) (actual time=0.200..0.200 rows=1 loops=26,587)

  • Index Cond: (id = transactions.merchant_id)
15. 0.004 0.019 ↑ 1.0 1 3 / 3

Hash (cost=1.43..1.43 rows=1 width=4) (actual time=0.019..0.019 rows=1 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.015 0.015 ↑ 1.0 1 3 / 3

Seq Scan on countries (cost=0.00..1.43 rows=1 width=4) (actual time=0.012..0.015 rows=1 loops=3)

  • Filter: ((iso_code)::text = 'DE'::text)
  • Rows Removed by Filter: 33
17. 337.695 337.695 ↑ 1.0 1 1,437 / 3

Index Scan using index_transaction_receipts_on_server_transaction_id on transaction_receipts (cost=0.70..8.70 rows=1 width=49) (actual time=0.704..0.705 rows=1 loops=1,437)

  • Index Cond: ((server_transaction_id)::text = (transactions.server_transaction_id)::text)
  • Filter: (transaction_event_id IS NULL)
  • Rows Removed by Filter: 0
18. 534.564 534.564 ↑ 1.0 1 1,437

Index Scan using transaction_receipt_details_pkey on transaction_receipt_details (cost=0.57..7.75 rows=1 width=8) (actual time=0.372..0.372 rows=1 loops=1,437)

  • Index Cond: (id = transaction_receipts.transaction_receipt_detail_id)
19. 231.357 231.357 ↑ 1.0 1 1,437

Index Scan using business_details_pkey on business_details (cost=0.43..7.59 rows=1 width=23) (actual time=0.161..0.161 rows=1 loops=1,437)

  • Index Cond: (id = transaction_receipt_details.business_detail_id)
20. 767.358 767.358 ↓ 0.0 0 1,437

Index Scan using idx_acquirer_communications_on_tx_id_and_method on acquirer_communications (cost=0.57..150.92 rows=1 width=40) (actual time=0.534..0.534 rows=0 loops=1,437)

  • Index Cond: ((transaction_id = transactions.id) AND ((acquirer_method)::text = 'authorize'::text))
  • Filter: ((NULLIF(upper(btrim(regexp_replace(dynamic_descriptor, '\s+'::text, ' '::text, 'g'::text))), ''::text) ~~ '%CAFE%'::text) OR (NULLIF(upper(btrim(regexp_replace(dynamic_descriptor, '\s+'::text, ' '::text, 'g'::text))), ''::text) IS NULL))
  • Rows Removed by Filter: 1