explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZSp6

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 460,823.768 ↑ 1.0 25 1

Limit (cost=4.06..386.10 rows=25 width=5,874) (actual time=20.437..460,823.768 rows=25 loops=1)

2. 311.860 460,823.756 ↑ 47,758.6 25 1

Nested Loop (cost=4.06..18,245,283.08 rows=1,193,965 width=5,874) (actual time=20.437..460,823.756 rows=25 loops=1)

3. 3,167.624 3,167.624 ↑ 1.6 742,442 1

Seq Scan on leads (cost=0.00..271,353.22 rows=1,193,965 width=5,874) (actual time=0.014..3,167.624 rows=742,442 loops=1)

  • Filter: (((user_type)::text = ANY ('{buyer,seller}'::text[])) AND (created_at > '2018-06-01 18:21:04.995'::timestamp without time zone))
  • Rows Removed by Filter: 761,894
4. 742.442 457,344.272 ↓ 0.0 0 742,442

Subquery Scan on service_attachment_mortgage (cost=4.06..15.04 rows=1 width=0) (actual time=0.616..0.616 rows=0 loops=742,442)

  • Filter: (service_attachment_mortgage.lender_lead_id IS NOT NULL)
5. 3,712.210 456,601.830 ↓ 0.0 0 742,442

Limit (cost=4.06..15.03 rows=1 width=524) (actual time=0.615..0.615 rows=0 loops=742,442)

6. 72,020.034 452,889.620 ↓ 0.0 0 742,442

Nested Loop (cost=4.06..15.03 rows=1 width=524) (actual time=0.610..0.610 rows=0 loops=742,442)

  • Join Filter: (provider_leads.lead_id = child_leads.id)
  • Rows Removed by Join Filter: 1
7. 59,395.360 189,322.710 ↓ 129.0 129 742,442

Merge Join (cost=3.63..6.57 rows=1 width=8) (actual time=0.061..0.255 rows=129 loops=742,442)

  • Merge Cond: (provider_leads.providable_id = lender_leads.id)
8. 92,062.808 92,062.808 ↑ 59.0 158 742,442

Index Scan using index_provider_leads_on_providable_type_and_providable_id on provider_leads (cost=0.43..27,191.70 rows=9,328 width=8) (actual time=0.020..0.124 rows=158 loops=742,442)

  • Index Cond: ((providable_type)::text = 'LenderLead'::text)
9. 23,758.144 37,864.542 ↓ 1.4 71 742,442

Sort (cost=2.96..3.08 rows=51 width=4) (actual time=0.040..0.051 rows=71 loops=742,442)

  • Sort Key: lender_leads.id
  • Sort Method: quicksort Memory: 28kB
10. 14,106.398 14,106.398 ↓ 1.4 71 742,442

Seq Scan on lender_leads (cost=0.00..1.51 rows=51 width=4) (actual time=0.004..0.019 rows=71 loops=742,442)

11. 191,546.876 191,546.876 ↓ 0.0 0 95,773,438

Index Scan using index_leads_on_parent_lead_id on leads child_leads (cost=0.43..8.45 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=95,773,438)

  • Index Cond: (parent_lead_id = leads.id)
  • Filter: ((user_type)::text = 'mortgage'::text)
  • Rows Removed by Filter: 0
Planning time : 7.258 ms
Execution time : 460,823.915 ms