explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rfm4

Settings
# exclusive inclusive rows x rows loops node
1. 26.940 5,439.150 ↓ 8.7 1,733 1

HashAggregate (cost=187,318.21..187,322.71 rows=200 width=152) (actual time=5,437.260..5,439.150 rows=1,733 loops=1)

  • Group Key: (COALESCE(holdings.holding_uuid, lepr.partner_organization_ueid))
2. 76.261 5,412.210 ↓ 3.1 25,181 1

HashAggregate (cost=186,928.23..187,092.43 rows=8,210 width=101) (actual time=5,380.585..5,412.210 rows=25,181 loops=1)

  • Group Key: COALESCE(holdings.holding_uuid, lepr.partner_organization_ueid), edh_ids.inn, edh_ids.kpp
3. 27.874 5,335.949 ↓ 3.3 27,386 1

Hash Left Join (cost=170,189.88..186,764.03 rows=8,210 width=101) (actual time=4,599.766..5,335.949 rows=27,386 loops=1)

  • Hash Cond: (lepr.partner_organization_ueid = holdings.partner_uuid)
4. 38.045 5,308.064 ↓ 3.3 27,386 1

Nested Loop Left Join (cost=170,189.85..186,733.19 rows=8,210 width=85) (actual time=4,599.732..5,308.064 rows=27,386 loops=1)

5. 36.158 5,133.089 ↓ 3.3 27,386 1

Nested Loop Left Join (cost=170,189.42..181,548.91 rows=8,210 width=85) (actual time=4,599.717..5,133.089 rows=27,386 loops=1)

6. 42.260 4,960.001 ↓ 3.3 27,386 1

Nested Loop Left Join (cost=170,189.00..176,991.66 rows=8,210 width=85) (actual time=4,599.704..4,960.001 rows=27,386 loops=1)

7. 79.325 4,780.811 ↓ 3.3 27,386 1

Hash Join (cost=170,188.58..171,360.47 rows=8,210 width=85) (actual time=4,599.675..4,780.811 rows=27,386 loops=1)

  • Hash Cond: (statistic.edh_identifier_summary_ueid = edh_ids.ueid)
8. 2,861.579 4,453.607 ↓ 3.7 120,560 1

HashAggregate (cost=144,302.96..144,948.76 rows=32,290 width=32) (actual time=4,351.599..4,453.607 rows=120,560 loops=1)

  • Group Key: statistic.edh_identifier_summary_ueid
  • Filter: ((sum(statistic.incoming_invoice_count) > 0) OR (sum(statistic.incoming_document_balance) > 0) OR (sum(statistic.outgoing_invoice_count) > 0) OR (sum(statistic.outgoing_document_balance) > 0))
  • Rows Removed by Filter: 8
9. 1,592.028 1,592.028 ↑ 1.1 3,039,185 1

Seq Scan on edh_identifier_usage_statistic statistic (cost=0.00..71,252.75 rows=3,246,676 width=32) (actual time=0.012..1,592.028 rows=3,039,185 loops=1)

  • Filter: (date <= '2019-12-31'::date)
  • Rows Removed by Filter: 219934
10. 51.979 247.879 ↓ 1.1 84,043 1

Hash (cost=24,890.68..24,890.68 rows=79,595 width=37) (actual time=247.879..247.879 rows=84,043 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 6652kB
11. 195.900 195.900 ↓ 1.1 84,043 1

Seq Scan on edh_identifier_summary edh_ids (cost=0.00..24,890.68 rows=79,595 width=37) (actual time=0.016..195.900 rows=84,043 loops=1)

  • Filter: ((((kpp)::text ~~ '77%'::text) OR (((kpp IS NULL) OR ((kpp)::text ~ '^(?:9(?:0|6).*)$'::text)) AND ((inn)::text ~~ '77%'::text))) AND (((kpp)::text !~~ '9999%'::text) OR ((kpp IS NULL) AND ((inn)::text !~~ '9999%'::text)) OR (((kpp)::text !~ '^(?:9(?:0|6).*)$'::text) AND ((inn)::text !~ '^(?:9(?:0|6).*)$'::text))))
  • Rows Removed by Filter: 264664
12. 136.930 136.930 ↑ 1.0 1 27,386

Index Scan using ix_edh_identifier_summary_affiliation_cross_identifier_ueid on edh_identifier_summary_affiliation_cross c (cost=0.42..0.68 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=27,386)

  • Index Cond: (edh_ids.ueid = edh_identifier_summary_ueid)
13. 136.930 136.930 ↑ 1.0 1 27,386

Index Scan using edh_affiliation_pkey on edh_affiliation affiliation (cost=0.42..0.55 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=27,386)

  • Index Cond: (c.edh_affiliation_ueid = ueid)
14. 136.930 136.930 ↑ 1.0 1 27,386

Index Scan using edh_legal_entity_partner_relation_pkey on edh_legal_entity_partner_relation lepr (cost=0.42..0.62 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=27,386)

  • Index Cond: (affiliation.edh_legal_entity_partner_relation_ueid = ueid)
15. 0.006 0.011 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=32) (actual time=0.011..0.011 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.003 0.005 ↑ 1.0 1 1

Subquery Scan on holdings (cost=0.00..0.02 rows=1 width=32) (actual time=0.003..0.005 rows=1 loops=1)

17. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1)

Planning time : 1.489 ms
Execution time : 5,440.160 ms