explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EmVA

Settings
# exclusive inclusive rows x rows loops node
1. 0.123 5,195.273 ↑ 22.9 41 1

Sort (cost=4,183,109.56..4,183,111.91 rows=940 width=593) (actual time=5,195.269..5,195.273 rows=41 loops=1)

  • Sort Key: (sum(CASE WHEN ((facts_distribution_test_2020_05_01.created_at >= ((timezone('Canada/Eastern'::text, now()))::date - '7 days'::interval)) AND (facts_distribution_test_2020_05_01.created_at <= ((timezone('Canada/Eastern'::text, now()))::date - '6 days'::interval)) AND (dl.vertical = ANY ('{41,50,57}'::smallint[])) AND (facts_distribution_test_2020_05_01.accepted = 1)) THEN dl.revenue ELSE '0'::double precision END)) DESC
  • Sort Method: quicksort Memory: 36kB
2. 2,003.614 5,195.150 ↑ 22.9 41 1

GroupAggregate (cost=4,172,135.99..4,183,063.14 rows=940 width=593) (actual time=3,153.781..5,195.150 rows=41 loops=1)

  • Group Key: db.buyer_name
  • Group Key: ()
3. 141.042 3,191.536 ↓ 20.5 160,875 1

Sort (cost=4,172,135.99..4,172,155.64 rows=7,860 width=68) (actual time=3,153.491..3,191.536 rows=160,875 loops=1)

  • Sort Key: db.buyer_name
  • Sort Method: external merge Disk: 13,000kB
4. 89.747 3,050.494 ↓ 20.5 160,875 1

Nested Loop Left Join (cost=299,315.92..4,171,627.44 rows=7,860 width=68) (actual time=1,237.127..3,050.494 rows=160,875 loops=1)

5. 40.416 2,638.997 ↓ 20.5 160,875 1

Hash Left Join (cost=299,315.49..4,162,923.71 rows=7,860 width=49) (actual time=1,237.108..2,638.997 rows=160,875 loops=1)

  • Hash Cond: (facts_distribution_test_2020_05_01.buyer_id = db.id)
6. 299.343 2,598.244 ↓ 20.5 160,875 1

Hash Join (cost=299,242.66..4,162,830.19 rows=7,860 width=36) (actual time=1,236.666..2,598.244 rows=160,875 loops=1)

  • Hash Cond: (facts_distribution_test_2020_05_01.lead_id = dl.source_id)
7. 48.031 1,156.594 ↓ 2.2 757,900 1

Append (cost=0.14..3,856,160.01 rows=343,105 width=26) (actual time=93.974..1,156.594 rows=757,900 loops=1)

  • Subplans Removed: 8
8. 1,019.436 1,108.563 ↓ 2.2 757,900 1

Bitmap Heap Scan on facts_distribution_test_2020_05_01 (cost=37,907.52..3,854,374.23 rows=343,097 width=26) (actual time=93.974..1,108.563 rows=757,900 loops=1)

  • Recheck Cond: (((created_at >= ((timezone('Canada/Eastern'::text, now()))::date - '7 days'::interval)) AND (created_at <= ((timezone('Canada/Eastern'::text, now()))::date - '6 days'::interval))) OR ((created_at >= (timezone('Canada/Eastern'::text, now()))::date) AND (created_at <= ((timezone('Canada/Eastern'::text, now()))::date + '1 day'::interval))))
  • Filter: (((type)::text = 'post'::text) AND ((created_at)::time without time zone <= (timezone('Canada/Eastern'::text, now()))::time without time zone))
  • Rows Removed by Filter: 949,121
  • Heap Blocks: exact=28,990
9. 0.003 89.127 ↓ 0.0 0 1

BitmapOr (cost=37,907.52..37,907.52 rows=1,547,079 width=0) (actual time=89.127..89.127 rows=0 loops=1)

10. 62.328 62.328 ↑ 1.0 1,230,710 1

Bitmap Index Scan on facts_distribution_test_2020_05_01_created_at_idx (cost=0.00..31,133.01 rows=1,276,443 width=0) (actual time=62.328..62.328 rows=1,230,710 loops=1)

  • Index Cond: ((created_at >= ((timezone('Canada/Eastern'::text, now()))::date - '7 days'::interval)) AND (created_at <= ((timezone('Canada/Eastern'::text, now()))::date - '6 days'::interval)))
11. 26.796 26.796 ↓ 1.8 477,031 1

Bitmap Index Scan on facts_distribution_test_2020_05_01_created_at_idx (cost=0.00..6,602.95 rows=270,637 width=0) (actual time=26.796..26.796 rows=477,031 loops=1)

  • Index Cond: ((created_at >= (timezone('Canada/Eastern'::text, now()))::date) AND (created_at <= ((timezone('Canada/Eastern'::text, now()))::date + '1 day'::interval)))
12. 85.717 1,142.307 ↓ 1.0 318,232 1

Hash (cost=293,494.25..293,494.25 rows=313,062 width=18) (actual time=1,142.307..1,142.307 rows=318,232 loops=1)

  • Buckets: 65,536 Batches: 8 Memory Usage: 2,614kB
13. 0.000 1,056.590 ↓ 1.0 318,232 1

Gather (cost=1,000.00..293,494.25 rows=313,062 width=18) (actual time=0.424..1,056.590 rows=318,232 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
14. 1,067.488 1,067.488 ↑ 1.2 106,077 3 / 3

Parallel Seq Scan on dim_lead dl (cost=0.00..261,188.05 rows=130,442 width=18) (actual time=0.027..1,067.488 rows=106,077 loops=3)

  • Filter: (vertical = ANY ('{41,50,57}'::integer[]))
  • Rows Removed by Filter: 4,457,262
15. 0.132 0.337 ↑ 3.0 431 1

Hash (cost=56.81..56.81 rows=1,281 width=25) (actual time=0.337..0.337 rows=431 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 42kB
16. 0.205 0.205 ↑ 3.0 431 1

Seq Scan on dim_buyer db (cost=0.00..56.81 rows=1,281 width=25) (actual time=0.007..0.205 rows=431 loops=1)

17. 321.750 321.750 ↑ 1.0 1 160,875

Index Scan using dim_person_pkey on dim_person dp (cost=0.43..1.11 rows=1 width=27) (actual time=0.002..0.002 rows=1 loops=160,875)

  • Index Cond: (facts_distribution_test_2020_05_01.person_id = id)
Planning time : 9.200 ms
Execution time : 5,199.275 ms