explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1HB1 : Optimization for: plan #oav7

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.313 8.379 ↓ 26.4 211 1

Sort (cost=137.99..138.01 rows=8 width=776) (actual time=8.366..8.379 rows=211 loops=1)

  • Sort Key: ((con.search ~~ 'fn$%$|'::text))
  • Sort Method: quicksort Memory: 872kB
2.          

Initplan (for Sort)

3. 0.004 0.004 ↑ 1.0 1 1

Seq Scan on groups_clients groups_clients_1 (cost=0.00..1.40 rows=1 width=4) (actual time=0.002..0.004 rows=1 loops=1)

  • Filter: (client_alias_id = 25)
  • Rows Removed by Filter: 31
4. 3.073 8.062 ↓ 26.4 211 1

Nested Loop Left Join (cost=3.46..136.47 rows=8 width=776) (actual time=0.118..8.062 rows=211 loops=1)

5. 0.044 2.668 ↓ 26.4 211 1

Nested Loop Left Join (cost=2.11..124.41 rows=8 width=2,070) (actual time=0.061..2.668 rows=211 loops=1)

6. 0.086 1.147 ↓ 26.4 211 1

Nested Loop Left Join (cost=2.11..17.91 rows=8 width=2,038) (actual time=0.050..1.147 rows=211 loops=1)

7. 0.070 0.850 ↓ 26.4 211 1

Nested Loop Left Join (cost=1.83..14.40 rows=8 width=2,002) (actual time=0.044..0.850 rows=211 loops=1)

8. 0.155 0.569 ↓ 26.4 211 1

Nested Loop (cost=1.56..10.21 rows=8 width=1,963) (actual time=0.039..0.569 rows=211 loops=1)

9. 0.142 0.203 ↓ 26.4 211 1

Hash Join (cost=1.41..7.56 rows=8 width=1,463) (actual time=0.029..0.203 rows=211 loops=1)

  • Hash Cond: (p.created_alias_id = groups_clients.client_alias_id)
10. 0.048 0.048 ↓ 1.0 243 1

Seq Scan on patients p (cost=0.00..5.43 rows=241 width=1,467) (actual time=0.010..0.048 rows=243 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 2
11. 0.003 0.013 ↓ 4.0 4 1

Hash (cost=1.40..1.40 rows=1 width=4) (actual time=0.013..0.013 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
12. 0.010 0.010 ↓ 4.0 4 1

Seq Scan on groups_clients (cost=0.00..1.40 rows=1 width=4) (actual time=0.009..0.010 rows=4 loops=1)

  • Filter: (group_id = $109)
  • Rows Removed by Filter: 28
13. 0.211 0.211 ↑ 1.0 1 211

Index Scan using contacts_pkey on contacts con (cost=0.15..0.33 rows=1 width=504) (actual time=0.001..0.001 rows=1 loops=211)

  • Index Cond: (contact_id = p.contact_id)
14. 0.211 0.211 ↑ 1.0 1 211

Index Scan using contacts_info_pkey on contacts_info ci (cost=0.28..0.52 rows=1 width=43) (actual time=0.001..0.001 rows=1 loops=211)

  • Index Cond: (contact_info_id = con.contact_info_id)
15. 0.211 0.211 ↑ 1.0 1 211

Index Scan using addresses_pkey on addresses adrs (cost=0.27..0.44 rows=1 width=40) (actual time=0.001..0.001 rows=1 loops=211)

  • Index Cond: (con.address_id = address_id)
16. 0.211 1.477 ↓ 0.0 0 211

Subquery Scan on t (cost=0.00..13.30 rows=1 width=32) (actual time=0.007..0.007 rows=0 loops=211)

  • Filter: (t.row_num = 1)
17. 0.000 1.266 ↓ 0.0 0 211

WindowAgg (cost=0.00..13.29 rows=1 width=112) (actual time=0.006..0.006 rows=0 loops=211)

18. 0.172 1.266 ↓ 0.0 0 211

Nested Loop (cost=0.00..13.28 rows=1 width=104) (actual time=0.006..0.006 rows=0 loops=211)

  • Join Filter: (cr.contact_id = c.contact_id)
  • Rows Removed by Join Filter: 0
19. 0.422 0.422 ↓ 0.0 0 211

Seq Scan on contacts_relationships cr (cost=0.00..1.56 rows=1 width=72) (actual time=0.002..0.002 rows=0 loops=211)

  • Filter: ((patient_id = p.patient_id) AND (now() >= COALESCE(date_from, '1900-01-01 00:00:00'::timestamp without time zone)) AND (now() <= COALESCE(date_to, '2900-01-01 00:00:00'::timestamp without time zone)))
  • Rows Removed by Filter: 25
20. 0.672 0.672 ↑ 1.0 2 16

Seq Scan on contacts c (cost=0.00..11.69 rows=2 width=36) (actual time=0.018..0.042 rows=2 loops=16)

  • Filter: ((subtype)::text = 'MUTUALITY_BE'::text)
  • Rows Removed by Filter: 253
21. 0.211 0.844 ↑ 1.0 1 211

Aggregate (cost=1.35..1.36 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=211)

22. 0.633 0.633 ↓ 0.0 0 211

Seq Scan on patients_identity_documents pidt (cost=0.00..1.35 rows=1 width=548) (actual time=0.003..0.003 rows=0 loops=211)

  • Filter: (patient_id = p.patient_id)
  • Rows Removed by Filter: 38
23.          

SubPlan (for Nested Loop Left Join)

24. 1.477 1.477 ↑ 1.0 1 211

Result (cost=0.06..0.09 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=211)

25.          

Initplan (for Result)

26. 0.000 0.000 ↑ 1.0 1 211

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=211)

27. 0.000 0.000 ↑ 1.0 1 211

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=211)

28. 0.000 0.000 ↑ 1.0 1 211

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=211)

29. 0.000 0.000 ↑ 1.0 1 211

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=211)

30. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=32) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=32) (never executed)

32. 0.000 0.000 ↑ 1.0 1 211

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=211)

33. 0.000 0.000 ↑ 1.0 1 211

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=211)

Planning time : 1.437 ms
Execution time : 8.648 ms