explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QLtC

Settings
# exclusive inclusive rows x rows loops node
1. 21.454 12,585.778 ↓ 1,419.0 1,419 1

Nested Loop Left Join (cost=29,833.19..31,445.64 rows=1 width=24) (actual time=61.483..12,585.778 rows=1,419 loops=1)

  • Join Filter: (t.referrer_id = b_1.user_id)
  • Rows Removed by Join Filter: 345294
2. 3.142 85.638 ↓ 1,419.0 1,419 1

Nested Loop Left Join (cost=21,588.95..23,148.25 rows=1 width=23) (actual time=51.171..85.638 rows=1,419 loops=1)

3. 13.321 76.820 ↓ 1,419.0 1,419 1

Hash Left Join (cost=21,588.66..23,147.66 rows=1 width=16) (actual time=51.159..76.820 rows=1,419 loops=1)

  • Hash Cond: (((t.id)::character varying)::text = (l.lead_id)::text)
  • Filter: (l.id IS NULL)
  • Rows Removed by Filter: 6822
4. 5.079 32.655 ↓ 7.5 8,241 1

Hash Join (cost=16,065.58..17,068.84 rows=1,092 width=16) (actual time=19.652..32.655 rows=8,241 loops=1)

  • Hash Cond: (t.id = b.id)
5. 7.936 7.936 ↓ 1.1 12,297 1

Seq Scan on "user" t (cost=0.00..975.08 rows=10,732 width=16) (actual time=0.007..7.936 rows=12,297 loops=1)

  • Filter: ((referrer_id IS NOT NULL) AND (created_at < '2020-01-27 00:00:00'::timestamp without time zone) AND ((role)::text = 'blogger'::text))
  • Rows Removed by Filter: 4708
6. 0.802 19.640 ↓ 5.4 9,267 1

Hash (cost=16,043.94..16,043.94 rows=1,731 width=4) (actual time=19.640..19.640 rows=9,267 loops=1)

  • Buckets: 16384 (originally 2048) Batches: 1 (originally 1) Memory Usage: 454kB
7. 0.541 18.838 ↓ 5.4 9,267 1

Subquery Scan on b (cost=15,863.09..16,043.94 rows=1,731 width=4) (actual time=16.219..18.838 rows=9,267 loops=1)

8. 9.855 18.297 ↓ 5.4 9,267 1

HashAggregate (cost=15,863.09..16,026.63 rows=1,731 width=24) (actual time=16.218..18.297 rows=9,267 loops=1)

  • Group Key: t_1.user_id
  • Filter: ((max(t_1.subscribers) >= 3000) AND ((max(t_1.er) >= '0.0100000000000000002'::double precision) OR (sum((((t_1.network)::text <> 'instagram'::text))::integer) > 0)))
  • Rows Removed by Filter: 4678
9. 7.547 8.442 ↑ 1.0 14,180 1

Bitmap Heap Scan on blogger t_1 (cost=278.03..15,643.21 rows=14,659 width=25) (actual time=1.855..8.442 rows=14,180 loops=1)

  • Recheck Cond: (user_id IS NOT NULL)
  • Heap Blocks: exact=8842
10. 0.895 0.895 ↑ 1.0 14,180 1

Bitmap Index Scan on blogger_user_id_idx (cost=0.00..274.36 rows=14,659 width=0) (actual time=0.895..0.895 rows=14,180 loops=1)

  • Index Cond: (user_id IS NOT NULL)
11. 18.114 30.844 ↑ 1.0 107,648 1

Hash (cost=3,651.48..3,651.48 rows=107,648 width=15) (actual time=30.844..30.844 rows=107,648 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3557kB
12. 12.730 12.730 ↑ 1.0 107,648 1

Seq Scan on lead l (cost=0.00..3,651.48 rows=107,648 width=15) (actual time=0.007..12.730 rows=107,648 loops=1)

13. 5.676 5.676 ↑ 1.0 1 1,419

Index Scan using user_pkey on "user" r (cost=0.29..0.59 rows=1 width=11) (actual time=0.004..0.004 rows=1 loops=1,419)

  • Index Cond: (t.referrer_id = id)
14. 68.112 12,478.686 ↑ 1.6 244 1,419

Finalize GroupAggregate (cost=8,244.25..8,288.86 rows=379 width=8) (actual time=8.679..8.794 rows=244 loops=1,419)

  • Group Key: b_1.user_id
15. 5,414.904 12,410.574 ↑ 1.2 257 1,419

Gather Merge (cost=8,244.25..8,283.49 rows=316 width=8) (actual time=8.677..8.746 rows=257 loops=1,419)

  • Workers Planned: 2
  • Workers Launched: 2
16. 28.380 6,995.670 ↑ 1.7 94 4,257 / 3

Partial GroupAggregate (cost=7,244.22..7,246.99 rows=158 width=8) (actual time=4.909..4.930 rows=94 loops=4,257)

  • Group Key: b_1.user_id
17. 55.341 6,967.290 ↑ 1.6 99 4,257 / 3

Sort (cost=7,244.22..7,244.62 rows=158 width=8) (actual time=4.905..4.910 rows=99 loops=4,257)

  • Sort Key: b_1.user_id
  • Sort Method: quicksort Memory: 35kB
  • Worker 0: Sort Method: quicksort Memory: 28kB
  • Worker 1: Sort Method: quicksort Memory: 27kB
18. 66.693 6,911.949 ↑ 1.4 112 4,257 / 3

Nested Loop Left Join (cost=0.42..7,238.45 rows=158 width=8) (actual time=0.094..4.871 rows=112 loops=4,257)

19. 6,209.544 6,209.544 ↑ 1.4 112 4,257 / 3

Parallel Seq Scan on post t_2 (cost=0.00..6,098.76 rows=158 width=8) (actual time=0.078..4.376 rows=112 loops=4,257)

  • Filter: ((project_id = 54) AND ((status)::text = 'published'::text))
  • Rows Removed by Filter: 17315
20. 635.712 635.712 ↑ 1.0 1 476,784 / 3

Index Scan using blogger_pkey on blogger b_1 (cost=0.42..7.21 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=476,784)

  • Index Cond: (t_2.blogger_id = id)
Planning time : 0.636 ms
Execution time : 12,588.640 ms