explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qc1P

Settings
# exclusive inclusive rows x rows loops node
1. 1.965 1,890.503 ↑ 4.9 1,913 1

Sort (cost=112,953.29..112,957.95 rows=9,303 width=155) (actual time=1,890.395..1,890.503 rows=1,913 loops=1)

  • Sort Key: (count(impressions_tb.id)) DESC, (count(likes_tb.id)) DESC
  • Sort Method: quicksort Memory: 557kB
2. 222.057 1,888.538 ↑ 4.9 1,913 1

GroupAggregate (cost=112,736.88..112,830.65 rows=9,303 width=155) (actual time=1,608.330..1,888.538 rows=1,913 loops=1)

  • Group Key: job_tb.id_in_source
3. 694.918 1,666.481 ↓ 40.0 376,740 1

Sort (cost=112,736.88..112,741.59 rows=9,408 width=122) (actual time=1,608.251..1,666.481 rows=376,740 loops=1)

  • Sort Key: job_tb.id_in_source
  • Sort Method: external merge Disk: 54,944kB
4. 97.784 971.563 ↓ 40.0 376,740 1

Hash Left Join (cost=102,143.63..112,612.70 rows=9,408 width=122) (actual time=564.793..971.563 rows=376,740 loops=1)

  • Hash Cond: (job_tb.location_id = location_tb.id)
5. 180.782 621.715 ↓ 40.0 376,740 1

Hash Right Join (cost=89,909.10..100,373.23 rows=9,408 width=115) (actual time=311.528..621.715 rows=376,740 loops=1)

  • Hash Cond: (history_tb.job_id = job_tb.id)
6. 146.298 146.298 ↑ 1.1 432,403 1

Index Scan using idx_history_created_at on history_tb (cost=0.09..10,111.98 rows=458,749 width=8) (actual time=0.027..146.298 rows=432,403 loops=1)

  • Index Cond: ((created_at > '2020-08-01 00:00:00'::timestamp without time zone) AND (created_at < '2020-09-01 00:00:00'::timestamp without time zone))
  • Filter: (count > 0)
  • Rows Removed by Filter: 79,764
7. 12.092 294.635 ↓ 4.9 46,102 1

Hash (cost=89,876.08..89,876.08 rows=9,408 width=111) (actual time=294.635..294.635 rows=46,102 loops=1)

  • Buckets: 65,536 (originally 16384) Batches: 1 (originally 1) Memory Usage: 7,901kB
8. 14.499 282.543 ↓ 4.9 46,102 1

Hash Right Join (cost=88,373.22..89,876.08 rows=9,408 width=111) (actual time=264.975..282.543 rows=46,102 loops=1)

  • Hash Cond: (likes_tb.job_id = job_tb.id)
9. 3.132 3.132 ↓ 1.1 5,819 1

Index Scan using likes_tb_date_idx on likes_tb (cost=0.06..1,498.69 rows=5,513 width=8) (actual time=0.024..3.132 rows=5,819 loops=1)

  • Index Cond: ((created_at > '2020-08-01 00:00:00'::timestamp without time zone) AND (created_at < '2020-09-01 00:00:00'::timestamp without time zone))
10. 12.511 264.912 ↓ 4.6 43,388 1

Hash (cost=88,340.23..88,340.23 rows=9,408 width=107) (actual time=264.912..264.912 rows=43,388 loops=1)

  • Buckets: 65,536 (originally 16384) Batches: 1 (originally 1) Memory Usage: 7,422kB
11. 13.552 252.401 ↓ 4.6 43,388 1

Merge Left Join (cost=88,320.58..88,340.23 rows=9,408 width=107) (actual time=231.987..252.401 rows=43,388 loops=1)

  • Merge Cond: (job_tb.id = click_out_tb.job_id)
12. 24.395 232.110 ↓ 4.6 43,388 1

Sort (cost=87,822.34..87,827.05 rows=9,408 width=103) (actual time=226.156..232.110 rows=43,388 loops=1)

  • Sort Key: job_tb.id
  • Sort Method: quicksort Memory: 12,525kB
13. 36.293 207.715 ↓ 4.6 43,388 1

Hash Right Join (cost=87,175.92..87,698.16 rows=9,408 width=103) (actual time=99.986..207.715 rows=43,388 loops=1)

  • Hash Cond: (impressions_tb.job_id = job_tb.id)
14. 71.527 71.527 ↓ 6.4 182,549 1

Index Scan using idx_impression_date_2 on impressions_tb (cost=0.09..507.30 rows=28,640 width=8) (actual time=0.020..71.527 rows=182,549 loops=1)

  • Index Cond: ((date > '2020-08-01 00:00:00'::timestamp without time zone) AND (date < '2020-09-01 00:00:00'::timestamp without time zone))
15. 12.887 99.895 ↓ 3.7 34,446 1

Hash (cost=87,142.90..87,142.90 rows=9,408 width=99) (actual time=99.895..99.895 rows=34,446 loops=1)

  • Buckets: 65,536 (originally 16384) Batches: 1 (originally 1) Memory Usage: 5,694kB
16. 76.262 87.008 ↓ 3.7 34,446 1

Bitmap Heap Scan on job_tb (cost=4,961.44..87,142.90 rows=9,408 width=99) (actual time=19.555..87.008 rows=34,446 loops=1)

  • Recheck Cond: ((source)::text = 'Time4Change'::text)
  • Filter: (updated_at > '2020-08-01 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 27,359
  • Heap Blocks: exact=50,712
17. 10.746 10.746 ↓ 1.2 73,812 1

Bitmap Index Scan on per_source (cost=0.00..4,960.97 rows=59,240 width=0) (actual time=10.746..10.746 rows=73,812 loops=1)

  • Index Cond: ((source)::text = 'Time4Change'::text)
18. 3.121 6.739 ↑ 1.0 9,869 1

Sort (cost=498.23..503.27 rows=10,064 width=8) (actual time=5.826..6.739 rows=9,869 loops=1)

  • Sort Key: click_out_tb.job_id
  • Sort Method: quicksort Memory: 847kB
19. 3.618 3.618 ↑ 1.0 9,869 1

Index Scan using click_out_tb_date_idx on click_out_tb (cost=0.06..364.41 rows=10,064 width=8) (actual time=0.025..3.618 rows=9,869 loops=1)

  • Index Cond: ((date > '2020-08-01 00:00:00'::timestamp without time zone) AND (date < '2020-09-01 00:00:00'::timestamp without time zone))
20. 143.657 252.064 ↑ 1.0 674,471 1

Hash (cost=9,856.40..9,856.40 rows=679,466 width=15) (actual time=252.064..252.064 rows=674,471 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 40,252kB
21. 108.407 108.407 ↑ 1.0 674,471 1

Seq Scan on location_tb (cost=0.00..9,856.40 rows=679,466 width=15) (actual time=0.012..108.407 rows=674,471 loops=1)

Planning time : 1.573 ms
Execution time : 1,903.634 ms