explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XuST

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 17,436.809 ↑ 1.0 1 1

Limit (cost=322,794.63..322,794.63 rows=1 width=32) (actual time=17,436.795..17,436.809 rows=1 loops=1)

2.          

Initplan (for Limit)

3. 0.004 0.155 ↑ 1.0 1 1

Limit (cost=0.00..0.01 rows=1 width=4) (actual time=0.153..0.155 rows=1 loops=1)

4. 0.151 0.151 ↑ 3,672.0 1 1

Seq Scan on qa_active_room (cost=0.00..54.72 rows=3,672 width=4) (actual time=0.151..0.151 rows=1 loops=1)

5. 0.015 17,436.792 ↑ 14,806.0 1 1

Sort (cost=322,794.62..322,831.63 rows=14,806 width=32) (actual time=17,436.792..17,436.792 rows=1 loops=1)

  • Sort Key: (CASE WHEN (date_part('week'::text, (CURRENT_DATE)::timestamp without time zone) <> date_part('week'::text, c.call_date)) THEN 0 ELSE 1 END), ((c.call_date)::date), (CASE WHEN (qa.qa_room_id = $0) THEN 0 ELSE 1 END), c.call_date
  • Sort Method: quicksort Memory: 25kB
6. 146.828 17,436.777 ↑ 7,403.0 2 1

Merge Anti Join (cost=321,607.52..322,720.59 rows=14,806 width=32) (actual time=17,415.699..17,436.777 rows=2 loops=1)

  • Merge Cond: (c.call_id = rv.call_id)
7. 53.148 16,855.247 ↑ 1.7 17,490 1

Sort (cost=304,134.14..304,208.18 rows=29,613 width=28) (actual time=16,846.360..16,855.247 rows=17,490 loops=1)

  • Sort Key: qa.call_id
  • Sort Method: quicksort Memory: 2,135kB
8. 44.033 16,802.099 ↑ 1.7 17,490 1

Hash Join (cost=258,439.10..301,934.79 rows=29,613 width=28) (actual time=6,953.031..16,802.099 rows=17,490 loops=1)

  • Hash Cond: (c.actor_id = ca.actor_id)
  • Join Filter: (COALESCE(ca.adp_payroll_id, 0) <> COALESCE(a.adp_payroll_id, 0))
9. 794.877 16,743.829 ↑ 2.2 29,738 1

Hash Join (cost=258,256.48..301,578.42 rows=66,122 width=36) (actual time=6,938.782..16,743.829 rows=29,738 loops=1)

  • Hash Cond: (c.call_id = qa.call_id)
  • Join Filter: (c.actor_id <> a.actor_id)
10. 2,072.549 11,881.368 ↑ 89.3 121,728 1

Nested Loop (cost=45,308.17..47,031.10 rows=10,872,614 width=28) (actual time=2,870.083..11,881.368 rows=121,728 loops=1)

11. 3,212.822 4,133.871 ↓ 7,093.7 1,418,737 1

HashAggregate (cost=45,307.60..45,309.60 rows=200 width=8) (actual time=2,869.895..4,133.871 rows=1,418,737 loops=1)

  • Group Key: sales.call_id
12. 921.049 921.049 ↓ 3.1 2,606,551 1

Seq Scan on sales (cost=0.00..43,216.48 rows=836,448 width=8) (actual time=0.007..921.049 rows=2,606,551 loops=1)

13. 5,674.948 5,674.948 ↓ 0.0 0 1,418,737

Index Scan using calls_copy_pkey on calls c (cost=0.56..8.60 rows=1 width=20) (actual time=0.004..0.004 rows=0 loops=1,418,737)

  • Index Cond: (call_id = sales.call_id)
  • Filter: ((disposition_id IS NOT NULL) AND (call_date > '2019-01-15 00:00:00'::timestamp without time zone) AND (call_id <> ALL ('{115843043,121894213}'::bigint[])) AND (have_recording OR (call_date < CURRENT_DATE)) AND (((call_date)::date < (now())::date) OR (disposition_id IS NOT NULL)))
  • Rows Removed by Filter: 0
14. 573.584 4,067.584 ↓ 1.6 535,854 1

Hash (cost=208,815.33..208,815.33 rows=330,639 width=20) (actual time=4,067.584..4,067.584 rows=535,854 loops=1)

  • Buckets: 1,048,576 (originally 524288) Batches: 2 (originally 1) Memory Usage: 25,645kB
15. 604.056 3,494.000 ↓ 1.6 535,854 1

Merge Anti Join (cost=205,480.98..208,815.33 rows=330,639 width=20) (actual time=2,594.670..3,494.000 rows=535,854 loops=1)

  • Merge Cond: (qa.call_id = sa.call_id)
16. 946.895 2,887.907 ↑ 1.2 535,962 1

Sort (cost=193,179.62..194,832.82 rows=661,278 width=20) (actual time=2,592.936..2,887.907 rows=535,962 loops=1)

  • Sort Key: qa.call_id
  • Sort Method: external merge Disk: 14,760kB
17. 696.000 1,941.012 ↑ 1.2 535,962 1

Nested Loop (cost=0.00..129,250.91 rows=661,278 width=20) (actual time=2.269..1,941.012 rows=535,962 loops=1)

  • Join Filter: ((qa.lock_actor_id IS NULL) OR (qa.lock_actor_id = a.actor_id) OR (qa.lock_date < (now())::date))
  • Rows Removed by Join Filter: 4
18. 1.346 1.346 ↑ 1.0 1 1

Seq Scan on actors a (cost=0.00..174.66 rows=1 width=8) (actual time=1.078..1.346 rows=1 loops=1)

  • Filter: (actor_id = 6,767)
  • Rows Removed by Filter: 7,197
19. 1,243.666 1,243.666 ↑ 3.7 535,966 1

Seq Scan on qa (cost=0.00..89,847.13 rows=1,961,456 width=24) (actual time=1.182..1,243.666 rows=535,966 loops=1)

  • Filter: (NOT skipped)
  • Rows Removed by Filter: 956,951
20. 0.750 2.037 ↑ 6.1 683 1

Sort (cost=12,301.36..12,311.81 rows=4,182 width=8) (actual time=1.726..2.037 rows=683 loops=1)

  • Sort Key: sa.call_id
  • Sort Method: quicksort Memory: 57kB
21. 1.158 1.287 ↑ 6.1 683 1

Bitmap Heap Scan on sales sa (cost=196.84..12,049.81 rows=4,182 width=8) (actual time=0.192..1.287 rows=683 loops=1)

  • Recheck Cond: (advertiser_unit_id = 128)
  • Heap Blocks: exact=465
22. 0.129 0.129 ↑ 6.1 683 1

Bitmap Index Scan on idx_sales_advertiser_unit_id (cost=0.00..195.79 rows=4,182 width=0) (actual time=0.128..0.129 rows=683 loops=1)

  • Index Cond: (advertiser_unit_id = 128)
23. 5.250 14.237 ↓ 6.0 3,838 1

Hash (cost=174.66..174.66 rows=636 width=8) (actual time=14.237..14.237 rows=3,838 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 178kB
24. 8.987 8.987 ↓ 6.0 3,838 1

Seq Scan on actors ca (cost=0.00..174.66 rows=636 width=8) (actual time=0.026..8.987 rows=3,838 loops=1)

  • Filter: ((last_name)::text ~* '^[KLMNOPQRSTUVWXYZ].*'::text)
  • Rows Removed by Filter: 3,360
25. 293.026 434.702 ↓ 1.4 193,477 1

Sort (cost=17,473.37..17,825.94 rows=141,026 width=8) (actual time=311.716..434.702 rows=193,477 loops=1)

  • Sort Key: rv.call_id
  • Sort Method: quicksort Memory: 15,214kB
26. 141.676 141.676 ↓ 1.4 193,477 1

Seq Scan on qa_reviews rv (cost=0.00..5,411.70 rows=141,026 width=8) (actual time=0.017..141.676 rows=193,477 loops=1)

  • Filter: ((score IS NOT NULL) AND newest)
  • Rows Removed by Filter: 65,906
Planning time : 6.433 ms
Execution time : 17,445.956 ms