explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Muoj

Settings
# exclusive inclusive rows x rows loops node
1. 6,430.345 70,241.874 ↑ 1.3 1,329,451 1

HashAggregate (cost=3,399,630.37..3,416,697.28 rows=1,706,691 width=47) (actual time=66,295.609..70,241.874 rows=1,329,451 loops=1)

  • Group Key: map.kredit_privat_id, ptm.user_id
  • Functions: 45
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 12.732 ms, Inlining 33.256 ms, Optimization 456.339 ms, Emission 202.655 ms, Total 704.982 ms
2.          

Initplan (forHashAggregate)

3. 0.001 0.001 ↑ 1.0 1 1

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

4. 1,521.367 63,811.528 ↓ 2.6 4,449,201 1

Hash Join (cost=3,262,194.60..3,386,830.18 rows=1,706,691 width=46) (actual time=61,487.576..63,811.528 rows=4,449,201 loops=1)

  • Hash Cond: (log.print_to_mail_id = ptm.id)
5. 1,935.665 1,935.665 ↓ 1.1 2,630,715 1

Seq Scan on print_to_mail_log log (cost=0.00..100,641.09 rows=2,473,766 width=8) (actual time=693.149..1,935.665 rows=2,630,715 loops=1)

  • Filter: ((action)::text = ANY ('{SEND,PRINTED}'::text[]))
  • Rows Removed by Filter: 1955763
6. 1,166.204 60,354.496 ↓ 2.0 3,026,815 1

Hash (cost=3,250,729.31..3,250,729.31 rows=1,528,706 width=62) (actual time=60,354.496..60,354.496 rows=3,026,815 loops=1)

  • Buckets: 4194304 (originally 2097152) Batches: 1 (originally 1) Memory Usage: 316886kB
7. 1,239.847 59,188.292 ↓ 2.0 3,026,815 1

Hash Join (cost=3,126,345.61..3,250,729.31 rows=1,528,706 width=62) (actual time=57,353.511..59,188.292 rows=3,026,815 loops=1)

  • Hash Cond: (ptm_file.print_to_mail_id = ptm.id)
8. 1,134.961 1,134.961 ↑ 1.1 4,494,334 1

Seq Scan on print_to_mail_file ptm_file (cost=0.00..97,905.24 rows=5,022,648 width=31) (actual time=0.019..1,134.961 rows=4,494,334 loops=1)

9. 527.723 56,813.484 ↓ 2.3 1,329,491 1

Hash (cost=3,121,914.15..3,121,914.15 rows=590,861 width=31) (actual time=56,813.483..56,813.484 rows=1,329,491 loops=1)

  • Buckets: 2097152 (originally 1048576) Batches: 1 (originally 1) Memory Usage: 99478kB
10. 2,101.496 56,285.761 ↓ 2.3 1,329,491 1

Hash Join (cost=2,835,873.49..3,121,914.15 rows=590,861 width=31) (actual time=52,560.513..56,285.761 rows=1,329,491 loops=1)

  • Hash Cond: ((ptm.ext_ref_id)::bigint = map.brokerage_id)
11. 1,932.629 1,932.629 ↓ 2.3 1,329,512 1

Index Scan using idx_print_to_mail_creation_date on print_to_mail ptm (cost=0.43..37,879.47 rows=590,861 width=33) (actual time=0.057..1,932.629 rows=1,329,512 loops=1)

  • Index Cond: (created_at >= $0)
  • Filter: (((channel)::text = ANY ('{cs,campaign}'::text[])) AND ((status)::text = ANY ('{PRINTED,SENT}'::text[])) AND ((ext_ref_id)::text <> '12663387;0369449184;DD766B2DC35AC9A8CFE9093B64A29C2BD36CA3AC738559300C0CC2BD8B46998F'::text) AND ((mandator)::text = 'brokerage'::text) AND (id <> ALL ('{630568,634871,743928,753949,780611,799710}'::bigint[])))
  • Rows Removed by Filter: 30427
12. 30,067.027 52,251.636 ↑ 1.0 109,698,450 1

Hash (cost=2,012,431.23..2,012,431.23 rows=109,792,245 width=16) (actual time=52,251.635..52,251.636 rows=109,698,450 loops=1)

  • Buckets: 67108864 Batches: 1 Memory Usage: 5666403kB
13. 22,184.609 22,184.609 ↑ 1.0 109,698,450 1

Seq Scan on brokerage_entity_map map (cost=0.00..2,012,431.23 rows=109,792,245 width=16) (actual time=0.024..22,184.609 rows=109,698,450 loops=1)

Planning time : 3.944 ms
Execution time : 71,031.568 ms