explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0bBa

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Unique (cost=21,867.80..21,867.87 rows=1 width=302) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=21,867.80..21,867.80 rows=1 width=302) (actual rows= loops=)

  • Sort Key: t.id, t.closed_at, t.worker_id, (min(t_3.closed_at)), t_3.client_phone, (to_char(t_3.closed_at, 'yyyy-mm'::text)), t_3.worker_id, (min(t_1.closed_at)), t_1.client_email, (to_char(t_1.closed_at, 'yyyy-mm'::text)), t_1.worker_id, (min(t_2.closed_at)), t_2.client_id, (to_char(t_2.closed_at, 'yyyy-mm'::text)), t_2.worker_id, (CASE WHEN (GREATEST(COALESCE(db1.sprzedaz, '1999-09-19 00:00:00'::timestamp without time zone), COALESCE(db2.sprzedaz, '1999-09-19 00:00:00'::timestamp without time zone), COALESCE(db3.sprzedaz, '1999-09-19 00:00:00'::timestamp without time zone)) <> '1999-09-19 00:00:00'::timestamp without time zone) THEN CASE WHEN ((t.closed_at >= GREATEST(COALESCE(db1.sprzedaz, '1999-09-19 00:00:00'::timestamp without time zone), COALESCE(db2.sprzedaz, '1999-09-19 00:00:00'::timestamp without time zone), COALESCE(db3.sprzedaz, '1999-09-19 00:00:00'::timestamp without time zone))) AND (t.closed_at <= GREATEST(COALESCE(db1.dzien, '1999-09-19'::date), COALESCE(db2.dzien, '1999-09-19'::date), COALESCE(db3.dzien, '1999-09-19'::date)))) THEN true ELSE false END ELSE false END), db1.order_id, db1.client_id, db1.sprzedaz, db1.dzien, db2.order_id, db2.phone, db2.sprzedaz, db2.dzien, db3.order_id, db3.email, db3.sprzedaz, db3.dzien
3. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=21,800.45..21,867.79 rows=1 width=302) (actual rows= loops=)

  • Merge Cond: ((t.client_phone)::text = (t_3.client_phone)::text)
  • Join Filter: ((t_3.worker_id = t.worker_id) AND ((to_char(t_3.closed_at, 'yyyy-mm'::text)) = to_char(t.closed_at, 'yyyy-mm'::text)))
  • Filter: (LEAST(COALESCE((min(t_3.closed_at)), '2222-02-02 00:00:00'::timestamp without time zone), COALESCE((min(t_1.closed_at)), '2222-02-02 00:00:00'::timestamp without time zone), COALESCE((min(t_2.closed_at)), '2222-02-02 00:00:00'::timestamp without time zone)) = t.closed_at)
4. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=16,603.25..16,639.35 rows=3,054 width=257) (actual rows= loops=)

  • Merge Cond: ((t.client_phone)::text = db2.phone)
  • Filter: (CASE WHEN (GREATEST(COALESCE(db1.sprzedaz, '1999-09-19 00:00:00'::timestamp without time zone), COALESCE(db2.sprzedaz, '1999-09-19 00:00:00'::timestamp without time zone), COALESCE(db3.sprzedaz, '1999-09-19 00:00:00'::timestamp without time zone)) <> '1999-09-19 00:00:00'::timestamp without time zone) THEN CASE WHEN ((t.closed_at >= GREATEST(COALESCE(db1.sprzedaz, '1999-09-19 00:00:00'::timestamp without time zone), COALESCE(db2.sprzedaz, '1999-09-19 00:00:00'::timestamp without time zone), COALESCE(db3.sprzedaz, '1999-09-19 00:00:00'::timestamp without time zone))) AND (t.closed_at <= GREATEST(COALESCE(db1.dzien, '1999-09-19'::date), COALESCE(db2.dzien, '1999-09-19'::date), COALESCE(db3.dzien, '1999-09-19'::date)))) THEN true ELSE false END ELSE false END IS FALSE)
5. 0.000 0.000 ↓ 0.0

Sort (cost=16,543.42..16,558.69 rows=6,107 width=209) (actual rows= loops=)

  • Sort Key: t.client_phone
6. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=16,082.76..16,159.40 rows=6,107 width=209) (actual rows= loops=)

  • Merge Cond: (t.client_id = t_2.client_id)
  • Join Filter: ((t_2.worker_id = t.worker_id) AND ((to_char(t_2.closed_at, 'yyyy-mm'::text)) = to_char(t.closed_at, 'yyyy-mm'::text)))
7. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=10,845.97..10,881.68 rows=6,107 width=165) (actual rows= loops=)

  • Merge Cond: (t.client_id = db1.client_id)
8. 0.000 0.000 ↓ 0.0

Sort (cost=10,786.13..10,801.40 rows=6,107 width=145) (actual rows= loops=)

  • Sort Key: t.client_id
9. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=10,201.96..10,402.12 rows=6,107 width=145) (actual rows= loops=)

  • Merge Cond: ((t.client_email)::text = (t_1.client_email)::text)
  • Join Filter: ((t_1.worker_id = t.worker_id) AND ((to_char(t_1.closed_at, 'yyyy-mm'::text)) = to_char(t.closed_at, 'yyyy-mm'::text)))
10. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=5,201.79..5,237.64 rows=6,107 width=101) (actual rows= loops=)

  • Merge Cond: ((t.client_email)::text = db3.email)
11. 0.000 0.000 ↓ 0.0

Sort (cost=5,141.96..5,157.23 rows=6,107 width=53) (actual rows= loops=)

  • Sort Key: t.client_email
12. 0.000 0.000 ↓ 0.0

Index Scan using idx_task_closed_at on task t (cost=0.43..4,757.95 rows=6,107 width=53) (actual rows= loops=)

  • Index Cond: ((closed_at >= '2019-05-01 00:00:00'::timestamp without time zone) AND (closed_at <= '2019-05-03 00:00:00'::timestamp without time zone))
13. 0.000 0.000 ↓ 0.0

Sort (cost=59.83..62.33 rows=1,000 width=48) (actual rows= loops=)

  • Sort Key: db3.email
14. 0.000 0.000 ↓ 0.0

Function Scan on dblink db3 (cost=0.00..10.00 rows=1,000 width=48) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Materialize (cost=5,000.16..5,138.03 rows=3,676 width=64) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=5,000.16..5,092.08 rows=3,676 width=64) (actual rows= loops=)

  • Group Key: t_1.client_email, (to_char(t_1.closed_at, 'yyyy-mm'::text)), t_1.worker_id
17. 0.000 0.000 ↓ 0.0

Sort (cost=5,000.16..5,009.36 rows=3,677 width=64) (actual rows= loops=)

  • Sort Key: t_1.client_email, (to_char(t_1.closed_at, 'yyyy-mm'::text)), t_1.worker_id
18. 0.000 0.000 ↓ 0.0

Index Scan using idx_task_closed_at on task t_1 (cost=0.43..4,782.41 rows=3,677 width=64) (actual rows= loops=)

  • Index Cond: ((closed_at >= '2019-05-01 00:00:00'::timestamp without time zone) AND (closed_at <= '2019-05-03 00:00:00'::timestamp without time zone))
  • Filter: ((client_email IS NOT NULL) AND (in_progress_time > 0))
19. 0.000 0.000 ↓ 0.0

Sort (cost=59.83..62.33 rows=1,000 width=20) (actual rows= loops=)

  • Sort Key: db1.client_id
20. 0.000 0.000 ↓ 0.0

Function Scan on dblink db1 (cost=0.00..10.00 rows=1,000 width=20) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Sort (cost=5,236.80..5,248.85 rows=4,820 width=48) (actual rows= loops=)

  • Sort Key: t_2.client_id
22. 0.000 0.000 ↓ 0.0

HashAggregate (cost=4,833.49..4,893.74 rows=4,820 width=48) (actual rows= loops=)

  • Group Key: t_2.client_id, to_char(t_2.closed_at, 'yyyy-mm'::text), t_2.worker_id
23. 0.000 0.000 ↓ 0.0

Index Scan using idx_task_closed_at on task t_2 (cost=0.43..4,785.27 rows=4,822 width=48) (actual rows= loops=)

  • Index Cond: ((closed_at >= '2019-05-01 00:00:00'::timestamp without time zone) AND (closed_at <= '2019-05-03 00:00:00'::timestamp without time zone))
  • Filter: (in_progress_time > 0)
24. 0.000 0.000 ↓ 0.0

Sort (cost=59.83..62.33 rows=1,000 width=48) (actual rows= loops=)

  • Sort Key: db2.phone
25. 0.000 0.000 ↓ 0.0

Function Scan on dblink db2 (cost=0.00..10.00 rows=1,000 width=48) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Sort (cost=5,197.20..5,208.29 rows=4,436 width=57) (actual rows= loops=)

  • Sort Key: t_3.client_phone
27. 0.000 0.000 ↓ 0.0

HashAggregate (cost=4,828.68..4,884.13 rows=4,436 width=57) (actual rows= loops=)

  • Group Key: t_3.client_phone, to_char(t_3.closed_at, 'yyyy-mm'::text), t_3.worker_id
28. 0.000 0.000 ↓ 0.0

Index Scan using idx_task_closed_at on task t_3 (cost=0.43..4,784.31 rows=4,437 width=57) (actual rows= loops=)

  • Index Cond: ((closed_at >= '2019-05-01 00:00:00'::timestamp without time zone) AND (closed_at <= '2019-05-03 00:00:00'::timestamp without time zone))
  • Filter: ((client_phone IS NOT NULL) AND (in_progress_time > 0))