explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dtFl

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

Unique (cost=5,274.65..5,588.93 rows=1,552 width=3,195) (actual time=416.335..416.335 rows=0 loops=1)

2. 0.152 416.335 ↓ 0.0 0 1

Sort (cost=5,274.65..5,278.53 rows=1,552 width=3,195) (actual time=416.335..416.335 rows=0 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: 25kB
3. 0.001 416.183 ↓ 0.0 0 1

GroupAggregate (cost=3,018.97..3,053.89 rows=1,552 width=3,195) (actual time=416.183..416.183 rows=0 loops=1)

  • Group Key: users.id, lead_lead.id
4. 0.007 416.182 ↓ 0.0 0 1

Sort (cost=3,018.97..3,022.85 rows=1,552 width=3,184) (actual time=416.182..416.182 rows=0 loops=1)

  • Sort Key: users.id, lead_lead.id
  • Sort Method: quicksort Memory: 25kB
5. 320.662 416.175 ↓ 0.0 0 1

Merge Right Join (cost=785.63..808.72 rows=1,552 width=3,184) (actual time=416.175..416.175 rows=0 loops=1)

  • Merge Cond: (lead_lead.user_id = users.id)
  • Filter: (((upper((users.first_name)::text) ~~ '%RED%'::text) OR (upper((users.last_name)::text) ~~ '%RED%'::text) OR (upper((users.phone)::text) ~~ '%RED%'::text) OR (upper((users.email)::text) ~~ '%RED%'::text) OR (upper((users.nickname)::text) ~~ '%RED%'::text) OR (upper((residences.title)::text) ~~ '%RED%'::text) OR (upper((residences.system_name)::text) ~~ '%RED%'::text) OR (upper((t6.system_name)::text) ~~ '%RED%'::text) OR (upper((t6.title)::text) ~~ '%RED%'::text) OR (upper((users_historicaluser.email)::text) ~~ '%RED%'::text) OR (upper((lead_lead.email)::text) ~~ '%RED%'::text) OR (upper((lead_lead.emails_secondary)::text) ~~ '%RED%'::text)) AND ((upper((users.first_name)::text) ~~ '%HAT%'::text) OR (upper((users.last_name)::text) ~~ '%HAT%'::text) OR (upper((users.phone)::text) ~~ '%HAT%'::text) OR (upper((users.email)::text) ~~ '%HAT%'::text) OR (upper((users.nickname)::text) ~~ '%HAT%'::text) OR (upper((t12.title)::text) ~~ '%HAT%'::text) OR (upper((t12.system_name)::text) ~~ '%HAT%'::text) OR (upper((t11.system_name)::text) ~~ '%HAT%'::text) OR (upper((t11.title)::text) ~~ '%HAT%'::text) OR (upper((t13.email)::text) ~~ '%HAT%'::text) OR (upper((lead_lead.email)::text) ~~ '%HAT%'::text) OR (upper((lead_lead.emails_secondary)::text) ~~ '%HAT%'::text)))
  • Rows Removed by Filter: 38961
6. 1.037 1.037 ↑ 13.5 1,107 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.014..1.037 rows=1,107 loops=1)

7. 71.964 94.476 ↓ 50.2 38,961 1

Sort (cost=785.35..787.29 rows=776 width=3,116) (actual time=82.692..94.476 rows=38,961 loops=1)

  • Sort Key: users.id
  • Sort Method: external sort Disk: 18968kB
8. 8.171 22.512 ↓ 50.2 38,961 1

Hash Left Join (cost=692.04..748.10 rows=776 width=3,116) (actual time=5.958..22.512 rows=38,961 loops=1)

  • Hash Cond: (users.id = t13.history_relation_id)
9. 2.232 14.303 ↓ 9.2 7,121 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

17. 0.956 4.186 ↓ 1.4 1,049 1

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

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

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

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

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

20. 0.736 2.728 ↑ 1.1 705 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 828kB
21. 0.326 1.992 ↑ 1.1 705 1

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

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

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

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

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

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

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

25. 0.113 0.336 ↑ 1.0 655 1

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

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

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

27. 0.119 0.283 ↑ 1.0 655 1

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

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

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

29. 0.038 0.183 ↑ 1.0 200 1

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

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

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

31. 0.014 0.045 ↓ 1.2 74 1

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

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

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

33. 0.136 0.664 ↑ 1.1 705 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 63kB
34. 0.153 0.528 ↑ 1.1 705 1

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

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

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

36. 0.128 0.286 ↑ 1.0 655 1

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

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

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

38. 0.040 0.131 ↑ 1.0 200 1

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

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

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

40. 0.014 0.038 ↓ 1.2 74 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
41. 0.024 0.024 ↓ 1.2 74 1

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