explain.depesz.com

PostgreSQL's explain analyze made readable

Result: e3ZY

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 16.825 ↓ 0.0 0 1

Unique (cost=365.22..392.97 rows=137 width=3,195) (actual time=16.825..16.825 rows=0 loops=1)

2. 0.051 16.824 ↓ 0.0 0 1

Sort (cost=365.22..365.57 rows=137 width=3,195) (actual time=16.824..16.824 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.002 16.773 ↓ 0.0 0 1

HashAggregate (cost=358.99..360.36 rows=137 width=3,195) (actual time=16.773..16.773 rows=0 loops=1)

  • Group Key: users.id, lead_lead.id
4. 0.004 16.771 ↓ 0.0 0 1

Hash Right Join (cost=283.43..357.62 rows=137 width=3,184) (actual time=16.771..16.771 rows=0 loops=1)

  • Hash Cond: (rooms.id = rooms_reservations.room_id)
5. 0.000 0.000 ↓ 0.0 0

Seq Scan on rooms (cost=0.00..69.55 rows=655 width=25) (never executed)

6. 0.001 16.767 ↓ 0.0 0 1

Hash (cost=281.71..281.71 rows=137 width=3,167) (actual time=16.767..16.767 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
7. 0.002 16.766 ↓ 0.0 0 1

Hash Right Join (cost=259.09..281.71 rows=137 width=3,167) (actual time=16.766..16.766 rows=0 loops=1)

  • Hash Cond: (rooms_reservations.user_id = users.id)
8. 0.000 0.000 ↓ 0.0 0

Seq Scan on rooms_reservations (cost=0.00..18.76 rows=776 width=12) (never executed)

9. 0.001 16.764 ↓ 0.0 0 1

Hash (cost=257.38..257.38 rows=137 width=3,159) (actual time=16.764..16.764 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
10. 10.622 16.763 ↓ 0.0 0 1

Merge Left Join (cost=236.83..257.38 rows=137 width=3,159) (actual time=16.763..16.763 rows=0 loops=1)

  • Merge Cond: (users.id = lead_lead.user_id)
  • Filter: (((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((t7.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: 4781
11. 1.285 4.991 ↓ 6.2 4,781 1

Merge Left Join (cost=236.55..241.17 rows=776 width=2,941) (actual time=3.325..4.991 rows=4,781 loops=1)

  • Merge Cond: (users.id = t7.history_relation_id)
12. 0.808 3.393 ↓ 1.0 801 1

Sort (cost=228.08..230.02 rows=776 width=2,909) (actual time=3.251..3.393 rows=801 loops=1)

  • Sort Key: users.id
  • Sort Method: quicksort Memory: 1029kB
13. 0.242 2.585 ↓ 1.0 801 1

Hash Left Join (cost=166.69..190.83 rows=776 width=2,909) (actual time=1.654..2.585 rows=801 loops=1)

  • Hash Cond: (users.id = users_historicaluser.history_relation_id)
14. 0.506 2.279 ↑ 1.1 705 1

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

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

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

  • Filter: (id IS NOT NULL)
16. 0.840 1.567 ↑ 1.0 1,119 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 963kB
17. 0.727 0.727 ↑ 1.0 1,119 1

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

18. 0.017 0.064 ↓ 1.2 74 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
19. 0.047 0.047 ↓ 1.2 74 1

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

20. 0.267 0.313 ↓ 66.2 4,106 1

Sort (cost=8.47..8.62 rows=62 width=36) (actual time=0.070..0.313 rows=4,106 loops=1)

  • Sort Key: t7.history_relation_id
  • Sort Method: quicksort Memory: 29kB
21. 0.046 0.046 ↓ 1.2 74 1

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

22. 1.150 1.150 ↑ 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.014..1.150 rows=1,106 loops=1)

Planning time : 3.352 ms
Execution time : 17.136 ms