explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dWf8 : Optimization for: Optimization for: PROD; plan #LC1K

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.259 686.877 ↓ 3.8 347 1

Unique (cost=104,646.99..104,652.68 rows=91 width=308) (actual time=686.596..686.877 rows=347 loops=1)

2.          

CTE niveau_rem

3. 0.003 0.003 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=80) (actual time=0.003..0.003 rows=1 loops=1)

4.          

CTE fees_amount

5. 0.620 326.383 ↓ 76.5 306 1

GroupAggregate (cost=13,773.55..13,773.63 rows=4 width=36) (actual time=325.597..326.383 rows=306 loops=1)

  • Group Key: "*SELECT* 1".agent
6. 0.688 325.763 ↓ 587.0 2,348 1

Sort (cost=13,773.55..13,773.56 rows=4 width=36) (actual time=325.587..325.763 rows=2,348 loops=1)

  • Sort Key: "*SELECT* 1".agent
  • Sort Method: quicksort Memory: 207kB
7. 0.209 325.075 ↓ 587.0 2,348 1

Append (cost=633.56..13,773.51 rows=4 width=36) (actual time=26.286..325.075 rows=2,348 loops=1)

8. 0.049 26.445 ↓ 284.0 284 1

Subquery Scan on *SELECT* 1 (cost=633.56..633.59 rows=1 width=36) (actual time=26.284..26.445 rows=284 loops=1)

9. 0.095 26.396 ↓ 284.0 284 1

Unique (cost=633.56..633.58 rows=1 width=52) (actual time=26.282..26.396 rows=284 loops=1)

10. 0.347 26.301 ↓ 294.0 294 1

Sort (cost=633.56..633.57 rows=1 width=52) (actual time=26.281..26.301 rows=294 loops=1)

  • Sort Key: ref_transaction.id_transaction, ref_transaction.id_int_agent_in, ref_transaction.id_ext_agent_in, ref_margin.id_ss_transaction, (COALESCE((ref_sub_transaction.date_paid)::date, (ref_transaction.date_paid)::date)), (round((((COALESCE((ref_margin.amount_base_rec / 2.0), '0'::numeric))::double precision * ('1'::double precision + (COALESCE(ref_transaction.total_vat, '0'::double precision) / '100'::double precision))))::numeric(10,4), 2))
  • Sort Method: quicksort Memory: 47kB
11. 6.277 25.954 ↓ 294.0 294 1

Nested Loop (cost=421.50..633.55 rows=1 width=52) (actual time=3.962..25.954 rows=294 loops=1)

  • Join Filter: (COALESCE((ref_sub_transaction.date_paid)::date, (ref_transaction.date_paid)::date) >= ((((((CASE WHEN (date_part('month'::text, (CURRENT_DATE)::timestamp without time zone) < (ref_subsidiary_1.month_fiscal_start)::double precision) THEN (date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - '1'::double precision) ELSE date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) END)::text || '-'::text) || (ref_subsidiary_1.month_fiscal_start)::text) || '-'::text) || (ref_subsidiary_1.day_fiscal_start)::text))::date)
  • Rows Removed by Join Filter: 154
12. 0.659 17.885 ↓ 448.0 448 1

Nested Loop (cost=421.50..632.39 rows=1 width=50) (actual time=3.909..17.885 rows=448 loops=1)

  • Join Filter: ((ref_margin.id_transaction = ref_transaction.id_transaction) AND (ref_margin.id_int_agent = ref_transaction.id_int_agent_in))
13. 0.668 6.922 ↓ 448.0 448 1

Hash Join (cost=421.06..624.01 rows=1 width=34) (actual time=2.951..6.922 rows=448 loops=1)

  • Hash Cond: ((ref_margin.id_transaction = ref_sub_transaction.id_transaction) AND (ref_margin.id_ss_transaction = ref_sub_transaction.id_ss_transaction))
14. 3.482 5.864 ↓ 84.2 421 1

Bitmap Heap Scan on ref_margin (cost=391.02..593.90 rows=5 width=22) (actual time=2.547..5.864 rows=421 loops=1)

  • Recheck Cond: ((id_ss_transaction IS NOT NULL) AND (id_filiale = 3))
  • Filter: (((ss_type_margin)::text = 'Agent'::text) AND ((type_margin)::text = 'IN'::text))
  • Rows Removed by Filter: 2504
  • Heap Blocks: exact=999
15. 0.062 2.382 ↓ 0.0 0 1

BitmapAnd (cost=391.02..391.02 rows=52 width=0) (actual time=2.382..2.382 rows=0 loops=1)

16. 0.348 0.348 ↑ 1.2 2,931 1

Bitmap Index Scan on ref_margin_xperf04 (cost=0.00..67.34 rows=3,589 width=0) (actual time=0.348..0.348 rows=2,931 loops=1)

  • Index Cond: (id_ss_transaction IS NOT NULL)
17. 1.972 1.972 ↑ 1.2 15,130 1

Bitmap Index Scan on ref_margin_xperf01 (cost=0.00..323.42 rows=17,466 width=0) (actual time=1.972..1.972 rows=15,130 loops=1)

  • Index Cond: (id_filiale = 3)
18. 0.138 0.390 ↑ 1.0 591 1

Hash (cost=20.77..20.77 rows=618 width=20) (actual time=0.390..0.390 rows=591 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 38kB
19. 0.252 0.252 ↑ 1.0 591 1

Seq Scan on ref_sub_transaction (cost=0.00..20.77 rows=618 width=20) (actual time=0.017..0.252 rows=591 loops=1)

  • Filter: (id_filiale = 3)
  • Rows Removed by Filter: 3
20. 10.304 10.304 ↑ 1.0 1 448

Index Scan using ref_transaction_xperf14 on ref_transaction (cost=0.43..8.37 rows=1 width=32) (actual time=0.022..0.023 rows=1 loops=448)

  • Index Cond: ((id_filiale = 3) AND (id_transaction = ref_sub_transaction.id_transaction))
  • Filter: (is_active AND (COALESCE((ref_sub_transaction.date_paid)::date, (date_paid)::date) <= CURRENT_DATE))
  • Rows Removed by Filter: 9
21. 1.792 1.792 ↑ 1.0 1 448

Seq Scan on ref_subsidiary ref_subsidiary_1 (cost=0.00..1.06 rows=1 width=12) (actual time=0.003..0.004 rows=1 loops=448)

  • Filter: (id_wis = 3)
  • Rows Removed by Filter: 4
22. 0.050 23.149 ↓ 284.0 284 1

Subquery Scan on *SELECT* 2 (cost=633.56..633.59 rows=1 width=36) (actual time=22.988..23.149 rows=284 loops=1)

23. 0.094 23.099 ↓ 284.0 284 1

Unique (cost=633.56..633.58 rows=1 width=52) (actual time=22.985..23.099 rows=284 loops=1)

24. 0.351 23.005 ↓ 294.0 294 1

Sort (cost=633.56..633.57 rows=1 width=52) (actual time=22.984..23.005 rows=294 loops=1)

  • Sort Key: ref_transaction_1.id_transaction, ref_transaction_1.id_int_agent_out, ref_transaction_1.id_ext_agent_out, ref_margin_1.id_ss_transaction, (COALESCE((ref_sub_transaction_1.date_paid)::date, (ref_transaction_1.date_paid)::date)), (round((((COALESCE((ref_margin_1.amount_base_rec / 2.0), '0'::numeric))::double precision * ('1'::double precision + (COALESCE(ref_transaction_1.total_vat, '0'::double precision) / '100'::double precision))))::numeric(10,4), 2))
  • Sort Method: quicksort Memory: 47kB
25. 5.913 22.654 ↓ 294.0 294 1

Nested Loop (cost=421.50..633.55 rows=1 width=52) (actual time=2.358..22.654 rows=294 loops=1)

  • Join Filter: (COALESCE((ref_sub_transaction_1.date_paid)::date, (ref_transaction_1.date_paid)::date) >= ((((((CASE WHEN (date_part('month'::text, (CURRENT_DATE)::timestamp without time zone) < (ref_subsidiary_2.month_fiscal_start)::double precision) THEN (date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - '1'::double precision) ELSE date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) END)::text || '-'::text) || (ref_subsidiary_2.month_fiscal_start)::text) || '-'::text) || (ref_subsidiary_2.day_fiscal_start)::text))::date)
  • Rows Removed by Join Filter: 154
26. 0.836 14.949 ↓ 448.0 448 1

Nested Loop (cost=421.50..632.39 rows=1 width=50) (actual time=2.321..14.949 rows=448 loops=1)

  • Join Filter: ((ref_margin_1.id_transaction = ref_transaction_1.id_transaction) AND (ref_margin_1.id_int_agent = ref_transaction_1.id_int_agent_out))
27. 0.616 6.049 ↓ 448.0 448 1

Hash Join (cost=421.06..624.01 rows=1 width=34) (actual time=2.287..6.049 rows=448 loops=1)

  • Hash Cond: ((ref_margin_1.id_transaction = ref_sub_transaction_1.id_transaction) AND (ref_margin_1.id_ss_transaction = ref_sub_transaction_1.id_ss_transaction))
28. 3.314 5.090 ↓ 84.2 421 1

Bitmap Heap Scan on ref_margin ref_margin_1 (cost=391.02..593.90 rows=5 width=22) (actual time=1.929..5.090 rows=421 loops=1)

  • Recheck Cond: ((id_ss_transaction IS NOT NULL) AND (id_filiale = 3))
  • Filter: (((ss_type_margin)::text = 'Agent'::text) AND ((type_margin)::text = 'OUT'::text))
  • Rows Removed by Filter: 2504
  • Heap Blocks: exact=999
29. 0.060 1.776 ↓ 0.0 0 1

BitmapAnd (cost=391.02..391.02 rows=52 width=0) (actual time=1.776..1.776 rows=0 loops=1)

30. 0.270 0.270 ↑ 1.2 2,931 1

Bitmap Index Scan on ref_margin_xperf04 (cost=0.00..67.34 rows=3,589 width=0) (actual time=0.270..0.270 rows=2,931 loops=1)

  • Index Cond: (id_ss_transaction IS NOT NULL)
31. 1.446 1.446 ↑ 1.2 15,130 1

Bitmap Index Scan on ref_margin_xperf01 (cost=0.00..323.42 rows=17,466 width=0) (actual time=1.446..1.446 rows=15,130 loops=1)

  • Index Cond: (id_filiale = 3)
32. 0.117 0.343 ↑ 1.0 591 1

Hash (cost=20.77..20.77 rows=618 width=20) (actual time=0.343..0.343 rows=591 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 38kB
33. 0.226 0.226 ↑ 1.0 591 1

Seq Scan on ref_sub_transaction ref_sub_transaction_1 (cost=0.00..20.77 rows=618 width=20) (actual time=0.015..0.226 rows=591 loops=1)

  • Filter: (id_filiale = 3)
  • Rows Removed by Filter: 3
34. 8.064 8.064 ↑ 1.0 1 448

Index Scan using ref_transaction_xperf14 on ref_transaction ref_transaction_1 (cost=0.43..8.37 rows=1 width=32) (actual time=0.017..0.018 rows=1 loops=448)

  • Index Cond: ((id_filiale = 3) AND (id_transaction = ref_sub_transaction_1.id_transaction))
  • Filter: (is_active AND (COALESCE((ref_sub_transaction_1.date_paid)::date, (date_paid)::date) <= CURRENT_DATE))
  • Rows Removed by Filter: 9
35. 1.792 1.792 ↑ 1.0 1 448

Seq Scan on ref_subsidiary ref_subsidiary_2 (cost=0.00..1.06 rows=1 width=12) (actual time=0.003..0.004 rows=1 loops=448)

  • Filter: (id_wis = 3)
  • Rows Removed by Filter: 4
36. 0.159 143.653 ↓ 890.0 890 1

Subquery Scan on *SELECT* 3 (cost=6,253.12..6,253.15 rows=1 width=36) (actual time=143.129..143.653 rows=890 loops=1)

37. 0.292 143.494 ↓ 890.0 890 1

Unique (cost=6,253.12..6,253.14 rows=1 width=52) (actual time=143.126..143.494 rows=890 loops=1)

38. 0.932 143.202 ↓ 890.0 890 1

Sort (cost=6,253.12..6,253.13 rows=1 width=52) (actual time=143.124..143.202 rows=890 loops=1)

  • Sort Key: ref_transaction_2.id_transaction, ref_transaction_2.id_int_agent_in, ref_transaction_2.id_ext_agent_in, ref_margin_2.id_ss_transaction, ((ref_transaction_2.date_paid)::date), (round((((((COALESCE(ref_margin_2.amount_base_rec, '0'::numeric))::double precision * ('1'::double precision + (COALESCE(ref_transaction_2.total_vat, '0'::double precision) / '100'::double precision))))::numeric(10,4) - (SubPlan 2)) / 2.0), 2))
  • Sort Method: quicksort Memory: 94kB
39. 13.364 142.270 ↓ 890.0 890 1

Nested Loop (cost=2,434.16..6,253.11 rows=1 width=52) (actual time=19.106..142.270 rows=890 loops=1)

40. 8.314 63.936 ↓ 11.9 890 1

Nested Loop (cost=2,423.15..5,110.15 rows=75 width=32) (actual time=18.844..63.936 rows=890 loops=1)

  • Join Filter: ((ref_transaction_2.date_paid)::date >= ((((((CASE WHEN (date_part('month'::text, (CURRENT_DATE)::timestamp without time zone) < (ref_subsidiary_3.month_fiscal_start)::double precision) THEN (date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - '1'::double precision) ELSE date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) END)::text || '-'::text) || (ref_subsidiary_3.month_fiscal_start)::text) || '-'::text) || (ref_subsidiary_3.day_fiscal_start)::text))::date)
  • Rows Removed by Join Filter: 965
41. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on ref_subsidiary ref_subsidiary_3 (cost=0.00..1.06 rows=1 width=12) (actual time=0.009..0.012 rows=1 loops=1)

  • Filter: (id_wis = 3)
  • Rows Removed by Filter: 4
42. 42.413 55.610 ↓ 8.2 1,855 1

Bitmap Heap Scan on ref_transaction ref_transaction_2 (cost=2,423.15..5,092.13 rows=226 width=32) (actual time=16.643..55.610 rows=1,855 loops=1)

  • Recheck Cond: ((id_filiale = 3) AND is_active)
  • Rows Removed by Index Recheck: 42922
  • Filter: ((date_paid)::date <= CURRENT_DATE)
  • Rows Removed by Filter: 614
  • Heap Blocks: lossy=2694
43. 1.721 13.197 ↓ 0.0 0 1

BitmapAnd (cost=2,423.15..2,423.15 rows=693 width=0) (actual time=13.197..13.197 rows=0 loops=1)

44. 2.235 2.235 ↓ 19.9 492,800 1

Bitmap Index Scan on ref_transaction_xperf1 (cost=0.00..29.32 rows=24,824 width=0) (actual time=2.235..2.235 rows=492,800 loops=1)

  • Index Cond: (id_filiale = 3)
45. 9.241 9.241 ↓ 1.1 133,788 1

Bitmap Index Scan on ref_transaction_xperf6 (cost=0.00..2,393.47 rows=125,810 width=0) (actual time=9.241..9.241 rows=133,788 loops=1)

46. 7.120 32.040 ↑ 1.0 1 890

Bitmap Heap Scan on ref_margin ref_margin_2 (cost=11.00..15.03 rows=1 width=22) (actual time=0.034..0.036 rows=1 loops=890)

  • Recheck Cond: ((id_transaction = ref_transaction_2.id_transaction) AND (id_int_agent = ref_transaction_2.id_int_agent_in))
  • Filter: ((id_ss_transaction IS NULL) AND (id_filiale = 3) AND ((ss_type_margin)::text = 'Agent'::text) AND ((type_margin)::text = 'IN'::text))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=1811
47. 2.670 24.920 ↓ 0.0 0 890

BitmapAnd (cost=11.00..11.00 rows=1 width=0) (actual time=0.028..0.028 rows=0 loops=890)

48. 8.010 8.010 ↓ 1.3 16 890

Bitmap Index Scan on ref_margin_xperf02 (cost=0.00..4.39 rows=12 width=0) (actual time=0.009..0.009 rows=16 loops=890)

  • Index Cond: (id_transaction = ref_transaction_2.id_transaction)
49. 14.240 14.240 ↑ 2.3 120 890

Bitmap Index Scan on ref_margin_xperf07 (cost=0.00..6.36 rows=274 width=0) (actual time=0.016..0.016 rows=120 loops=890)

  • Index Cond: (id_int_agent = ref_transaction_2.id_int_agent_in)
50.          

SubPlan (forNested Loop)

51. 8.900 32.930 ↑ 1.0 1 890

Aggregate (cost=15.27..15.30 rows=1 width=32) (actual time=0.037..0.037 rows=1 loops=890)

52. 4.450 24.030 ↓ 0.0 0 890

Bitmap Heap Scan on ref_margin a (cost=11.25..15.27 rows=1 width=6) (actual time=0.027..0.027 rows=0 loops=890)

  • Recheck Cond: ((id_transaction = ref_margin_2.id_transaction) AND (id_int_agent = ref_transaction_2.id_int_agent_in))
  • Filter: ((id_ss_transaction IS NOT NULL) AND ((type_margin)::text = 'IN'::text))
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=1811
53. 2.670 19.580 ↓ 0.0 0 890

BitmapAnd (cost=11.25..11.25 rows=1 width=0) (actual time=0.022..0.022 rows=0 loops=890)

54. 6.230 6.230 ↓ 1.3 16 890

Bitmap Index Scan on ref_margin_xperf02 (cost=0.00..4.52 rows=12 width=0) (actual time=0.007..0.007 rows=16 loops=890)

  • Index Cond: (id_transaction = ref_margin_2.id_transaction)
55. 10.680 10.680 ↑ 2.3 120 890

Bitmap Index Scan on ref_margin_xperf07 (cost=0.00..6.48 rows=274 width=0) (actual time=0.012..0.012 rows=120 loops=890)

  • Index Cond: (id_int_agent = ref_transaction_2.id_int_agent_in)
56. 0.158 131.619 ↓ 890.0 890 1

Subquery Scan on *SELECT* 4 (cost=6,253.12..6,253.15 rows=1 width=36) (actual time=131.100..131.619 rows=890 loops=1)

57. 0.286 131.461 ↓ 890.0 890 1

Unique (cost=6,253.12..6,253.14 rows=1 width=52) (actual time=131.098..131.461 rows=890 loops=1)

58. 0.941 131.175 ↓ 890.0 890 1

Sort (cost=6,253.12..6,253.13 rows=1 width=52) (actual time=131.096..131.175 rows=890 loops=1)

  • Sort Key: ref_transaction_3.id_transaction, ref_transaction_3.id_int_agent_out, ref_transaction_3.id_ext_agent_out, ref_margin_3.id_ss_transaction, ((ref_transaction_3.date_paid)::date), (round((((((COALESCE(ref_margin_3.amount_base_rec, '0'::numeric))::double precision * ('1'::double precision + (COALESCE(ref_transaction_3.total_vat, '0'::double precision) / '100'::double precision))))::numeric(10,4) - (SubPlan 3)) / 2.0), 2))
  • Sort Method: quicksort Memory: 94kB
59. 13.090 130.234 ↓ 890.0 890 1

Nested Loop (cost=2,434.16..6,253.11 rows=1 width=52) (actual time=17.338..130.234 rows=890 loops=1)

60. 8.133 59.294 ↓ 11.9 890 1

Nested Loop (cost=2,423.15..5,110.15 rows=75 width=32) (actual time=17.127..59.294 rows=890 loops=1)

  • Join Filter: ((ref_transaction_3.date_paid)::date >= ((((((CASE WHEN (date_part('month'::text, (CURRENT_DATE)::timestamp without time zone) < (ref_subsidiary_4.month_fiscal_start)::double precision) THEN (date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - '1'::double precision) ELSE date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) END)::text || '-'::text) || (ref_subsidiary_4.month_fiscal_start)::text) || '-'::text) || (ref_subsidiary_4.day_fiscal_start)::text))::date)
  • Rows Removed by Join Filter: 965
61. 0.016 0.016 ↑ 1.0 1 1

Seq Scan on ref_subsidiary ref_subsidiary_4 (cost=0.00..1.06 rows=1 width=12) (actual time=0.012..0.016 rows=1 loops=1)

  • Filter: (id_wis = 3)
  • Rows Removed by Filter: 4
62. 39.428 51.145 ↓ 8.2 1,855 1

Bitmap Heap Scan on ref_transaction ref_transaction_3 (cost=2,423.15..5,092.13 rows=226 width=32) (actual time=15.064..51.145 rows=1,855 loops=1)

  • Recheck Cond: ((id_filiale = 3) AND is_active)
  • Rows Removed by Index Recheck: 42922
  • Filter: ((date_paid)::date <= CURRENT_DATE)
  • Rows Removed by Filter: 614
  • Heap Blocks: lossy=2694
63. 1.751 11.717 ↓ 0.0 0 1

BitmapAnd (cost=2,423.15..2,423.15 rows=693 width=0) (actual time=11.717..11.717 rows=0 loops=1)

64. 2.232 2.232 ↓ 19.9 492,800 1

Bitmap Index Scan on ref_transaction_xperf1 (cost=0.00..29.32 rows=24,824 width=0) (actual time=2.232..2.232 rows=492,800 loops=1)

  • Index Cond: (id_filiale = 3)
65. 7.734 7.734 ↓ 1.1 133,788 1

Bitmap Index Scan on ref_transaction_xperf6 (cost=0.00..2,393.47 rows=125,810 width=0) (actual time=7.734..7.734 rows=133,788 loops=1)

66. 6.230 26.700 ↑ 1.0 1 890

Bitmap Heap Scan on ref_margin ref_margin_3 (cost=11.00..15.03 rows=1 width=22) (actual time=0.029..0.030 rows=1 loops=890)

  • Recheck Cond: ((id_transaction = ref_transaction_3.id_transaction) AND (id_int_agent = ref_transaction_3.id_int_agent_out))
  • Filter: ((id_ss_transaction IS NULL) AND (id_filiale = 3) AND ((ss_type_margin)::text = 'Agent'::text) AND ((type_margin)::text = 'OUT'::text))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=1801
67. 2.670 20.470 ↓ 0.0 0 890

BitmapAnd (cost=11.00..11.00 rows=1 width=0) (actual time=0.023..0.023 rows=0 loops=890)

68. 7.120 7.120 ↓ 1.3 16 890

Bitmap Index Scan on ref_margin_xperf02 (cost=0.00..4.39 rows=12 width=0) (actual time=0.008..0.008 rows=16 loops=890)

  • Index Cond: (id_transaction = ref_transaction_3.id_transaction)
69. 10.680 10.680 ↑ 2.9 95 890

Bitmap Index Scan on ref_margin_xperf07 (cost=0.00..6.36 rows=274 width=0) (actual time=0.012..0.012 rows=95 loops=890)

  • Index Cond: (id_int_agent = ref_transaction_3.id_int_agent_out)
70.          

SubPlan (forNested Loop)

71. 8.900 31.150 ↑ 1.0 1 890

Aggregate (cost=15.27..15.30 rows=1 width=32) (actual time=0.035..0.035 rows=1 loops=890)

72. 4.450 22.250 ↓ 0.0 0 890

Bitmap Heap Scan on ref_margin a_1 (cost=11.25..15.27 rows=1 width=6) (actual time=0.025..0.025 rows=0 loops=890)

  • Recheck Cond: ((id_transaction = ref_margin_3.id_transaction) AND (id_int_agent = ref_transaction_3.id_int_agent_out))
  • Filter: ((id_ss_transaction IS NOT NULL) AND ((type_margin)::text = 'OUT'::text))
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=1801
73. 2.670 17.800 ↓ 0.0 0 890

BitmapAnd (cost=11.25..11.25 rows=1 width=0) (actual time=0.020..0.020 rows=0 loops=890)

74. 6.230 6.230 ↓ 1.3 16 890

Bitmap Index Scan on ref_margin_xperf02 (cost=0.00..4.52 rows=12 width=0) (actual time=0.007..0.007 rows=16 loops=890)

  • Index Cond: (id_transaction = ref_margin_3.id_transaction)
75. 8.900 8.900 ↑ 2.9 95 890

Bitmap Index Scan on ref_margin_xperf07 (cost=0.00..6.48 rows=274 width=0) (actual time=0.010..0.010 rows=95 loops=890)

  • Index Cond: (id_int_agent = ref_transaction_3.id_int_agent_out)
76. 1.182 686.618 ↓ 3.8 347 1

Sort (cost=90,873.35..90,873.58 rows=91 width=308) (actual time=686.594..686.618 rows=347 loops=1)

  • Sort Key: ref_people.firstname, ref_people.name, ref_people.id_ext, ref_people.id_int, ref_people.email_pro, ((COALESCE(ref_people.date_first_activation, ref_people.date_apply))::date), ((COALESCE((SubPlan 6), COALESCE(ref_people.date_first_activation, ref_people.date_apply)))::date), (((COALESCE((SubPlan 8), COALESCE(ref_people.date_first_activation, ref_people.date_apply)))::date + '1 year'::interval)), (CASE WHEN ((((COALESCE((SubPlan 10), COALESCE(ref_people.date_first_activation, ref_people.date_apply)))::date + '1 year'::interval) >= ((((((CASE WHEN (date_part('month'::text, (CURRENT_DATE)::timestamp without time zone) < (ref_subsidiary.month_fiscal_start)::double precision) THEN (date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - '1'::double precision) ELSE date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) END)::text || '-'::text) || (ref_subsidiary.month_fiscal_start)::text) || '-'::text) || (ref_subsidiary.day_fiscal_start)::text))::date) AND (((COALESCE((SubPlan 12), COALESCE(ref_people.date_first_activation, ref_people.date_apply)))::date + '1 year'::interval) <= (((((((CASE WHEN (date_part('month'::text, (CURRENT_DATE)::timestamp without time zone) < (ref_subsidiary.month_fiscal_start)::double precision) THEN (date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - '1'::double precision) ELSE date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) END)::text || '-'::text) || (ref_subsidiary.month_fiscal_start)::text) || '-'::text) || (ref_subsidiary.day_fiscal_start)::text))::date + '1 year'::interval))) THEN 'prorata'::text ELSE ''::text END), (((((((CASE WHEN (date_part('month'::text, (CURRENT_DATE)::timestamp without time zone) < (ref_subsidiary.month_fiscal_start)::double precision) THEN (date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - '1'::double precision) ELSE date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) END)::text || '-'::text) || (ref_subsidiary.month_fiscal_start)::text) || '-'::text) || (ref_subsidiary.day_fiscal_start)::text))::date), (age((((((((CASE WHEN (date_part('month'::text, (CURRENT_DATE)::timestamp without time zone) < (ref_subsidiary.month_fiscal_start)::double precision) THEN (date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - '1'::double precision) ELSE date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) END)::text || '-'::text) || (ref_subsidiary.month_fiscal_start)::text) || '-'::text) || (ref_subsidiary.day_fiscal_start)::text))::date)::timestamp with time zone, ((COALESCE((SubPlan 14), COALESCE(ref_people.date_first_activation, ref_people.date_apply)))::date)::timestamp with time zone)), (((((date_part('year'::text, (((((((CASE WHEN (date_part('month'::text, (CURRENT_DATE)::timestamp without time zone) < (ref_subsidiary.month_fiscal_start)::double precision) THEN (date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - '1'::double precision) ELSE date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) END)::text || '-'::text) || (ref_subsidiary.month_fiscal_start)::text) || '-'::text) || (ref_subsidiary.day_fiscal_start)::text))::date)::timestamp without time zone) - date_part('year'::text, ((COALESCE((SubPlan 16), COALESCE(ref_people.date_first_activation, ref_people.date_apply)))::date)::timestamp without time zone)))::integer * 12) + ((date_part('month'::text, (((((((CASE WHEN (date_part('month'::text, (CURRENT_DATE)::timestamp without time zone) < (ref_subsidiary.month_fiscal_start)::double precision) THEN (date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - '1'::double precision) ELSE date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) END)::text || '-'::text) || (ref_subsidiary.month_fiscal_start)::text) || '-'::text) || (ref_subsidiary.day_fiscal_start)::text))::date)::timestamp without time zone) - date_part('month'::text, ((COALESCE((SubPlan 18), COALESCE(ref_people.date_first_activation, ref_people.date_apply)))::date)::timestamp without time zone)))::integer)), (CASE WHEN ((((date_part('year'::text, (((((((CASE WHEN (date_part('month'::text, (CURRENT_DATE)::timestamp without time zone) < (ref_subsidiary.month_fiscal_start)::double precision) THEN (date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - '1'::double precision) ELSE date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) END)::text || '-'::text) || (ref_subsidiary.month_fiscal_start)::text) || '-'::text) || (ref_subsidiary.day_fiscal_start)::text))::date + '1 year'::interval)) - date_part('year'::text, ((COALESCE((SubPlan 20), COALESCE(ref_people.date_first_activation, ref_people.date_apply)))::date)::timestamp without time zone)) * '12'::double precision) + ((('6'::double precision - date_part('month'::text, ((COALESCE((SubPlan 22), COALESCE(ref_people.date_first_activation, ref_people.date_apply)))::date)::timestamp without time zone)))::integer)::double precision) < '12'::double precision) THEN 'non soumis à maintien de qualif'::text ELSE 'soumis'::text END), ref_people.id_qualification, ref_people.code_qualification, (CASE WHEN (ref_people.id_qualification = 1) THEN niveau_rem.niv_bronze WHEN (ref_people.id_qualification = 2) THEN niveau_rem.niv_argent WHEN (ref_people.id_qualification = 3) THEN niveau_rem.niv_or WHEN (ref_people.id_qualification = 4) THEN niveau_rem.niv_platinium WHEN (ref_people.id_qualification = 5) THEN niveau_rem.niv_platinium_assoc ELSE '0'::numeric END), (CASE WHEN ((((COALESCE((SubPlan 24), COALESCE(ref_people.date_first_activation, ref_people.date_apply)))::date + '1 year'::interval) >= ((((((CASE WHEN (date_part('month'::text, (CURRENT_DATE)::timestamp without time zone) < (ref_subsidiary.month_fiscal_start)::double precision) THEN (date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - '1'::double precision) ELSE date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) END)::text || '-'::text) || (ref_subsidiary.month_fiscal_start)::text) || '-'::text) || (ref_subsidiary.day_fiscal_start)::text))::date) AND (((COALESCE((SubPlan 26), COALESCE(ref_people.date_first_activation, ref_people.date_apply)))::date + '1 year'::interval) <= (((((((CASE WHEN (date_part('month'::text, (CURRENT_DATE)::timestamp without time zone) < (ref_subsidiary.month_fiscal_start)::double precision) THEN (date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - '1'::double precision) ELSE date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) END)::text || '-'::text) || (ref_subsidiary.month_fiscal_start)::text) || '-'::text) || (ref_subsidiary.day_fiscal_start)::text))::date + '1 year'::interval))) THEN (((CASE WHEN (ref_people.id_qualification = 1) THEN niveau_rem.niv_bronze WHEN (ref_people.id_qualification = 2) THEN niveau_rem.niv_argent WHEN (ref_people.id_qualification = 3) THEN niveau_rem.niv_or WHEN (ref_people.id_qualification = 4) THEN niveau_rem.niv_platinium WHEN (ref_people.id_qualification = 5) THEN niveau_rem.niv_platinium_assoc ELSE niveau_rem.niv_bronze END / '12'::numeric) * (((((date_part('year'::text, (((((((CASE WHEN (date_part('month'::text, (CURRENT_DATE)::timestamp without time zone) < (ref_subsidiary.month_fiscal_start)::double precision) THEN (date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - '1'::double precision) ELSE date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) END)::text || '-'::text) || (ref_subsidiary.month_fiscal_start)::text) || '-'::text) || (ref_subsidiary.day_fiscal_start)::text))::date)::timestamp without time zone) - date_part('year'::text, ((COALESCE((SubPlan 28), COALESCE(ref_people.date_first_activation, ref_people.date_apply)))::date)::timestamp without time zone)))::integer * 12) + ((date_part('month'::text, (((((((CASE WHEN (date_part('month'::text, (CURRENT_DATE)::timestamp without time zone) < (ref_subsidiary.month_fiscal_start)::double precision) THEN (date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - '1'::double precision) ELSE date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) END)::text || '-'::text) || (ref_subsidiary.month_fiscal_start)::text) || '-'::text) || (ref_subsidiary.day_fiscal_start)::text))::date)::timestamp without time zone) - date_part('month'::text, ((COALESCE((SubPlan 30), COALESCE(ref_people.date_first_activation, ref_people.date_apply)))::date)::timestamp without time zone)))::integer))::numeric))::numeric(10,2) WHEN (((COALESCE((SubPlan 32), COALESCE(ref_people.date_first_activation, ref_people.date_apply)))::date + '1 year'::interval) <= ((((((CASE WHEN (date_part('month'::text, (CURRENT_DATE)::timestamp without time zone) < (ref_subsidiary.month_fiscal_start)::double precision) THEN (date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - '1'::double precision) ELSE date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) END)::text || '-'::text) || (ref_subsidiary.month_fiscal_start)::text) || '-'::text) || (ref_subsidiary.day_fiscal_start)::text))::date) THEN CASE WHEN (ref_people.id_qualification = 1) THEN niveau_rem.niv_bronze WHEN (ref_people.id_qualification = 2) THEN niveau_rem.niv_argent WHEN (ref_people.id_qualification = 3) THEN niveau_rem.niv_or WHEN (ref_people.id_qualification = 4) THEN niveau_rem.niv_platinium WHEN (ref_people.id_qualification = 5) THEN niveau_rem.niv_platinium_assoc ELSE niveau_rem.niv_bronze END ELSE '0'::numeric END), ((SubPlan 33)), ((SubPlan 34)), ((SubPlan 35)), ((SubPlan 36)), ((SubPlan 37)), ((SubPlan 38)), ((COALESCE((SubPlan 39), '0'::numeric))::numeric(10,2))
  • Sort Method: quicksort Memory: 117kB
77. 27.510 685.436 ↓ 3.8 347 1

Nested Loop (cost=1,757.41..90,870.39 rows=91 width=308) (actual time=338.136..685.436 rows=347 loops=1)

78. 0.004 0.032 ↑ 1.0 1 1

Nested Loop (cost=0.00..1.09 rows=1 width=92) (actual time=0.026..0.032 rows=1 loops=1)

79. 0.008 0.008 ↑ 1.0 1 1

CTE Scan on niveau_rem (cost=0.00..0.02 rows=1 width=80) (actual time=0.008..0.008 rows=1 loops=1)

80. 0.020 0.020 ↑ 1.0 1 1

Seq Scan on ref_subsidiary (cost=0.00..1.06 rows=1 width=12) (actual time=0.015..0.020 rows=1 loops=1)

  • Filter: (id_wis = 3)
  • Rows Removed by Filter: 4
81. 2.955 12.243 ↓ 3.8 347 1

Bitmap Heap Scan on ref_people (cost=1,757.41..5,011.57 rows=91 width=96) (actual time=9.449..12.243 rows=347 loops=1)

  • Recheck Cond: ((id_filiale = 3) AND (id_type = 5))
  • Filter: (is_active AND (id_qualification > 0) AND (id_statut <> 2))
  • Rows Removed by Filter: 956
  • Heap Blocks: exact=456
82. 0.748 9.288 ↓ 0.0 0 1

BitmapAnd (cost=1,757.41..1,757.41 rows=908 width=0) (actual time=9.288..9.288 rows=0 loops=1)

83. 5.720 5.720 ↑ 1.0 41,521 1

Bitmap Index Scan on ref_people_xperf12 (cost=0.00..845.58 rows=42,287 width=0) (actual time=5.720..5.720 rows=41,521 loops=1)

  • Index Cond: (is_active = true)
84. 2.820 2.820 ↓ 1.8 25,141 1

Bitmap Index Scan on ref_people_xperf06 (cost=0.00..911.53 rows=14,113 width=0) (actual time=2.820..2.820 rows=25,141 loops=1)

  • Index Cond: ((id_filiale = 3) AND (id_type = 5))
85.          

SubPlan (forNested Loop)

86. 0.347 2.776 ↑ 1.0 1 347

Result (cost=6.23..6.24 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=347)

87.          

Initplan (forResult)

88. 0.347 2.429 ↑ 1.0 1 347

Limit (cost=0.28..6.23 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=347)

89. 2.082 2.082 ↑ 2.0 1 347

Index Scan using ref_qualification_xperf3 on ref_qualification (cost=0.28..12.17 rows=2 width=8) (actual time=0.006..0.006 rows=1 loops=347)

  • Index Cond: ((id_int = ref_people.id_int) AND (date_histo IS NOT NULL))
  • Filter: is_active
90. 0.347 1.388 ↑ 1.0 1 347

Result (cost=6.23..6.24 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=347)

91.          

Initplan (forResult)

92. 0.000 1.041 ↑ 1.0 1 347

Limit (cost=0.28..6.23 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=347)

93. 1.041 1.041 ↑ 2.0 1 347

Index Scan using ref_qualification_xperf3 on ref_qualification ref_qualification_1 (cost=0.28..12.17 rows=2 width=8) (actual time=0.003..0.003 rows=1 loops=347)

  • Index Cond: ((id_int = ref_people.id_int) AND (date_histo IS NOT NULL))
  • Filter: is_active
94. 0.347 1.388 ↑ 1.0 1 347

Result (cost=6.23..6.24 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=347)

95.          

Initplan (forResult)

96. 0.000 1.041 ↑ 1.0 1 347

Limit (cost=0.28..6.23 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=347)

97. 1.041 1.041 ↑ 2.0 1 347

Index Scan using ref_qualification_xperf3 on ref_qualification ref_qualification_2 (cost=0.28..12.17 rows=2 width=8) (actual time=0.002..0.003 rows=1 loops=347)

  • Index Cond: ((id_int = ref_people.id_int) AND (date_histo IS NOT NULL))
  • Filter: is_active
98. 0.295 1.770 ↑ 1.0 1 295

Result (cost=6.23..6.24 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=295)

99.          

Initplan (forResult)

100. 0.295 1.475 ↑ 1.0 1 295

Limit (cost=0.28..6.23 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=295)

101. 1.180 1.180 ↑ 2.0 1 295

Index Scan using ref_qualification_xperf3 on ref_qualification ref_qualification_3 (cost=0.28..12.17 rows=2 width=8) (actual time=0.004..0.004 rows=1 loops=295)

  • Index Cond: ((id_int = ref_people.id_int) AND (date_histo IS NOT NULL))
  • Filter: is_active
102. 0.347 1.735 ↑ 1.0 1 347

Result (cost=6.23..6.24 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=347)

103.          

Initplan (forResult)

104. 0.347 1.388 ↑ 1.0 1 347

Limit (cost=0.28..6.23 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=347)

105. 1.041 1.041 ↑ 2.0 1 347

Index Scan using ref_qualification_xperf3 on ref_qualification ref_qualification_4 (cost=0.28..12.17 rows=2 width=8) (actual time=0.003..0.003 rows=1 loops=347)

  • Index Cond: ((id_int = ref_people.id_int) AND (date_histo IS NOT NULL))
  • Filter: is_active
106. 0.347 1.735 ↑ 1.0 1 347

Result (cost=6.23..6.24 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=347)

107.          

Initplan (forResult)

108. 0.347 1.388 ↑ 1.0 1 347

Limit (cost=0.28..6.23 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=347)

109. 1.041 1.041 ↑ 2.0 1 347

Index Scan using ref_qualification_xperf3 on ref_qualification ref_qualification_5 (cost=0.28..12.17 rows=2 width=8) (actual time=0.003..0.003 rows=1 loops=347)

  • Index Cond: ((id_int = ref_people.id_int) AND (date_histo IS NOT NULL))
  • Filter: is_active
110. 0.347 1.735 ↑ 1.0 1 347

Result (cost=6.23..6.24 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=347)

111.          

Initplan (forResult)

112. 0.347 1.388 ↑ 1.0 1 347

Limit (cost=0.28..6.23 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=347)

113. 1.041 1.041 ↑ 2.0 1 347

Index Scan using ref_qualification_xperf3 on ref_qualification ref_qualification_6 (cost=0.28..12.17 rows=2 width=8) (actual time=0.003..0.003 rows=1 loops=347)

  • Index Cond: ((id_int = ref_people.id_int) AND (date_histo IS NOT NULL))
  • Filter: is_active
114. 0.347 1.735 ↑ 1.0 1 347

Result (cost=6.23..6.24 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=347)

115.          

Initplan (forResult)

116. 0.347 1.388 ↑ 1.0 1 347

Limit (cost=0.28..6.23 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=347)

117. 1.041 1.041 ↑ 2.0 1 347

Index Scan using ref_qualification_xperf3 on ref_qualification ref_qualification_7 (cost=0.28..12.17 rows=2 width=8) (actual time=0.003..0.003 rows=1 loops=347)

  • Index Cond: ((id_int = ref_people.id_int) AND (date_histo IS NOT NULL))
  • Filter: is_active
118. 0.347 1.388 ↑ 1.0 1 347

Result (cost=6.23..6.24 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=347)

119.          

Initplan (forResult)

120. 0.000 1.041 ↑ 1.0 1 347

Limit (cost=0.28..6.23 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=347)

121. 1.041 1.041 ↑ 2.0 1 347

Index Scan using ref_qualification_xperf3 on ref_qualification ref_qualification_8 (cost=0.28..12.17 rows=2 width=8) (actual time=0.003..0.003 rows=1 loops=347)

  • Index Cond: ((id_int = ref_people.id_int) AND (date_histo IS NOT NULL))
  • Filter: is_active
122. 0.347 1.388 ↑ 1.0 1 347

Result (cost=6.23..6.24 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=347)

123.          

Initplan (forResult)

124. 0.000 1.041 ↑ 1.0 1 347

Limit (cost=0.28..6.23 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=347)

125. 1.041 1.041 ↑ 2.0 1 347

Index Scan using ref_qualification_xperf3 on ref_qualification ref_qualification_9 (cost=0.28..12.17 rows=2 width=8) (actual time=0.003..0.003 rows=1 loops=347)

  • Index Cond: ((id_int = ref_people.id_int) AND (date_histo IS NOT NULL))
  • Filter: is_active
126. 0.295 1.475 ↑ 1.0 1 295

Result (cost=6.23..6.24 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=295)

127.          

Initplan (forResult)

128. 0.295 1.180 ↑ 1.0 1 295

Limit (cost=0.28..6.23 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=295)

129. 0.885 0.885 ↑ 2.0 1 295

Index Scan using ref_qualification_xperf3 on ref_qualification ref_qualification_10 (cost=0.28..12.17 rows=2 width=8) (actual time=0.003..0.003 rows=1 loops=295)

  • Index Cond: ((id_int = ref_people.id_int) AND (date_histo IS NOT NULL))
  • Filter: is_active
130. 0.230 1.150 ↑ 1.0 1 230

Result (cost=6.23..6.24 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=230)

131.          

Initplan (forResult)

132. 0.230 0.920 ↑ 1.0 1 230

Limit (cost=0.28..6.23 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=230)

133. 0.690 0.690 ↑ 2.0 1 230

Index Scan using ref_qualification_xperf3 on ref_qualification ref_qualification_11 (cost=0.28..12.17 rows=2 width=8) (actual time=0.003..0.003 rows=1 loops=230)

  • Index Cond: ((id_int = ref_people.id_int) AND (date_histo IS NOT NULL))
  • Filter: is_active
134. 0.230 1.150 ↑ 1.0 1 230

Result (cost=6.23..6.24 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=230)

135.          

Initplan (forResult)

136. 0.230 0.920 ↑ 1.0 1 230

Limit (cost=0.28..6.23 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=230)

137. 0.690 0.690 ↑ 2.0 1 230

Index Scan using ref_qualification_xperf3 on ref_qualification ref_qualification_12 (cost=0.28..12.17 rows=2 width=8) (actual time=0.003..0.003 rows=1 loops=230)

  • Index Cond: ((id_int = ref_people.id_int) AND (date_histo IS NOT NULL))
  • Filter: is_active
138. 0.117 0.585 ↑ 1.0 1 117

Result (cost=6.23..6.24 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=117)

139.          

Initplan (forResult)

140. 0.117 0.468 ↑ 1.0 1 117

Limit (cost=0.28..6.23 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=117)

141. 0.351 0.351 ↑ 2.0 1 117

Index Scan using ref_qualification_xperf3 on ref_qualification ref_qualification_13 (cost=0.28..12.17 rows=2 width=8) (actual time=0.003..0.003 rows=1 loops=117)

  • Index Cond: ((id_int = ref_people.id_int) AND (date_histo IS NOT NULL))
  • Filter: is_active
142. 2.082 151.986 ↑ 1.0 1 347

Aggregate (cost=169.14..169.15 rows=1 width=8) (actual time=0.438..0.438 rows=1 loops=347)

143. 3.035 149.904 ↓ 4.0 4 347

Nested Loop (cost=0.85..169.14 rows=1 width=4) (actual time=0.048..0.432 rows=4 loops=347)

144. 3.817 3.817 ↓ 7.5 15 347

Index Only Scan using ref_genealogy_xperf3 on ref_genealogy (cost=0.42..8.47 rows=2 width=4) (actual time=0.008..0.011 rows=15 loops=347)

  • Index Cond: ((id_int = ref_people.id_int) AND (id_filiale = ref_people.id_filiale) AND (level >= 1) AND (level <= 5))
  • Heap Fetches: 0
145. 143.052 143.052 ↓ 0.0 0 5,109

Index Scan using ref_people_xperf01 on ref_people filleul (cost=0.42..80.32 rows=1 width=4) (actual time=0.022..0.028 rows=0 loops=5,109)

  • Index Cond: (id_int = ref_genealogy.id_int_level)
  • Filter: (is_active AND (id_statut <> 2) AND (id_type = 5))
  • Rows Removed by Filter: 16
146. 1.041 38.170 ↑ 1.0 1 347

Aggregate (cost=169.33..169.34 rows=1 width=8) (actual time=0.110..0.110 rows=1 loops=347)

147. 1.030 37.129 ↑ 1.0 1 347

Nested Loop (cost=0.85..169.33 rows=1 width=4) (actual time=0.032..0.107 rows=1 loops=347)

148. 1.735 1.735 ↓ 2.5 5 347

Index Only Scan using ref_genealogy_xperf3 on ref_genealogy ref_genealogy_1 (cost=0.42..8.66 rows=2 width=4) (actual time=0.004..0.005 rows=5 loops=347)

  • Index Cond: ((id_int = ref_people.id_int) AND (level = 1))
  • Heap Fetches: 0
149. 34.364 34.364 ↓ 0.0 0 1,562

Index Scan using ref_people_xperf01 on ref_people filleul_1 (cost=0.42..80.32 rows=1 width=4) (actual time=0.017..0.022 rows=0 loops=1,562)

  • Index Cond: (id_int = ref_genealogy_1.id_int_level)
  • Filter: (is_active AND (id_statut <> 2) AND (id_type = 5))
  • Rows Removed by Filter: 16
150. 0.694 38.170 ↑ 1.0 1 347

Aggregate (cost=169.33..169.34 rows=1 width=8) (actual time=0.110..0.110 rows=1 loops=347)

151. 1.634 37.476 ↑ 1.0 1 347

Nested Loop (cost=0.85..169.33 rows=1 width=4) (actual time=0.022..0.108 rows=1 loops=347)

152. 1.388 1.388 ↓ 2.0 4 347

Index Only Scan using ref_genealogy_xperf3 on ref_genealogy ref_genealogy_2 (cost=0.42..8.66 rows=2 width=4) (actual time=0.003..0.004 rows=4 loops=347)

  • Index Cond: ((id_int = ref_people.id_int) AND (level = 2))
  • Heap Fetches: 0
153. 34.454 34.454 ↓ 0.0 0 1,498

Index Scan using ref_people_xperf01 on ref_people filleul_2 (cost=0.42..80.32 rows=1 width=4) (actual time=0.018..0.023 rows=0 loops=1,498)

  • Index Cond: (id_int = ref_genealogy_2.id_int_level)
  • Filter: (is_active AND (id_statut <> 2) AND (id_type = 5))
  • Rows Removed by Filter: 16
154. 0.694 28.107 ↑ 1.0 1 347

Aggregate (cost=169.33..169.34 rows=1 width=8) (actual time=0.081..0.081 rows=1 loops=347)

155. 1.017 27.413 ↑ 1.0 1 347

Nested Loop (cost=0.85..169.33 rows=1 width=4) (actual time=0.014..0.079 rows=1 loops=347)

156. 1.388 1.388 ↓ 1.5 3 347

Index Only Scan using ref_genealogy_xperf3 on ref_genealogy ref_genealogy_3 (cost=0.42..8.66 rows=2 width=4) (actual time=0.003..0.004 rows=3 loops=347)

  • Index Cond: ((id_int = ref_people.id_int) AND (level = 3))
  • Heap Fetches: 0
157. 25.008 25.008 ↓ 0.0 0 1,042

Index Scan using ref_people_xperf01 on ref_people filleul_3 (cost=0.42..80.32 rows=1 width=4) (actual time=0.020..0.024 rows=0 loops=1,042)

  • Index Cond: (id_int = ref_genealogy_3.id_int_level)
  • Filter: (is_active AND (id_statut <> 2) AND (id_type = 5))
  • Rows Removed by Filter: 16
158. 0.694 19.432 ↑ 1.0 1 347

Aggregate (cost=88.97..88.98 rows=1 width=8) (actual time=0.056..0.056 rows=1 loops=347)

159. 0.346 18.738 ↓ 0.0 0 347

Nested Loop (cost=0.85..88.96 rows=1 width=4) (actual time=0.010..0.054 rows=0 loops=347)

160. 1.388 1.388 ↓ 2.0 2 347

Index Only Scan using ref_genealogy_xperf3 on ref_genealogy ref_genealogy_4 (cost=0.42..8.65 rows=1 width=4) (actual time=0.003..0.004 rows=2 loops=347)

  • Index Cond: ((id_int = ref_people.id_int) AND (level = 4))
  • Heap Fetches: 0
161. 17.004 17.004 ↓ 0.0 0 654

Index Scan using ref_people_xperf01 on ref_people filleul_4 (cost=0.42..80.30 rows=1 width=4) (actual time=0.021..0.026 rows=0 loops=654)

  • Index Cond: (id_int = ref_genealogy_4.id_int_level)
  • Filter: (is_active AND (id_statut <> 2) AND (id_type = 5))
  • Rows Removed by Filter: 16
162. 0.694 11.104 ↑ 1.0 1 347

Aggregate (cost=88.97..88.98 rows=1 width=8) (actual time=0.032..0.032 rows=1 loops=347)

163. 0.191 10.410 ↓ 0.0 0 347

Nested Loop (cost=0.85..88.96 rows=1 width=4) (actual time=0.007..0.030 rows=0 loops=347)

164. 1.041 1.041 ↑ 1.0 1 347

Index Only Scan using ref_genealogy_xperf3 on ref_genealogy ref_genealogy_5 (cost=0.42..8.65 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=347)

  • Index Cond: ((id_int = ref_people.id_int) AND (level = 5))
  • Heap Fetches: 0
165. 9.178 9.178 ↓ 0.0 0 353

Index Scan using ref_people_xperf01 on ref_people filleul_5 (cost=0.42..80.30 rows=1 width=4) (actual time=0.021..0.026 rows=0 loops=353)

  • Index Cond: (id_int = ref_genealogy_5.id_int_level)
  • Filter: (is_active AND (id_statut <> 2) AND (id_type = 5))
  • Rows Removed by Filter: 16
166. 0.694 337.284 ↑ 1.0 1 347

Aggregate (cost=0.10..0.10 rows=1 width=32) (actual time=0.972..0.972 rows=1 loops=347)

167. 336.590 336.590 ↑ 1.0 1 347

CTE Scan on fees_amount (cost=0.00..0.09 rows=1 width=32) (actual time=0.959..0.970 rows=1 loops=347)

  • Filter: (agent = ref_people.id_int)
  • Rows Removed by Filter: 305
Planning time : 18.149 ms