explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dtUf

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 191.872 ↑ 1.0 100 1

Limit (cost=30,855.74..30,875.99 rows=100 width=3,195) (actual time=191.756..191.872 rows=100 loops=1)

2. 0.102 191.864 ↑ 103.5 100 1

Unique (cost=30,855.74..32,950.80 rows=10,346 width=3,195) (actual time=191.755..191.864 rows=100 loops=1)

3. 0.708 191.762 ↑ 103.5 100 1

Sort (cost=30,855.74..30,881.60 rows=10,346 width=3,195) (actual time=191.755..191.762 rows=100 loops=1)

  • Sort Key: users.id, users.password, users.last_login, users.is_superuser, users.first_name, users.last_name, users.email, users.phone, users.photo, users.salesforce_data, users.is_staff, users.is_active, users.created, users.updated, users.webhook_id, users.nickname, users.about, users.note, users.bio, users.in_blacklist, users.subscribe, users.birthday, users.country, users.city, users.occupation, (max(rooms_reservations.end_date)), (max((rooms.system_name)::text)), lead_lead.id, lead_lead.created, lead_lead.modified, lead_lead.session_key, lead_lead.web_source, lead_lead.source_id, lead_lead.request_for_package, lead_lead.status, lead_lead.status_changed, lead_lead.status_group, lead_lead.lost_note, lead_lead.from_june_os, lead_lead.first_name, lead_lead.last_name, lead_lead.phone, lead_lead.email, lead_lead.emails_secondary, lead_lead.email_consent, lead_lead.residence_id, lead_lead.price_from, lead_lead.price_to, lead_lead.move_in_from, lead_lead.city_id, lead_lead.move_out, lead_lead.home_type, lead_lead.from_smartform, lead_lead.from_sales_force, lead_lead.from_sign_up, lead_lead.from_social_network_sign_up, lead_lead.salesforce_id, lead_lead.salesforce_status, lead_lead.room_id, lead_lead.created_by_id, lead_lead.updated_by_id, lead_lead.landing_page_url, lead_lead.http_referer, lead_lead.utm_source, lead_lead.utm_medium, lead_lead.utm_campaign, lead_lead.gclid, lead_lead.fbclid, lead_lead.ga, lead_lead.gid, lead_lead.user_id, lead_lead.owner_id, lead_lead.parent_id, lead_lead.note, lead_lead.background_check_report_member1, lead_lead.background_check_report_member2, lead_lead.background_check_report_guarantor1, lead_lead.background_check_report_guarantor2, lead_lead.marketing_inquired_room_id, lead_lead.marketing_facebook_ad_id
  • Sort Method: quicksort Memory: 1137kB
4. 15.686 191.054 ↑ 17.8 580 1

GroupAggregate (cost=15,681.04..15,913.83 rows=10,346 width=3,195) (actual time=165.780..191.054 rows=580 loops=1)

  • Group Key: users.id, lead_lead.id
5. 77.495 175.368 ↓ 3.8 38,961 1

Sort (cost=15,681.04..15,706.91 rows=10,346 width=3,184) (actual time=165.594..175.368 rows=38,961 loops=1)

  • Sort Key: users.id, lead_lead.id
  • Sort Method: external sort Disk: 21704kB
6. 46.282 97.873 ↓ 3.8 38,961 1

Merge Left Join (cost=783.36..809.13 rows=10,346 width=3,184) (actual time=32.250..97.873 rows=38,961 loops=1)

  • Merge Cond: (users.id = lead_lead.user_id)
  • Filter: (((upper((users.first_name)::text) ~~ '%A%'::text) OR (upper((users.last_name)::text) ~~ '%A%'::text) OR (upper((users.phone)::text) ~~ '%A%'::text) OR (upper((users.email)::text) ~~ '%A%'::text) OR (upper((users.nickname)::text) ~~ '%A%'::text) OR (upper((residences.title)::text) ~~ '%A%'::text) OR (upper((residences.system_name)::text) ~~ '%A%'::text) OR (upper((t6.system_name)::text) ~~ '%A%'::text) OR (upper((t6.title)::text) ~~ '%A%'::text) OR (upper((users_historicaluser.email)::text) ~~ '%A%'::text) OR (upper((lead_lead.email)::text) ~~ '%A%'::text) OR (upper((lead_lead.emails_secondary)::text) ~~ '%A%'::text)) AND ((upper((users.first_name)::text) ~~ '%A%'::text) OR (upper((users.last_name)::text) ~~ '%A%'::text) OR (upper((users.phone)::text) ~~ '%A%'::text) OR (upper((users.email)::text) ~~ '%A%'::text) OR (upper((users.nickname)::text) ~~ '%A%'::text) OR (upper((t12.title)::text) ~~ '%A%'::text) OR (upper((t12.system_name)::text) ~~ '%A%'::text) OR (upper((t11.system_name)::text) ~~ '%A%'::text) OR (upper((t11.title)::text) ~~ '%A%'::text) OR (upper((t13.email)::text) ~~ '%A%'::text) OR (upper((lead_lead.email)::text) ~~ '%A%'::text) OR (upper((lead_lead.emails_secondary)::text) ~~ '%A%'::text)))
7. 12.774 50.380 ↓ 50.2 38,961 1

Merge Left Join (cost=783.08..787.70 rows=776 width=3,116) (actual time=32.211..50.380 rows=38,961 loops=1)

  • Merge Cond: (users.id = t13.history_relation_id)
8. 19.365 34.962 ↓ 9.2 7,121 1

Sort (cost=774.61..776.55 rows=776 width=3,084) (actual time=32.153..34.962 rows=7,121 loops=1)

  • Sort Key: users.id
  • Sort Method: external merge Disk: 6616kB
9. 2.308 15.597 ↓ 9.2 7,121 1

Hash Left Join (cost=684.64..737.36 rows=776 width=3,084) (actual time=6.836..15.597 rows=7,121 loops=1)

  • Hash Cond: (t11.residence_id = t12.id)
10. 2.171 13.132 ↓ 9.2 7,121 1

Hash Left Join (cost=591.14..641.78 rows=776 width=3,053) (actual time=6.667..13.132 rows=7,121 loops=1)

  • Hash Cond: (users.id = t10.user_id)
11. 0.688 10.257 ↓ 3.3 2,541 1

Hash Left Join (cost=482.90..519.45 rows=776 width=3,009) (actual time=5.953..10.257 rows=2,541 loops=1)

  • Hash Cond: (users.id = users_historicaluser.history_relation_id)
12. 0.803 9.519 ↓ 2.8 2,157 1

Hash Left Join (cost=475.50..508.71 rows=776 width=2,977) (actual time=5.893..9.519 rows=2,157 loops=1)

  • Hash Cond: (t6.residence_id = residences.id)
13. 0.864 8.540 ↓ 2.8 2,157 1

Hash Left Join (cost=382.00..413.13 rows=776 width=2,946) (actual time=5.699..8.540 rows=2,157 loops=1)

  • Hash Cond: (t5.room_id = t6.id)
14. 0.730 7.361 ↓ 2.8 2,157 1

Hash Left Join (cost=304.27..333.34 rows=776 width=2,906) (actual time=5.373..7.361 rows=2,157 loops=1)

  • Hash Cond: (rooms_reservations.room_id = rooms.id)
15. 1.113 6.267 ↓ 2.8 2,157 1

Hash Right Join (cost=226.53..253.56 rows=776 width=2,889) (actual time=4.995..6.267 rows=2,157 loops=1)

  • Hash Cond: (t5.user_id = users.id)
16. 0.179 0.179 ↑ 1.1 705 1

Seq Scan on rooms_reservations t5 (cost=0.00..18.76 rows=776 width=8) (actual time=0.007..0.179 rows=705 loops=1)

17. 1.218 4.975 ↓ 1.4 1,049 1

Hash (cost=216.83..216.83 rows=776 width=2,885) (actual time=4.975..4.975 rows=1,049 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1161kB
18. 0.574 3.757 ↓ 1.4 1,049 1

Hash Right Join (cost=189.80..216.83 rows=776 width=2,885) (actual time=3.081..3.757 rows=1,049 loops=1)

  • Hash Cond: (rooms_reservations.user_id = users.id)
19. 0.128 0.128 ↑ 1.1 705 1

Seq Scan on rooms_reservations (cost=0.00..18.76 rows=776 width=12) (actual time=0.009..0.128 rows=705 loops=1)

20. 0.855 3.055 ↑ 1.1 705 1

Hash (cost=180.10..180.10 rows=776 width=2,877) (actual time=3.055..3.055 rows=705 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 828kB
21. 0.440 2.200 ↑ 1.1 705 1

Hash Join (cost=159.29..180.10 rows=776 width=2,877) (actual time=1.635..2.200 rows=705 loops=1)

  • Hash Cond: (t4.user_id = users.id)
22. 0.146 0.146 ↑ 1.1 705 1

Seq Scan on rooms_reservations t4 (cost=0.00..18.76 rows=776 width=4) (actual time=0.008..0.146 rows=705 loops=1)

  • Filter: (id IS NOT NULL)
23. 1.034 1.614 ↑ 1.0 1,119 1

Hash (cost=145.24..145.24 rows=1,124 width=2,877) (actual time=1.614..1.614 rows=1,119 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 963kB
24. 0.580 0.580 ↑ 1.0 1,119 1

Seq Scan on users (cost=0.00..145.24 rows=1,124 width=2,877) (actual time=0.010..0.580 rows=1,119 loops=1)

25. 0.116 0.364 ↑ 1.0 655 1

Hash (cost=69.55..69.55 rows=655 width=25) (actual time=0.364..0.364 rows=655 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 46kB
26. 0.248 0.248 ↑ 1.0 655 1

Seq Scan on rooms (cost=0.00..69.55 rows=655 width=25) (actual time=0.004..0.248 rows=655 loops=1)

27. 0.137 0.315 ↑ 1.0 655 1

Hash (cost=69.55..69.55 rows=655 width=48) (actual time=0.315..0.315 rows=655 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 60kB
28. 0.178 0.178 ↑ 1.0 655 1

Seq Scan on rooms t6 (cost=0.00..69.55 rows=655 width=48) (actual time=0.003..0.178 rows=655 loops=1)

29. 0.039 0.176 ↑ 1.0 200 1

Hash (cost=91.00..91.00 rows=200 width=39) (actual time=0.176..0.176 rows=200 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
30. 0.137 0.137 ↑ 1.0 200 1

Seq Scan on residences (cost=0.00..91.00 rows=200 width=39) (actual time=0.009..0.137 rows=200 loops=1)

31. 0.017 0.050 ↓ 1.2 74 1

Hash (cost=6.62..6.62 rows=62 width=36) (actual time=0.050..0.050 rows=74 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
32. 0.033 0.033 ↓ 1.2 74 1

Seq Scan on users_historicaluser (cost=0.00..6.62 rows=62 width=36) (actual time=0.004..0.033 rows=74 loops=1)

33. 0.143 0.704 ↑ 1.1 705 1

Hash (cost=98.55..98.55 rows=776 width=48) (actual time=0.704..0.704 rows=705 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 63kB
34. 0.163 0.561 ↑ 1.1 705 1

Hash Left Join (cost=77.74..98.55 rows=776 width=48) (actual time=0.318..0.561 rows=705 loops=1)

  • Hash Cond: (t10.room_id = t11.id)
35. 0.095 0.095 ↑ 1.1 705 1

Seq Scan on rooms_reservations t10 (cost=0.00..18.76 rows=776 width=8) (actual time=0.004..0.095 rows=705 loops=1)

36. 0.134 0.303 ↑ 1.0 655 1

Hash (cost=69.55..69.55 rows=655 width=48) (actual time=0.303..0.303 rows=655 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 60kB
37. 0.169 0.169 ↑ 1.0 655 1

Seq Scan on rooms t11 (cost=0.00..69.55 rows=655 width=48) (actual time=0.002..0.169 rows=655 loops=1)

38. 0.040 0.157 ↑ 1.0 200 1

Hash (cost=91.00..91.00 rows=200 width=39) (actual time=0.157..0.157 rows=200 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
39. 0.117 0.117 ↑ 1.0 200 1

Seq Scan on residences t12 (cost=0.00..91.00 rows=200 width=39) (actual time=0.004..0.117 rows=200 loops=1)

40. 2.610 2.644 ↓ 527.5 32,708 1

Sort (cost=8.47..8.62 rows=62 width=36) (actual time=0.054..2.644 rows=32,708 loops=1)

  • Sort Key: t13.history_relation_id
  • Sort Method: quicksort Memory: 29kB
41. 0.034 0.034 ↓ 1.2 74 1

Seq Scan on users_historicaluser t13 (cost=0.00..6.62 rows=62 width=36) (actual time=0.008..0.034 rows=74 loops=1)

42. 1.211 1.211 ↑ 13.5 1,106 1

Index Scan using lead_lead_user_id_91223f04_uniq on lead_lead (cost=0.29..1,941.00 rows=14,985 width=282) (actual time=0.011..1.211 rows=1,106 loops=1)