explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kCt2A : laxmi

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 7.447 ↑ 1.0 1 1

Subquery Scan on institutionprofile (cost=13.22..468.82 rows=1 width=1,150) (actual time=7.446..7.447 rows=1 loops=1)

  • Buffers: shared hit=70 read=15
  • I/O Timings: read=6.587
2. 0.085 7.444 ↑ 1.0 1 1

GroupAggregate (cost=13.22..468.81 rows=1 width=1,158) (actual time=7.444..7.444 rows=1 loops=1)

  • Group Key: i.id, id.groupid, c.address1, c.address2, c.locality, c.region, c.country, c.postal, c.url
  • Buffers: shared hit=70 read=15
  • I/O Timings: read=6.587
3. 0.097 5.910 ↑ 1.0 1 1

Sort (cost=13.22..13.22 rows=1 width=411) (actual time=5.910..5.910 rows=1 loops=1)

  • Sort Key: id.groupid, c.address1, c.address2, c.locality, c.region, c.country, c.postal, c.url
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=12 read=11
  • I/O Timings: read=5.613
4. 0.004 5.813 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.13..13.21 rows=1 width=411) (actual time=5.811..5.813 rows=1 loops=1)

  • Join Filter: (i.id = c.institutionid)
  • Buffers: shared hit=3 read=11
  • I/O Timings: read=5.613
5. 0.007 3.386 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.71..8.76 rows=1 width=318) (actual time=3.385..3.386 rows=1 loops=1)

  • Join Filter: (i.id = id.institutionid)
  • Buffers: shared hit=3 read=7
  • I/O Timings: read=3.250
6. 1.985 1.985 ↑ 1.0 1 1

Index Scan using dill_institution_pkey on dill_institution i (cost=0.42..4.44 rows=1 width=305) (actual time=1.985..1.985 rows=1 loops=1)

  • Index Cond: (id = 68331)
  • Buffers: shared hit=3 read=4
  • I/O Timings: read=1.903
7. 1.394 1.394 ↑ 1.0 1 1

Index Scan using identifier_institutionid_ix on dill_identifier id (cost=0.29..4.31 rows=1 width=21) (actual time=1.394..1.394 rows=1 loops=1)

  • Index Cond: (institutionid = 68331)
  • Buffers: shared read=3
  • I/O Timings: read=1.347
8. 2.423 2.423 ↑ 1.0 1 1

Index Scan using contact_institutionid_type_ix on dill_contact c (cost=0.42..4.44 rows=1 width=101) (actual time=2.422..2.423 rows=1 loops=1)

  • Index Cond: ((institutionid = 68331) AND (type = 0))
  • Buffers: shared read=4
  • I/O Timings: read=2.363
9.          

SubPlan (for GroupAggregate)

10. 0.006 0.033 ↑ 1.0 1 1

Result (cost=4.46..4.47 rows=1 width=32) (actual time=0.033..0.033 rows=1 loops=1)

  • Buffers: shared hit=3
11.          

Initplan (for Result)

12. 0.001 0.027 ↓ 0.0 0 1

Unique (cost=4.45..4.46 rows=1 width=38) (actual time=0.027..0.027 rows=0 loops=1)

  • Buffers: shared hit=3
13. 0.007 0.026 ↓ 0.0 0 1

Sort (cost=4.45..4.45 rows=1 width=38) (actual time=0.026..0.026 rows=0 loops=1)

  • Sort Key: c_1.url
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3
14. 0.019 0.019 ↓ 0.0 0 1

Index Scan using contact_institutionid_type_ix on dill_contact c_1 (cost=0.42..4.44 rows=1 width=38) (actual time=0.019..0.019 rows=0 loops=1)

  • Index Cond: ((institutionid = i.id) AND (type = 4))
  • Filter: (url IS NOT NULL)
  • Buffers: shared hit=3
15. 0.001 0.016 ↑ 1.0 1 1

Result (cost=4.46..4.47 rows=1 width=32) (actual time=0.016..0.016 rows=1 loops=1)

  • Buffers: shared hit=3
16.          

Initplan (for Result)

17. 0.001 0.015 ↓ 0.0 0 1

Unique (cost=4.45..4.46 rows=1 width=38) (actual time=0.015..0.015 rows=0 loops=1)

  • Buffers: shared hit=3
18. 0.008 0.014 ↓ 0.0 0 1

Sort (cost=4.45..4.45 rows=1 width=38) (actual time=0.014..0.014 rows=0 loops=1)

  • Sort Key: c_1_1.url
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3
19. 0.006 0.006 ↓ 0.0 0 1

Index Scan using contact_institutionid_type_ix on dill_contact c_1_1 (cost=0.42..4.44 rows=1 width=38) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: ((institutionid = i.id) AND (type = 6))
  • Filter: (url IS NOT NULL)
  • Buffers: shared hit=3
20. 0.003 0.579 ↑ 1.0 1 1

Result (cost=57.21..57.22 rows=1 width=32) (actual time=0.579..0.579 rows=1 loops=1)

  • Buffers: shared read=2
  • I/O Timings: read=0.527
21.          

Initplan (for Result)

22. 0.000 0.576 ↓ 0.0 0 1

Unique (cost=54.49..57.21 rows=544 width=32) (actual time=0.576..0.576 rows=0 loops=1)

  • Buffers: shared read=2
  • I/O Timings: read=0.527
23. 0.012 0.576 ↓ 0.0 0 1

Sort (cost=54.49..55.85 rows=544 width=32) (actual time=0.576..0.576 rows=0 loops=1)

  • Sort Key: ((((((a.groupid)::text || '|'::text) || (id_1.symbol)::text) || '|'::text) || (g.name)::text))
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared read=2
  • I/O Timings: read=0.527
24. 0.000 0.564 ↓ 0.0 0 1

Nested Loop (cost=2.75..29.77 rows=544 width=32) (actual time=0.564..0.564 rows=0 loops=1)

  • Join Filter: (a.groupid = g.id)
  • Buffers: shared read=2
  • I/O Timings: read=0.527
25. 0.004 0.564 ↓ 0.0 0 1

Nested Loop (cost=2.60..21.07 rows=3 width=21) (actual time=0.564..0.564 rows=0 loops=1)

  • Buffers: shared read=2
  • I/O Timings: read=0.527
26. 0.560 0.560 ↓ 0.0 0 1

Bitmap Heap Scan on dill_affiliate a (cost=2.31..8.12 rows=3 width=8) (actual time=0.560..0.560 rows=0 loops=1)

  • Recheck Cond: (institutionid = i.id)
  • Buffers: shared read=2
  • I/O Timings: read=0.527
  • -> Bitmap Index Scan on affiliate_institutionid_ix (cost=0.00..2.31 rows=3 width=0) (actual time=0.557..0.557 rows=0loops=1)
  • Index Cond: (institutionid = i.id)
  • Buffers: shared read=2
  • I/O Timings: read=0.527
27. 0.000 0.000 ↓ 0.0 0

Index Scan using identifier_groupid_ix on dill_identifier id_1 (cost=0.29..4.31 rows=1 width=13) (never executed)

  • Index Cond: (groupid = a.groupid)
28. 0.000 0.000 ↓ 0.0 0

Index Scan using group__pkey on dill_group_ g (cost=0.15..0.17 rows=1 width=33) (never executed)

  • Index Cond: (id = id_1.groupid)
29. 0.004 0.475 ↑ 1.0 1 1

Aggregate (cost=8.15..8.16 rows=1 width=8) (actual time=0.475..0.475 rows=1 loops=1)

  • Buffers: shared read=2
  • I/O Timings: read=0.448
30. 0.003 0.471 ↓ 0.0 0 1

Bitmap Heap Scan on dill_policy p (cost=2.31..8.14 rows=1 width=8) (actual time=0.471..0.471 rows=0 loops=1)

  • Recheck Cond: (institutionid = i.id)
  • Filter: ((servicetype = ANY ('{1,3}'::bigint[])) AND (type = 1))
  • Buffers: shared read=2
  • I/O Timings: read=0.448
31. 0.468 0.468 ↓ 0.0 0 1

Bitmap Index Scan on policy_institutionid_ix (cost=0.00..2.31 rows=3 width=0) (actual time=0.468..0.468 rows=0 loops=1)

  • Index Cond: (institutionid = i.id)
  • Buffers: shared read=2
  • I/O Timings: read=0.448
32. 0.002 0.006 ↑ 1.0 1 1

Aggregate (cost=8.15..8.16 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=1)

  • Buffers: shared hit=2
33. 0.001 0.004 ↓ 0.0 0 1

Bitmap Heap Scan on dill_policy p_1 (cost=2.31..8.14 rows=2 width=8) (actual time=0.004..0.004 rows=0 loops=1)

  • Recheck Cond: (institutionid = i.id)
  • Filter: ((servicetype = ANY ('{2,3}'::bigint[])) AND (type = 1))
  • Buffers: shared hit=2
34. 0.003 0.003 ↓ 0.0 0 1

Bitmap Index Scan on policy_institutionid_ix (cost=0.00..2.31 rows=3 width=0) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (institutionid = i.id)
  • Buffers: shared hit=2
35. 0.001 0.006 ↑ 1.0 1 1

Aggregate (cost=16.75..16.76 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=1)

  • Buffers: shared hit=2
36. 0.001 0.005 ↓ 0.0 0 1

Nested Loop (cost=2.60..16.75 rows=2 width=8) (actual time=0.005..0.005 rows=0 loops=1)

  • Buffers: shared hit=2
37. 0.003 0.004 ↓ 0.0 0 1

Bitmap Heap Scan on dill_policy p_2 (cost=2.31..8.14 rows=2 width=8) (actual time=0.004..0.004 rows=0 loops=1)

  • Recheck Cond: (institutionid = i.id)
  • Filter: (type = 1)
  • Buffers: shared hit=2
38. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on policy_institutionid_ix (cost=0.00..2.31 rows=3 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (institutionid = i.id)
  • Buffers: shared hit=2
39. 0.000 0.000 ↓ 0.0 0

Index Scan using loan_pkey on dill_loan l (cost=0.29..4.30 rows=1 width=16) (never executed)

  • Index Cond: (policyid = p_2.id)
40. 0.001 0.003 ↑ 1.0 1 1

Aggregate (cost=16.75..16.76 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)

  • Buffers: shared hit=2
41. 0.001 0.002 ↓ 0.0 0 1

Nested Loop (cost=2.60..16.75 rows=2 width=8) (actual time=0.002..0.002 rows=0 loops=1)

  • Buffers: shared hit=2
42. 0.000 0.001 ↓ 0.0 0 1

Bitmap Heap Scan on dill_policy p_3 (cost=2.31..8.14 rows=2 width=8) (actual time=0.001..0.001 rows=0 loops=1)

  • Recheck Cond: (institutionid = i.id)
  • Filter: (type = 1)
  • Buffers: shared hit=2
43. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on policy_institutionid_ix (cost=0.00..2.31 rows=3 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (institutionid = i.id)
  • Buffers: shared hit=2
44. 0.000 0.000 ↓ 0.0 0

Index Scan using loan_pkey on dill_loan l_1 (cost=0.29..4.30 rows=1 width=16) (never executed)

  • Index Cond: (policyid = p_3.id)
45. 0.001 0.005 ↑ 1.0 1 1

Aggregate (cost=16.76..16.77 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1)

  • Buffers: shared hit=2
46. 0.001 0.004 ↓ 0.0 0 1

Nested Loop (cost=2.60..16.75 rows=2 width=8) (actual time=0.004..0.004 rows=0 loops=1)

  • Buffers: shared hit=2
47. 0.000 0.003 ↓ 0.0 0 1

Bitmap Heap Scan on dill_policy p_4 (cost=2.31..8.14 rows=2 width=8) (actual time=0.003..0.003 rows=0 loops=1)

  • Recheck Cond: (institutionid = i.id)
  • Filter: ((servicetype = ANY ('{2,3}'::bigint[])) AND (type = 1))
  • Buffers: shared hit=2
48. 0.003 0.003 ↓ 0.0 0 1

Bitmap Index Scan on policy_institutionid_ix (cost=0.00..2.31 rows=3 width=0) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (institutionid = i.id)
  • Buffers: shared hit=2
49. 0.000 0.000 ↓ 0.0 0

Index Scan using loan_pkey on dill_loan l_2 (cost=0.29..4.30 rows=1 width=16) (never executed)

  • Index Cond: (policyid = p_4.id)
50. 0.001 0.003 ↑ 1.0 1 1

Aggregate (cost=16.75..16.76 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)

  • Buffers: shared hit=2
51. 0.001 0.002 ↓ 0.0 0 1

Nested Loop (cost=2.60..16.75 rows=2 width=8) (actual time=0.002..0.002 rows=0 loops=1)

  • Buffers: shared hit=2
52. 0.000 0.001 ↓ 0.0 0 1

Bitmap Heap Scan on dill_policy p_5 (cost=2.31..8.14 rows=2 width=8) (actual time=0.001..0.001 rows=0 loops=1)

  • Recheck Cond: (institutionid = i.id)
  • Filter: (type = 1)
  • Buffers: shared hit=2
53. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on policy_institutionid_ix (cost=0.00..2.31 rows=3 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (institutionid = i.id)
  • Buffers: shared hit=2
54. 0.000 0.000 ↓ 0.0 0

Index Scan using loan_pkey on dill_loan l_3 (cost=0.29..4.30 rows=1 width=16) (never executed)

  • Index Cond: (policyid = p_5.id)
55. 0.002 0.029 ↑ 1.0 1 1

Result (cost=13.61..13.62 rows=1 width=32) (actual time=0.029..0.029 rows=1 loops=1)

  • Buffers: shared hit=2
56.          

Initplan (for Result)

57. 0.011 0.027 ↓ 0.0 0 1

Sort (cost=13.61..13.61 rows=1 width=64) (actual time=0.027..0.027 rows=0 loops=1)

  • Sort Key: (((CASE WHEN (min(f.fee) = max(f.fee)) THEN (to_currency((min(f.fee))::double precision))::text ELSE (((to_currency((min(f.fee))::double precision))::text || ' - '::text) || (to_currency((max(f.fee))::double precision))::text) END || ' '::text) || ((COALESCE(c_1_2.currency, ''::bpchar)))::text))
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2
58. 0.000 0.016 ↓ 0.0 0 1

GroupAggregate (cost=12.79..13.60 rows=1 width=64) (actual time=0.016..0.016 rows=0 loops=1)

  • Group Key: (COALESCE(c_1_2.currency, ''::bpchar))
  • Buffers: shared hit=2
59. 0.012 0.016 ↓ 0.0 0 1

Sort (cost=12.79..12.80 rows=1 width=39) (actual time=0.016..0.016 rows=0 loops=1)

  • Sort Key: (COALESCE(c_1_2.currency, ''::bpchar))
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2
60. 0.000 0.004 ↓ 0.0 0 1

Nested Loop (cost=2.89..12.78 rows=1 width=39) (actual time=0.004..0.004 rows=0 loops=1)

  • Join Filter: (p_6.id = f.policyid)
  • Buffers: shared hit=2
61. 0.002 0.004 ↓ 0.0 0 1

Nested Loop (cost=2.60..12.45 rows=1 width=20) (actual time=0.004..0.004 rows=0 loops=1)

  • Buffers: shared hit=2
62. 0.000 0.002 ↓ 0.0 0 1

Bitmap Heap Scan on dill_policy p_6 (cost=2.31..8.14 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=1)

  • Recheck Cond: (institutionid = i.id)
  • Filter: ((servicetype = ANY ('{1,3}'::bigint[])) AND (type = 1))
  • Buffers: shared hit=2
63. 0.002 0.002 ↓ 0.0 0 1

Bitmap Index Scan on policy_institutionid_ix (cost=0.00..2.31 rows=3 width=0) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: (institutionid = i.id)
  • Buffers: shared hit=2
64. 0.000 0.000 ↓ 0.0 0

Index Scan using charge_pkey on dill_charge c_1_2 (cost=0.29..4.30 rows=1 width=12) (never executed)

  • Index Cond: (policyid = p_6.id)
65. 0.000 0.000 ↓ 0.0 0

Index Scan using fee_policyid_ix on dill_fee f (cost=0.29..0.32 rows=1 width=11) (never executed)

  • Index Cond: (policyid = c_1_2.policyid)
66. 0.001 0.040 ↑ 1.0 1 1

Result (cost=19.05..19.06 rows=1 width=32) (actual time=0.040..0.040 rows=1 loops=1)

  • Buffers: shared hit=2
67.          

Initplan (for Result)

68. 0.011 0.039 ↓ 0.0 0 1

Sort (cost=19.05..19.05 rows=2 width=64) (actual time=0.038..0.039 rows=0 loops=1)

  • Sort Key: (((CASE WHEN (min(f_1.fee) = max(f_1.fee)) THEN (to_currency((min(f_1.fee))::double precision))::text ELSE (((to_currency((min(f_1.fee))::double precision))::text || ' - '::text) || (to_currency((max(f_1.fee))::double precision))::text) END || ' '::text) || ((COALESCE(c_1_3.currency, ''::bpchar)))::text))
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2
69. 0.001 0.028 ↓ 0.0 0 1

GroupAggregate (cost=17.44..19.04 rows=2 width=64) (actual time=0.028..0.028 rows=0 loops=1)

  • Group Key: (COALESCE(c_1_3.currency, ''::bpchar))
  • Buffers: shared hit=2
70. 0.014 0.027 ↓ 0.0 0 1

Sort (cost=17.44..17.44 rows=2 width=39) (actual time=0.027..0.027 rows=0 loops=1)

  • Sort Key: (COALESCE(c_1_3.currency, ''::bpchar))
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2
71. 0.000 0.013 ↓ 0.0 0 1

Nested Loop (cost=2.89..17.43 rows=2 width=39) (actual time=0.013..0.013 rows=0 loops=1)

  • Join Filter: (p_7.id = f_1.policyid)
  • Buffers: shared hit=2
72. 0.002 0.013 ↓ 0.0 0 1

Nested Loop (cost=2.60..16.75 rows=2 width=20) (actual time=0.013..0.013 rows=0 loops=1)

  • Buffers: shared hit=2
73. 0.010 0.011 ↓ 0.0 0 1

Bitmap Heap Scan on dill_policy p_7 (cost=2.31..8.14 rows=2 width=8) (actual time=0.011..0.011 rows=0 loops=1)

  • Recheck Cond: (institutionid = i.id)
  • Filter: ((servicetype = ANY ('{2,3}'::bigint[])) AND (type = 1))
  • Buffers: shared hit=2
74. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on policy_institutionid_ix (cost=0.00..2.31 rows=3 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (institutionid = i.id)
  • Buffers: shared hit=2
75. 0.000 0.000 ↓ 0.0 0

Index Scan using charge_pkey on dill_charge c_1_3 (cost=0.29..4.30 rows=1 width=12) (never executed)

  • Index Cond: (policyid = p_7.id)
76. 0.000 0.000 ↓ 0.0 0

Index Scan using fee_policyid_ix on dill_fee f_1 (cost=0.29..0.32 rows=1 width=11) (never executed)

  • Index Cond: (policyid = c_1_3.policyid)
77. 0.001 0.017 ↑ 1.0 1 1

Result (cost=16.24..16.25 rows=1 width=32) (actual time=0.017..0.017 rows=1 loops=1)

  • Buffers: shared hit=2
78.          

Initplan (for Result)

79. 0.000 0.016 ↓ 0.0 0 1

Unique (cost=16.23..16.24 rows=2 width=8) (actual time=0.016..0.016 rows=0 loops=1)

  • Buffers: shared hit=2
80. 0.012 0.016 ↓ 0.0 0 1

Sort (cost=16.23..16.24 rows=2 width=8) (actual time=0.016..0.016 rows=0 loops=1)

  • Sort Key: r.type
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2
81. 0.002 0.004 ↓ 0.0 0 1

Nested Loop (cost=4.62..16.22 rows=2 width=8) (actual time=0.004..0.004 rows=0 loops=1)

  • Buffers: shared hit=2
82. 0.001 0.002 ↓ 0.0 0 1

Bitmap Heap Scan on dill_policy p_8 (cost=2.31..8.14 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=1)

  • Recheck Cond: (institutionid = i.id)
  • Filter: ((servicetype = ANY ('{1,3}'::bigint[])) AND (type = 1))
  • Buffers: shared hit=2
83. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on policy_institutionid_ix (cost=0.00..2.31 rows=3 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (institutionid = i.id)
  • Buffers: shared hit=2
84. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on dill_request r (cost=2.31..8.05 rows=3 width=16) (never executed)

  • Recheck Cond: (policyid = p_8.id)
85. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on request_policyid_ix (cost=0.00..2.31 rows=3 width=0) (never executed)

  • Index Cond: (policyid = p_8.id)
86. 0.001 0.013 ↑ 1.0 1 1

Result (cost=24.34..24.35 rows=1 width=32) (actual time=0.013..0.013 rows=1 loops=1)

  • Buffers: shared hit=2
87.          

Initplan (for Result)

88. 0.000 0.012 ↓ 0.0 0 1

Unique (cost=24.32..24.34 rows=3 width=8) (actual time=0.012..0.012 rows=0 loops=1)

  • Buffers: shared hit=2
89. 0.007 0.012 ↓ 0.0 0 1

Sort (cost=24.32..24.33 rows=3 width=8) (actual time=0.012..0.012 rows=0 loops=1)

  • Sort Key: r_1.type
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2
90. 0.001 0.005 ↓ 0.0 0 1

Nested Loop (cost=4.62..24.30 rows=3 width=8) (actual time=0.005..0.005 rows=0 loops=1)

  • Buffers: shared hit=2
91. 0.001 0.004 ↓ 0.0 0 1

Bitmap Heap Scan on dill_policy p_9 (cost=2.31..8.14 rows=2 width=8) (actual time=0.004..0.004 rows=0 loops=1)

  • Recheck Cond: (institutionid = i.id)
  • Filter: ((servicetype = ANY ('{2,3}'::bigint[])) AND (type = 1))
  • Buffers: shared hit=2
92. 0.003 0.003 ↓ 0.0 0 1

Bitmap Index Scan on policy_institutionid_ix (cost=0.00..2.31 rows=3 width=0) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (institutionid = i.id)
  • Buffers: shared hit=2
93. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on dill_request r_1 (cost=2.31..8.05 rows=3 width=16) (never executed)

  • Recheck Cond: (policyid = p_9.id)
94. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on request_policyid_ix (cost=0.00..2.31 rows=3 width=0) (never executed)

  • Index Cond: (policyid = p_9.id)
95. 0.001 0.014 ↑ 1.0 1 1

Result (cost=14.32..14.33 rows=1 width=32) (actual time=0.014..0.014 rows=1 loops=1)

  • Buffers: shared hit=2
96.          

Initplan (for Result)

97. 0.000 0.013 ↓ 0.0 0 1

Unique (cost=14.31..14.32 rows=1 width=8) (actual time=0.013..0.013 rows=0 loops=1)

  • Buffers: shared hit=2
98. 0.010 0.013 ↓ 0.0 0 1

Sort (cost=14.31..14.32 rows=1 width=8) (actual time=0.013..0.013 rows=0 loops=1)

  • Sort Key: d.type
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2
99. 0.001 0.003 ↓ 0.0 0 1

Nested Loop (cost=4.61..14.30 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=1)

  • Buffers: shared hit=2
100. 0.001 0.002 ↓ 0.0 0 1

Bitmap Heap Scan on dill_policy p_10 (cost=2.31..8.14 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=1)

  • Recheck Cond: (institutionid = i.id)
  • Filter: ((servicetype = ANY ('{1,3}'::bigint[])) AND (type = 1))
  • Buffers: shared hit=2
101. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on policy_institutionid_ix (cost=0.00..2.31 rows=3 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (institutionid = i.id)
  • Buffers: shared hit=2
102. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on dill_delivery d (cost=2.30..6.14 rows=2 width=16) (never executed)

  • Recheck Cond: (policyid = p_10.id)
103. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on delivery_policyid_ix (cost=0.00..2.30 rows=2 width=0) (never executed)

  • Index Cond: (policyid = p_10.id)
104. 0.001 0.011 ↑ 1.0 1 1

Result (cost=20.49..20.50 rows=1 width=32) (actual time=0.011..0.011 rows=1 loops=1)

  • Buffers: shared hit=2
105.          

Initplan (for Result)

106. 0.000 0.010 ↓ 0.0 0 1

Unique (cost=20.48..20.49 rows=2 width=8) (actual time=0.010..0.010 rows=0 loops=1)

  • Buffers: shared hit=2
107. 0.007 0.010 ↓ 0.0 0 1

Sort (cost=20.48..20.48 rows=2 width=8) (actual time=0.010..0.010 rows=0 loops=1)

  • Sort Key: d_1.type
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2
108. 0.001 0.003 ↓ 0.0 0 1

Nested Loop (cost=4.61..20.47 rows=2 width=8) (actual time=0.003..0.003 rows=0 loops=1)

  • Buffers: shared hit=2
109. 0.001 0.002 ↓ 0.0 0 1

Bitmap Heap Scan on dill_policy p_11 (cost=2.31..8.14 rows=2 width=8) (actual time=0.002..0.002 rows=0 loops=1)

  • Recheck Cond: (institutionid = i.id)
  • Filter: ((servicetype = ANY ('{2,3}'::bigint[])) AND (type = 1))
  • Buffers: shared hit=2
110. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on policy_institutionid_ix (cost=0.00..2.31 rows=3 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (institutionid = i.id)
  • Buffers: shared hit=2
111. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on dill_delivery d_1 (cost=2.30..6.14 rows=2 width=16) (never executed)

  • Recheck Cond: (policyid = p_11.id)
112. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on delivery_policyid_ix (cost=0.00..2.30 rows=2 width=0) (never executed)

  • Index Cond: (policyid = p_11.id)
113. 0.004 0.010 ↑ 1.0 1 1

Aggregate (cost=21.73..21.75 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=1)

  • Buffers: shared hit=2
114. 0.001 0.006 ↓ 0.0 0 1

Nested Loop Left Join (cost=4.65..21.73 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=1)

  • Filter: (l_4.id IS NULL)
  • Buffers: shared hit=2
115. 0.004 0.005 ↓ 0.0 0 1

Bitmap Heap Scan on dill_policy p_12 (cost=2.31..8.14 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1)

  • Recheck Cond: (institutionid = i.id)
  • Filter: ((servicetype = ANY ('{1,3}'::bigint[])) AND (type = 1))
  • Buffers: shared hit=2
116. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on policy_institutionid_ix (cost=0.00..2.31 rows=3 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (institutionid = i.id)
  • Buffers: shared hit=2
117. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on dill_location l_4 (cost=2.34..13.52 rows=6 width=16) (never executed)

  • Recheck Cond: (p_12.id = policyid)
118. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on location_policyid_ix (cost=0.00..2.33 rows=6 width=0) (never executed)

  • Index Cond: (policyid = p_12.id)
119. 0.001 0.012 ↑ 1.0 1 1

Result (cost=21.72..21.73 rows=1 width=32) (actual time=0.012..0.012 rows=1 loops=1)

  • Buffers: shared hit=2
120.          

Initplan (for Result)

121. 0.000 0.011 ↓ 0.0 0 1

Unique (cost=21.71..21.72 rows=1 width=3) (actual time=0.011..0.011 rows=0 loops=1)

  • Buffers: shared hit=2
122. 0.008 0.011 ↓ 0.0 0 1

Sort (cost=21.71..21.72 rows=1 width=3) (actual time=0.011..0.011 rows=0 loops=1)

  • Sort Key: l_5.country
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2
123. 0.001 0.003 ↓ 0.0 0 1

Nested Loop (cost=4.65..21.70 rows=1 width=3) (actual time=0.003..0.003 rows=0 loops=1)

  • Buffers: shared hit=2
124. 0.001 0.002 ↓ 0.0 0 1

Bitmap Heap Scan on dill_policy p_13 (cost=2.31..8.14 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=1)

  • Recheck Cond: (institutionid = i.id)
  • Filter: ((servicetype = ANY ('{1,3}'::bigint[])) AND (type = 1))
  • Buffers: shared hit=2
125. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on policy_institutionid_ix (cost=0.00..2.31 rows=3 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (institutionid = i.id)
  • Buffers: shared hit=2
126. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on dill_location l_5 (cost=2.34..13.54 rows=2 width=11) (never executed)

  • Recheck Cond: (policyid = p_13.id)
  • Filter: ((NOT COALESCE(exclude, false)) AND (country <> COALESCE(c.country, ' '::bpchar)))
127. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on location_policyid_ix (cost=0.00..2.33 rows=6 width=0) (never executed)

  • Index Cond: (policyid = p_13.id)
128. 0.000 0.012 ↑ 1.0 1 1

Result (cost=21.72..21.73 rows=1 width=32) (actual time=0.012..0.012 rows=1 loops=1)

  • Buffers: shared hit=2
129.          

Initplan (for Result)

130. 0.001 0.012 ↓ 0.0 0 1

Unique (cost=21.71..21.72 rows=1 width=3) (actual time=0.011..0.012 rows=0 loops=1)

  • Buffers: shared hit=2
131. 0.006 0.011 ↓ 0.0 0 1

Sort (cost=21.71..21.72 rows=1 width=3) (actual time=0.011..0.011 rows=0 loops=1)

  • Sort Key: l_6.country
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2
132. 0.001 0.005 ↓ 0.0 0 1

Nested Loop (cost=4.65..21.70 rows=1 width=3) (actual time=0.005..0.005 rows=0 loops=1)

  • Buffers: shared hit=2
133. 0.001 0.004 ↓ 0.0 0 1

Bitmap Heap Scan on dill_policy p_14 (cost=2.31..8.14 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=1)

  • Recheck Cond: (institutionid = i.id)
  • Filter: ((servicetype = ANY ('{1,3}'::bigint[])) AND (type = 1))
  • Buffers: shared hit=2
134. 0.003 0.003 ↓ 0.0 0 1

Bitmap Index Scan on policy_institutionid_ix (cost=0.00..2.31 rows=3 width=0) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (institutionid = i.id)
  • Buffers: shared hit=2
135. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on dill_location l_6 (cost=2.34..13.54 rows=2 width=11) (never executed)

  • Recheck Cond: (policyid = p_14.id)
  • Filter: (COALESCE(exclude, false) AND (country <> COALESCE(c.country, ' '::bpchar)))
136. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on location_policyid_ix (cost=0.00..2.33 rows=6 width=0) (never executed)

  • Index Cond: (policyid = p_14.id)
137. 0.001 0.003 ↑ 1.0 1 1

Aggregate (cost=35.31..35.33 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=1)

  • Buffers: shared hit=2
138. 0.001 0.002 ↓ 0.0 0 1

Nested Loop Left Join (cost=4.65..35.31 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (l_7.id IS NULL)
  • Buffers: shared hit=2
139. 0.000 0.001 ↓ 0.0 0 1

Bitmap Heap Scan on dill_policy p_15 (cost=2.31..8.14 rows=2 width=8) (actual time=0.001..0.001 rows=0 loops=1)

  • Recheck Cond: (institutionid = i.id)
  • Filter: ((servicetype = ANY ('{2,3}'::bigint[])) AND (type = 1))
  • Buffers: shared hit=2
140. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on policy_institutionid_ix (cost=0.00..2.31 rows=3 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (institutionid = i.id)
  • Buffers: shared hit=2
141. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on dill_location l_7 (cost=2.34..13.52 rows=6 width=16) (never executed)

  • Recheck Cond: (p_15.id = policyid)
142. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on location_policyid_ix (cost=0.00..2.33 rows=6 width=0) (never executed)

  • Index Cond: (policyid = p_15.id)
143. 0.001 0.014 ↑ 1.0 1 1

Result (cost=35.28..35.29 rows=1 width=32) (actual time=0.014..0.014 rows=1 loops=1)

  • Buffers: shared hit=2
144.          

Initplan (for Result)

145. 0.000 0.013 ↓ 0.0 0 1

Unique (cost=35.27..35.28 rows=1 width=3) (actual time=0.013..0.013 rows=0 loops=1)

  • Buffers: shared hit=2
146. 0.009 0.013 ↓ 0.0 0 1

Sort (cost=35.27..35.27 rows=1 width=3) (actual time=0.013..0.013 rows=0 loops=1)

  • Sort Key: l_8.country
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2
147. 0.001 0.004 ↓ 0.0 0 1

Nested Loop (cost=4.65..35.26 rows=1 width=3) (actual time=0.004..0.004 rows=0 loops=1)

  • Buffers: shared hit=2
148. 0.001 0.003 ↓ 0.0 0 1

Bitmap Heap Scan on dill_policy p_16 (cost=2.31..8.14 rows=2 width=8) (actual time=0.003..0.003 rows=0 loops=1)

  • Recheck Cond: (institutionid = i.id)
  • Filter: ((servicetype = ANY ('{2,3}'::bigint[])) AND (type = 1))
  • Buffers: shared hit=2
149. 0.002 0.002 ↓ 0.0 0 1

Bitmap Index Scan on policy_institutionid_ix (cost=0.00..2.31 rows=3 width=0) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: (institutionid = i.id)
  • Buffers: shared hit=2
150. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on dill_location l_8 (cost=2.34..13.54 rows=2 width=11) (never executed)

  • Recheck Cond: (policyid = p_16.id)
  • Filter: ((NOT COALESCE(exclude, false)) AND (country <> COALESCE(c.country, ' '::bpchar)))
151. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on location_policyid_ix (cost=0.00..2.33 rows=6 width=0) (never executed)

  • Index Cond: (policyid = p_16.id)
152. 0.002 0.029 ↑ 1.0 1 1

Result (cost=35.28..35.29 rows=1 width=32) (actual time=0.029..0.029 rows=1 loops=1)

  • Buffers: shared hit=2
153.          

Initplan (for Result)

154. 0.001 0.027 ↓ 0.0 0 1

Unique (cost=35.27..35.28 rows=1 width=11) (actual time=0.027..0.027 rows=0 loops=1)

  • Buffers: shared hit=2
155. 0.000 0.026 ↓ 0.0 0 1

Group (cost=35.27..35.28 rows=1 width=11) (actual time=0.026..0.026 rows=0 loops=1)

  • Group Key: l_9.country, p_17.id
  • Buffers: shared hit=2
156. 0.017 0.026 ↓ 0.0 0 1

Sort (cost=35.27..35.27 rows=1 width=11) (actual time=0.026..0.026 rows=0 loops=1)

  • Sort Key: l_9.country, p_17.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2
157. 0.003 0.009 ↓ 0.0 0 1

Nested Loop (cost=4.65..35.26 rows=1 width=11) (actual time=0.009..0.009 rows=0 loops=1)

  • Buffers: shared hit=2
158. 0.004 0.006 ↓ 0.0 0 1

Bitmap Heap Scan on dill_policy p_17 (cost=2.31..8.14 rows=2 width=8) (actual time=0.006..0.006 rows=0 loops=1)

  • Recheck Cond: (institutionid = i.id)
  • Filter: ((servicetype = ANY ('{2,3}'::bigint[])) AND (type = 1))
  • Buffers: shared hit=2
159. 0.002 0.002 ↓ 0.0 0 1

Bitmap Index Scan on policy_institutionid_ix (cost=0.00..2.31 rows=3 width=0) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: (institutionid = i.id)
  • Buffers: shared hit=2
160. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on dill_location l_9 (cost=2.34..13.54 rows=2 width=11) (never executed)

  • Recheck Cond: (policyid = p_17.id)
  • Filter: (COALESCE(exclude, false) AND (country <> COALESCE(c.country, ' '::bpchar)))
161. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on location_policyid_ix (cost=0.00..2.33 rows=6 width=0) (never executed)

  • Index Cond: (policyid = p_17.id)
162. 0.002 0.029 ↑ 1.0 1 1

Result (cost=4.49..4.50 rows=1 width=32) (actual time=0.029..0.029 rows=1 loops=1)

  • Buffers: shared hit=3
163.          

Initplan (for Result)

164. 0.001 0.027 ↓ 0.0 0 1

Unique (cost=4.48..4.49 rows=1 width=32) (actual time=0.027..0.027 rows=0 loops=1)

  • Buffers: shared hit=3
165. 0.009 0.026 ↓ 0.0 0 1

Sort (cost=4.48..4.49 rows=1 width=32) (actual time=0.026..0.026 rows=0 loops=1)

  • Sort Key: ((((((((((((((c_1_4.attention)::text || '|'::text) || (c_1_4.address1)::text) || '|'::text) || (c_1_4.address2)::text) || '|'::text) || (c_1_4.locality)::text) || ', '::text) || CASE WHEN (c_1_4.region IS NOT NULL) THEN "substring"((c_1_4.region)::text, 4, 2) ELSE ' '::text END) || ' '::text) || (c_1_4.postal)::text) || ' '::text) || (c_1_4.country)::text))
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3
166. 0.017 0.017 ↓ 0.0 0 1

Index Scan using contact_institutionid_type_ix on dill_contact c_1_4 (cost=0.42..4.47 rows=1 width=32) (actual time=0.017..0.017 rows=0 loops=1)

  • Index Cond: ((institutionid = i.id) AND (type = 9))
  • Buffers: shared hit=3
167. 0.003 0.011 ↑ 1.0 1 1

Result (cost=4.44..4.45 rows=1 width=32) (actual time=0.011..0.011 rows=1 loops=1)

  • Buffers: shared hit=3
168.          

Initplan (for Result)

169. 0.008 0.008 ↓ 0.0 0 1

Index Scan using contact_institutionid_type_ix on dill_contact c_1_5 (cost=0.42..4.44 rows=1 width=38) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: ((institutionid = i.id) AND (type = 1))
  • Buffers: shared hit=3
170. 0.001 0.012 ↑ 1.0 1 1

Result (cost=4.44..4.45 rows=1 width=32) (actual time=0.012..0.012 rows=1 loops=1)

  • Buffers: shared hit=3
171.          

Initplan (for Result)

172. 0.011 0.011 ↓ 0.0 0 1

Index Scan using contact_institutionid_type_ix on dill_contact c_1_6 (cost=0.42..4.44 rows=1 width=18) (actual time=0.011..0.011 rows=0 loops=1)

  • Index Cond: ((institutionid = i.id) AND (type = 1))
  • Buffers: shared hit=3
173. 0.002 0.015 ↑ 1.0 1 1

Result (cost=4.44..4.45 rows=1 width=32) (actual time=0.015..0.015 rows=1 loops=1)

  • Buffers: shared hit=3
174.          

Initplan (for Result)

175. 0.013 0.013 ↓ 0.0 0 1

Index Scan using contact_institutionid_type_ix on dill_contact c_1_7 (cost=0.42..4.44 rows=1 width=38) (actual time=0.013..0.013 rows=0 loops=1)

  • Index Cond: ((institutionid = i.id) AND (type = 7))
  • Buffers: shared hit=3
176. 0.003 0.042 ↑ 1.0 1 1

Result (cost=4.44..4.45 rows=1 width=32) (actual time=0.042..0.042 rows=1 loops=1)

  • Buffers: shared hit=3
177.          

Initplan (for Result)

178. 0.039 0.039 ↓ 0.0 0 1

Index Scan using contact_institutionid_type_ix on dill_contact c_1_8 (cost=0.42..4.44 rows=1 width=38) (actual time=0.039..0.039 rows=0 loops=1)

  • Index Cond: ((institutionid = i.id) AND (type = 11))
  • Buffers: shared hit=3
179. 0.002 0.010 ↑ 1.0 1 1

Result (cost=4.44..4.45 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=1)

  • Buffers: shared hit=3
180.          

Initplan (for Result)

181. 0.008 0.008 ↓ 0.0 0 1

Index Only Scan using contact_institutionid_type_ix on dill_contact c_1_9 (cost=0.42..4.44 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: ((institutionid = i.id) AND (type = 11))
  • Heap Fetches: 0
  • Buffers: shared hit=3
Planning time : 1,804.204 ms
Execution time : 10.578 ms