explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SpLA

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 12.284 ↑ 4.0 5 1

Limit (cost=25,283.48..25,285.23 rows=20 width=612) (actual time=12.281..12.284 rows=5 loops=1)

  • Buffers: shared hit=1001 read=9
2. 0.003 12.283 ↑ 40.0 5 1

Unique (cost=25,283.48..25,300.98 rows=200 width=612) (actual time=12.28..12.283 rows=5 loops=1)

  • Buffers: shared hit=1001 read=9
3. 0.076 12.280 ↑ 40.0 5 1

Sort (cost=25,283.48..25,283.98 rows=200 width=612) (actual time=12.28..12.28 rows=5 loops=1)

  • Sort Key: (similarity((claims.number)::text, 'MQT3K3'::text)) DESC, claims.id, claims.broadspire_event_number, claims.policy_id, claims.created_at, claims.updated_at, claims.first_notice_of_loss_id, claims.policy_term_revision_id, claims.number, claims.assigned_user_id, claims.contacts_completed, claims.coverage_determined, claims.first_closed_at, claims.incident_datetime, claims.incident_location_additional_details, claims.incident_location_city, claims.incident_location_description, claims.incident_location_state, claims.incident_location_street_address1, claims.incident_location_street_address2, claims.incident_location_zip, claims.incident_timezone, claims.incident_type, claims.injuries_addressed, claims.liability_determined, claims.property_damage_assesed, claims.status, claims.catastrophe_related, claims.state_compliance_reviewed, claims.policy_number, claims.facts_of_loss, claims.last_opened_at, claims.closed_without_feature_reason, claims.attempt_early_settle_offer
  • Sort Method: quicksort Memory: 27kB
  • Buffers: shared hit=1001 read=9
4. 0.002 12.204 ↑ 40.0 5 1

Unique (cost=25,265.82..25,273.84 rows=200 width=612) (actual time=12.202..12.204 rows=5 loops=1)

  • Buffers: shared hit=976 read=9
5. 0.018 12.202 ↑ 321.0 5 1

Sort (cost=25,265.82..25,269.83 rows=1,605 width=612) (actual time=12.201..12.202 rows=5 loops=1)

  • Sort Key: claims.id
  • Sort Method: quicksort Memory: 27kB
  • Buffers: shared hit=976 read=9
6. 0.040 12.184 ↑ 321.0 5 1

Aggregate (cost=25,148.26..25,164.31 rows=1,605 width=612) (actual time=12.175..12.184 rows=5 loops=1)

  • Buffers: shared hit=976 read=9
7. 0.002 12.144 ↑ 321.0 5 1

Append (cost=180.89..25,011.84 rows=1,605 width=612) (actual time=0.588..12.144 rows=5 loops=1)

  • Buffers: shared hit=976 read=9
8. 0.027 0.595 ↑ 115.0 1 1

Bitmap Heap Scan on claims claims (cost=180.89..584.82 rows=115 width=321) (actual time=0.588..0.595 rows=1 loops=1)

  • Heap Blocks: exact=6
  • Buffers: shared hit=51 read=1
9. 0.568 0.568 ↑ 19.2 6 1

Bitmap Index Scan on claim_number_search_index (cost=0..180.87 rows=115 width=0) (actual time=0.568..0.568 rows=6 loops=1)

  • Index Cond: ((claims.number)::text % 'MQT3K3'::text)
  • Buffers: shared hit=45 read=1
10. 0.012 2.789 ↑ 28.8 4 1

Nested Loop (cost=512.52..7,624.14 rows=115 width=321) (actual time=2.675..2.789 rows=4 loops=1)

  • Buffers: shared hit=198 read=1
11. 0.099 2.747 ↑ 106.0 5 1

Bitmap Heap Scan on policies policies (cost=512.11..2,105.54 rows=530 width=23) (actual time=2.658..2.747 rows=5 loops=1)

  • Heap Blocks: exact=43
  • Buffers: shared hit=179 read=1
12. 2.648 2.648 ↑ 12.3 43 1

Bitmap Index Scan on policy_number_search_index (cost=0..511.97 rows=530 width=0) (actual time=2.648..2.648 rows=43 loops=1)

  • Index Cond: ((policies.number)::text % 'MQT3K3'::text)
  • Buffers: shared hit=136 read=1
13. 0.030 0.030 ↑ 2.0 1 5

Index Scan using index_claims_on_policy_id on claims claims_1 (cost=0.42..10.39 rows=2 width=317) (actual time=0.005..0.006 rows=1 loops=5)

  • Index Cond: (claims_1.policy_id = policies.id)
  • Buffers: shared hit=19
14. 0.000 3.722 ↓ 0.0 0 1

Nested Loop (cost=1,083.05..8,925.58 rows=803 width=321) (actual time=3.722..3.722 rows=0 loops=1)

  • Buffers: shared hit=280 read=6
15. 0.003 3.722 ↓ 0.0 0 1

Bitmap Heap Scan on claim_involved_parties claim_involved_parties (cost=1,082.63..3,508.23 rows=803 width=54) (actual time=3.722..3.722 rows=0 loops=1)

  • Buffers: shared hit=280 read=6
16. 0.002 3.719 ↓ 0.0 0 1

BitmapOr (cost=1,082.63..1,082.63 rows=803 width=0) (actual time=3.719..3.719 rows=0 loops=1)

  • Buffers: shared hit=280 read=6
17. 1.419 1.419 ↓ 0.0 0 1

Bitmap Index Scan on involved_parties_name_search_index (cost=0..430.01 rows=268 width=0) (actual time=1.419..1.419 rows=0 loops=1)

  • Index Cond: ((((claim_involved_parties.first_name)::text || ' '::text) || (claim_involved_parties.last_name)::text) % 'MQT3K3'::text)
  • Buffers: shared hit=106 read=3
18. 1.165 1.165 ↓ 0.0 0 1

Bitmap Index Scan on involved_parties_phone_search_index (cost=0..274.01 rows=268 width=0) (actual time=1.165..1.165 rows=0 loops=1)

  • Index Cond: ((claim_involved_parties.phone_number)::text % 'MQT3K3'::text)
  • Buffers: shared hit=72 read=1
19. 1.133 1.133 ↓ 0.0 0 1

Bitmap Index Scan on involved_parties_email_search_index (cost=0..378.01 rows=268 width=0) (actual time=1.133..1.133 rows=0 loops=1)

  • Index Cond: ((claim_involved_parties.email)::text % 'MQT3K3'::text)
  • Buffers: shared hit=102 read=2
20. 0.000 0.000 ↓ 0.0 0 0

Index Scan using claims_pkey on claims claims_2 (cost=0.42..6.73 rows=1 width=317) (never executed)

  • Index Cond: (claims_2.id = claim_involved_parties.claim_id)
21. 0.000 3.854 ↓ 0.0 0 1

Nested Loop (cost=1,282.46..3,802.31 rows=240 width=321) (actual time=3.854..3.854 rows=0 loops=1)

  • Buffers: shared hit=324
22. 0.001 3.854 ↓ 0.0 0 1

Bitmap Heap Scan on claim_associated_parties claim_associated_parties (cost=1,282.04..1,925.71 rows=240 width=60) (actual time=3.854..3.854 rows=0 loops=1)

  • Buffers: shared hit=324
23. 0.001 3.853 ↓ 0.0 0 1

BitmapOr (cost=1,282.04..1,282.04 rows=240 width=0) (actual time=3.853..3.853 rows=0 loops=1)

  • Buffers: shared hit=324
24. 0.786 0.786 ↓ 0.0 0 1

Bitmap Index Scan on associated_parties_name_search_index (cost=0..228.45 rows=60 width=0) (actual time=0.786..0.786 rows=0 loops=1)

  • Index Cond: ((((claim_associated_parties.first_name)::text || ' '::text) || (claim_associated_parties.last_name)::text) % 'MQT3K3'::text)
  • Buffers: shared hit=58
25. 1.488 1.488 ↓ 0.0 0 1

Bitmap Index Scan on associated_parties_company_name_search_index (cost=0..492.45 rows=60 width=0) (actual time=1.488..1.488 rows=0 loops=1)

  • Index Cond: ((claim_associated_parties.company_name)::text % 'MQT3K3'::text)
  • Buffers: shared hit=125
26. 0.824 0.824 ↓ 0.0 0 1

Bitmap Index Scan on associated_parties_phone_search_index (cost=0..292.45 rows=60 width=0) (actual time=0.823..0.824 rows=0 loops=1)

  • Index Cond: ((claim_associated_parties.phone)::text % 'MQT3K3'::text)
  • Buffers: shared hit=73
27. 0.754 0.754 ↓ 0.0 0 1

Bitmap Index Scan on associated_parties_email_search_index (cost=0..268.45 rows=60 width=0) (actual time=0.754..0.754 rows=0 loops=1)

  • Index Cond: ((claim_associated_parties.email)::text % 'MQT3K3'::text)
  • Buffers: shared hit=68
28. 0.000 0.000 ↓ 0.0 0 0

Index Scan using claims_pkey on claims claims_3 (cost=0.42..7.8 rows=1 width=317) (never executed)

  • Index Cond: (claims_3.id = claim_associated_parties.claim_id)
29. 0.001 1.182 ↓ 0.0 0 1

Nested Loop (cost=495.08..4,058.93 rows=332 width=321) (actual time=1.182..1.182 rows=0 loops=1)

  • Buffers: shared hit=123 read=1
30. 0.002 1.181 ↓ 0.0 0 1

Bitmap Heap Scan on claim_vehicles claim_vehicles (cost=494.66..1,531.04 rows=332 width=48) (actual time=1.181..1.181 rows=0 loops=1)

  • Buffers: shared hit=123 read=1
31. 0.002 1.179 ↓ 0.0 0 1

BitmapOr (cost=494.66..494.66 rows=332 width=0) (actual time=1.179..1.179 rows=0 loops=1)

  • Buffers: shared hit=123 read=1
32. 0.701 0.701 ↓ 0.0 0 1

Bitmap Index Scan on vehicle_name_search_index (cost=0..261.25 rows=166 width=0) (actual time=0.701..0.701 rows=0 loops=1)

  • Index Cond: ((((((claim_vehicles.year)::text || ' '::text) || (claim_vehicles.make)::text) || ' '::text) || (claim_vehicles.model)::text) % 'MQT3K3'::text)
  • Buffers: shared hit=65 read=1
33. 0.476 0.476 ↓ 0.0 0 1

Bitmap Index Scan on vehicle_vin_search_index (cost=0..233.25 rows=166 width=0) (actual time=0.476..0.476 rows=0 loops=1)

  • Index Cond: ((claim_vehicles.vin)::text % 'MQT3K3'::text)
  • Buffers: shared hit=58
34. 0.000 0.000 ↓ 0.0 0 0

Index Scan using claims_pkey on claims claims_4 (cost=0.42..7.59 rows=1 width=317) (never executed)

  • Index Cond: (claims_4.id = claim_vehicles.claim_id)
Planning time : 3.761 ms
Execution time : 12.846 ms