explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LC1K : Optimization for: PROD

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.269 1,019,382.473 ↓ 3.8 347 1

Unique (cost=184,381.23..184,386.91 rows=91 width=308) (actual time=1,019,382.183..1,019,382.473 rows=347 loops=1)

2.          

CTE niveau_rem

3. 0.005 0.005 ↑ 1.0 1 1

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

4.          

CTE fees_amount

5. 0.634 1,018,963.678 ↓ 76.5 306 1

GroupAggregate (cost=93,507.79..93,507.87 rows=4 width=36) (actual time=1,018,962.871..1,018,963.678 rows=306 loops=1)

  • Group Key: "*SELECT* 1".agent
6. 0.972 1,018,963.044 ↓ 587.0 2,348 1

Sort (cost=93,507.79..93,507.80 rows=4 width=36) (actual time=1,018,962.853..1,018,963.044 rows=2,348 loops=1)

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

Append (cost=11,333.92..93,507.75 rows=4 width=36) (actual time=119,859.350..1,018,962.072 rows=2,348 loops=1)

8. 0.051 119,859.512 ↓ 284.0 284 1

Subquery Scan on *SELECT* 1 (cost=11,333.92..11,333.95 rows=1 width=36) (actual time=119,859.348..119,859.512 rows=284 loops=1)

9. 0.096 119,859.461 ↓ 284.0 284 1

Unique (cost=11,333.92..11,333.94 rows=1 width=52) (actual time=119,859.346..119,859.461 rows=284 loops=1)

10. 1.282 119,859.365 ↓ 294.0 294 1

Sort (cost=11,333.92..11,333.92 rows=1 width=52) (actual time=119,859.344..119,859.365 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. 14.503 119,858.083 ↓ 294.0 294 1

Nested Loop (cost=10,021.05..11,333.91 rows=1 width=52) (actual time=652.779..119,858.083 rows=294 loops=1)

  • Join Filter: ((ref_transaction.id_transaction = ref_margin.id_transaction) AND (ref_transaction.id_int_agent_in = ref_margin.id_int_agent))
12. 14.535 83.195 ↓ 305.0 305 1

Merge Join (cost=49.87..1,122.23 rows=1 width=52) (actual time=22.722..83.195 rows=305 loops=1)

  • Merge Cond: (ref_transaction.id_transaction = ref_sub_transaction.id_transaction)
  • Join Filter: ((COALESCE((ref_sub_transaction.date_paid)::date, (ref_transaction.date_paid)::date) <= CURRENT_DATE) AND (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: 286
13. 3.445 66.819 ↓ 3.6 2,443 1

Nested Loop (cost=0.43..42,693.30 rows=679 width=40) (actual time=0.207..66.819 rows=2,443 loops=1)

14. 63.374 63.374 ↓ 3.6 2,443 1

Index Scan using ref_transaction_xperf14 on ref_transaction (cost=0.43..42,683.75 rows=679 width=32) (actual time=0.155..63.374 rows=2,443 loops=1)

  • Index Cond: (id_filiale = 3)
  • Filter: is_active
  • Rows Removed by Filter: 23881
15. 0.000 0.000 ↑ 1.0 1 2,443

Materialize (cost=0.00..1.07 rows=1 width=12) (actual time=0.000..0.000 rows=1 loops=2,443)

16. 0.028 0.028 ↑ 1.0 1 1

Seq Scan on ref_subsidiary ref_subsidiary_1 (cost=0.00..1.06 rows=1 width=12) (actual time=0.023..0.028 rows=1 loops=1)

  • Filter: (id_wis = 3)
  • Rows Removed by Filter: 4
17. 1.182 1.841 ↑ 1.0 591 1

Sort (cost=49.42..50.97 rows=618 width=20) (actual time=1.170..1.841 rows=591 loops=1)

  • Sort Key: ref_sub_transaction.id_transaction
  • Sort Method: quicksort Memory: 70kB
18. 0.659 0.659 ↑ 1.0 591 1

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

  • Filter: (id_filiale = 3)
  • Rows Removed by Filter: 3
19. 115,796.300 119,760.385 ↑ 1.0 1 305

Bitmap Heap Scan on ref_margin (cost=9,971.18..10,211.63 rows=1 width=22) (actual time=346.566..392.657 rows=1 loops=305)

  • Recheck Cond: ((id_filiale = 3) AND ((type_margin)::text = 'IN'::text) AND ((ss_type_margin)::text = 'Agent'::text) AND (id_transaction = ref_sub_transaction.id_transaction))
  • Rows Removed by Index Recheck: 1021066
  • Filter: ((id_ss_transaction IS NOT NULL) AND (ref_sub_transaction.id_ss_transaction = id_ss_transaction))
  • Rows Removed by Filter: 1
  • Heap Blocks: lossy=18873683
20. 577.975 3,964.085 ↓ 0.0 0 305

BitmapAnd (cost=9,971.18..9,971.18 rows=83 width=0) (actual time=12.997..12.997 rows=0 loops=305)

21. 802.455 802.455 ↓ 39.8 619,520 305

Bitmap Index Scan on ref_margin_xperf1 (cost=0.00..16.23 rows=15,567 width=0) (actual time=2.631..2.631 rows=619,520 loops=305)

  • Index Cond: (id_filiale = 3)
22. 916.220 916.220 ↑ 1.7 668,160 305

Bitmap Index Scan on ref_margin_xperf5 (cost=0.00..32.77 rows=1,158,535 width=0) (actual time=3.004..3.004 rows=668,160 loops=305)

  • Index Cond: ((type_margin)::text = 'IN'::text)
23. 841.800 841.800 ↑ 1.5 628,480 305

Bitmap Index Scan on ref_margin_xperf6 (cost=0.00..33.31 rows=919,967 width=0) (actual time=2.760..2.760 rows=628,480 loops=305)

  • Index Cond: ((ss_type_margin)::text = 'Agent'::text)
24. 825.635 825.635 ↓ 73.2 638,393 305

Bitmap Index Scan on ref_margin_xperf2 (cost=0.00..9,888.13 rows=8,717 width=0) (actual time=2.707..2.707 rows=638,393 loops=305)

  • Index Cond: (id_transaction = ref_sub_transaction.id_transaction)
25. 0.054 127,822.844 ↓ 284.0 284 1

Subquery Scan on *SELECT* 2 (cost=11,333.59..11,333.62 rows=1 width=36) (actual time=127,822.666..127,822.844 rows=284 loops=1)

26. 0.112 127,822.790 ↓ 284.0 284 1

Unique (cost=11,333.59..11,333.61 rows=1 width=52) (actual time=127,822.664..127,822.790 rows=284 loops=1)

27. 1.251 127,822.678 ↓ 294.0 294 1

Sort (cost=11,333.59..11,333.59 rows=1 width=52) (actual time=127,822.656..127,822.678 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
28. 14.530 127,821.427 ↓ 294.0 294 1

Nested Loop (cost=10,020.75..11,333.58 rows=1 width=52) (actual time=434.786..127,821.427 rows=294 loops=1)

  • Join Filter: ((ref_transaction_1.id_transaction = ref_margin_1.id_transaction) AND (ref_transaction_1.id_int_agent_out = ref_margin_1.id_int_agent))
29. 12.764 56.122 ↓ 305.0 305 1

Merge Join (cost=49.87..1,122.23 rows=1 width=52) (actual time=7.807..56.122 rows=305 loops=1)

  • Merge Cond: (ref_transaction_1.id_transaction = ref_sub_transaction_1.id_transaction)
  • Join Filter: ((COALESCE((ref_sub_transaction_1.date_paid)::date, (ref_transaction_1.date_paid)::date) <= CURRENT_DATE) AND (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: 286
30. 2.356 42.172 ↓ 3.6 2,443 1

Nested Loop (cost=0.43..42,693.30 rows=679 width=40) (actual time=0.090..42.172 rows=2,443 loops=1)

31. 39.816 39.816 ↓ 3.6 2,443 1

Index Scan using ref_transaction_xperf14 on ref_transaction ref_transaction_1 (cost=0.43..42,683.75 rows=679 width=32) (actual time=0.051..39.816 rows=2,443 loops=1)

  • Index Cond: (id_filiale = 3)
  • Filter: is_active
  • Rows Removed by Filter: 23881
32. 0.000 0.000 ↑ 1.0 1 2,443

Materialize (cost=0.00..1.07 rows=1 width=12) (actual time=0.000..0.000 rows=1 loops=2,443)

33. 0.025 0.025 ↑ 1.0 1 1

Seq Scan on ref_subsidiary ref_subsidiary_2 (cost=0.00..1.06 rows=1 width=12) (actual time=0.024..0.025 rows=1 loops=1)

  • Filter: (id_wis = 3)
  • Rows Removed by Filter: 4
34. 0.903 1.186 ↑ 1.0 591 1

Sort (cost=49.42..50.97 rows=618 width=20) (actual time=0.526..1.186 rows=591 loops=1)

  • Sort Key: ref_sub_transaction_1.id_transaction
  • Sort Method: quicksort Memory: 70kB
35. 0.283 0.283 ↑ 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.020..0.283 rows=591 loops=1)

  • Filter: (id_filiale = 3)
  • Rows Removed by Filter: 3
36. 123,872.090 127,750.775 ↑ 1.0 1 305

Bitmap Heap Scan on ref_margin ref_margin_1 (cost=9,970.88..10,211.31 rows=1 width=22) (actual time=361.069..418.855 rows=1 loops=305)

  • Recheck Cond: ((id_filiale = 3) AND ((type_margin)::text = 'OUT'::text) AND ((ss_type_margin)::text = 'Agent'::text) AND (id_transaction = ref_sub_transaction_1.id_transaction))
  • Rows Removed by Index Recheck: 1021066
  • Filter: ((id_ss_transaction IS NOT NULL) AND (ref_sub_transaction_1.id_ss_transaction = id_ss_transaction))
  • Rows Removed by Filter: 1
  • Heap Blocks: lossy=18873683
37. 569.130 3,878.685 ↓ 0.0 0 305

BitmapAnd (cost=9,970.88..9,970.88 rows=82 width=0) (actual time=12.717..12.717 rows=0 loops=305)

38. 790.865 790.865 ↓ 39.8 619,520 305

Bitmap Index Scan on ref_margin_xperf1 (cost=0.00..16.23 rows=15,567 width=0) (actual time=2.593..2.593 rows=619,520 loops=305)

  • Index Cond: (id_filiale = 3)
39. 876.875 876.875 ↑ 1.7 665,600 305

Bitmap Index Scan on ref_margin_xperf5 (cost=0.00..32.46 rows=1,137,471 width=0) (actual time=2.875..2.875 rows=665,600 loops=305)

  • Index Cond: ((type_margin)::text = 'OUT'::text)
40. 828.990 828.990 ↑ 1.5 628,480 305

Bitmap Index Scan on ref_margin_xperf6 (cost=0.00..33.31 rows=919,967 width=0) (actual time=2.718..2.718 rows=628,480 loops=305)

  • Index Cond: ((ss_type_margin)::text = 'Agent'::text)
41. 812.825 812.825 ↓ 73.2 638,393 305

Bitmap Index Scan on ref_margin_xperf2 (cost=0.00..9,888.13 rows=8,717 width=0) (actual time=2.665..2.665 rows=638,393 loops=305)

  • Index Cond: (id_transaction = ref_sub_transaction_1.id_transaction)
42. 0.163 381,908.449 ↓ 890.0 890 1

Subquery Scan on *SELECT* 3 (cost=35,621.66..35,621.69 rows=1 width=36) (actual time=381,907.902..381,908.449 rows=890 loops=1)

43. 0.301 381,908.286 ↓ 890.0 890 1

Unique (cost=35,621.66..35,621.68 rows=1 width=52) (actual time=381,907.899..381,908.286 rows=890 loops=1)

44. 2.890 381,907.985 ↓ 890.0 890 1

Sort (cost=35,621.66..35,621.66 rows=1 width=52) (actual time=381,907.897..381,907.985 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
45. 28.268 381,905.095 ↓ 890.0 890 1

Hash Join (cost=5,195.03..35,621.65 rows=1 width=52) (actual time=522.624..381,905.095 rows=890 loops=1)

  • Hash Cond: ((ref_margin_2.id_transaction = ref_transaction_2.id_transaction) AND (ref_margin_2.id_int_agent = ref_transaction_2.id_int_agent_in))
46. 407.505 417.245 ↓ 1.5 1,855 1

Bitmap Heap Scan on ref_margin ref_margin_2 (cost=83.76..30,258.28 rows=1,269 width=22) (actual time=15.088..417.245 rows=1,855 loops=1)

  • Recheck Cond: ((id_filiale = 3) AND ((type_margin)::text = 'IN'::text) AND ((ss_type_margin)::text = 'Agent'::text))
  • Rows Removed by Index Recheck: 1018792
  • Filter: (id_ss_transaction IS NULL)
  • Rows Removed by Filter: 421
  • Heap Blocks: lossy=61891
47. 1.217 9.740 ↓ 0.0 0 1

BitmapAnd (cost=83.76..83.76 rows=11,467 width=0) (actual time=9.740..9.740 rows=0 loops=1)

48. 2.788 2.788 ↓ 39.8 619,520 1

Bitmap Index Scan on ref_margin_xperf1 (cost=0.00..16.23 rows=15,567 width=0) (actual time=2.788..2.788 rows=619,520 loops=1)

  • Index Cond: (id_filiale = 3)
49. 2.994 2.994 ↑ 1.7 668,160 1

Bitmap Index Scan on ref_margin_xperf5 (cost=0.00..32.77 rows=1,158,535 width=0) (actual time=2.994..2.994 rows=668,160 loops=1)

  • Index Cond: ((type_margin)::text = 'IN'::text)
50. 2.741 2.741 ↑ 1.5 628,480 1

Bitmap Index Scan on ref_margin_xperf6 (cost=0.00..33.31 rows=919,967 width=0) (actual time=2.741..2.741 rows=628,480 loops=1)

  • Index Cond: ((ss_type_margin)::text = 'Agent'::text)
51. 0.555 78.562 ↓ 11.9 890 1

Hash (cost=5,110.15..5,110.15 rows=75 width=32) (actual time=78.561..78.562 rows=890 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 68kB
52. 6.002 78.007 ↓ 11.9 890 1

Nested Loop (cost=2,423.15..5,110.15 rows=75 width=32) (actual time=26.087..78.007 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
53. 0.021 0.021 ↑ 1.0 1 1

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

  • Filter: (id_wis = 3)
  • Rows Removed by Filter: 4
54. 53.597 71.984 ↓ 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=23.079..71.984 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
55. 1.988 18.387 ↓ 0.0 0 1

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

56. 2.475 2.475 ↓ 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.475..2.475 rows=492,800 loops=1)

  • Index Cond: (id_filiale = 3)
57. 13.924 13.924 ↓ 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=13.924..13.924 rows=133,788 loops=1)

58.          

SubPlan (forHash Join)

59. 33.820 381,381.020 ↑ 1.0 1 890

Aggregate (cost=198.10..198.13 rows=1 width=32) (actual time=428.517..428.518 rows=1 loops=890)

60. 376,251.950 381,347.200 ↓ 0.0 0 890

Bitmap Heap Scan on ref_margin a (cost=32.46..198.10 rows=1 width=6) (actual time=413.479..428.480 rows=0 loops=890)

  • Recheck Cond: ((id_int_agent = ref_transaction_2.id_int_agent_in) AND (id_transaction = ref_margin_2.id_transaction))
  • Rows Removed by Index Recheck: 985531
  • Filter: ((id_ss_transaction IS NOT NULL) AND ((type_margin)::text = 'IN'::text))
  • Rows Removed by Filter: 2
  • Heap Blocks: lossy=52380910
61. 537.560 5,095.250 ↓ 0.0 0 890

BitmapAnd (cost=32.46..32.46 rows=42 width=0) (actual time=5.725..5.725 rows=0 loops=890)

62. 2,164.480 2,164.480 ↓ 102.3 590,614 890

Bitmap Index Scan on ref_margin_xperf7 (cost=0.00..16.08 rows=5,772 width=0) (actual time=2.432..2.432 rows=590,614 loops=890)

  • Index Cond: (id_int_agent = ref_transaction_2.id_int_agent_in)
63. 2,393.210 2,393.210 ↓ 73.2 638,293 890

Bitmap Index Scan on ref_margin_xperf2 (cost=0.00..16.13 rows=8,717 width=0) (actual time=2.689..2.689 rows=638,293 loops=890)

  • Index Cond: (id_transaction = ref_margin_2.id_transaction)
64. 0.171 389,371.049 ↓ 890.0 890 1

Subquery Scan on *SELECT* 4 (cost=35,218.45..35,218.48 rows=1 width=36) (actual time=389,370.471..389,371.049 rows=890 loops=1)

65. 0.309 389,370.878 ↓ 890.0 890 1

Unique (cost=35,218.45..35,218.47 rows=1 width=52) (actual time=389,370.463..389,370.878 rows=890 loops=1)

66. 3.266 389,370.569 ↓ 890.0 890 1

Sort (cost=35,218.45..35,218.45 rows=1 width=52) (actual time=389,370.461..389,370.569 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
67. 27.920 389,367.303 ↓ 890.0 890 1

Hash Join (cost=5,194.71..35,218.44 rows=1 width=52) (actual time=532.341..389,367.303 rows=890 loops=1)

  • Hash Cond: ((ref_margin_3.id_transaction = ref_transaction_3.id_transaction) AND (ref_margin_3.id_int_agent = ref_transaction_3.id_int_agent_out))
68. 416.108 425.628 ↓ 1.5 1,855 1

Bitmap Heap Scan on ref_margin ref_margin_3 (cost=83.44..29,855.83 rows=1,251 width=22) (actual time=14.685..425.628 rows=1,855 loops=1)

  • Recheck Cond: ((id_filiale = 3) AND ((type_margin)::text = 'OUT'::text) AND ((ss_type_margin)::text = 'Agent'::text))
  • Rows Removed by Index Recheck: 1018792
  • Filter: (id_ss_transaction IS NULL)
  • Rows Removed by Filter: 421
  • Heap Blocks: lossy=61891
69. 1.231 9.520 ↓ 0.0 0 1

BitmapAnd (cost=83.44..83.44 rows=11,259 width=0) (actual time=9.520..9.520 rows=0 loops=1)

70. 2.649 2.649 ↓ 39.8 619,520 1

Bitmap Index Scan on ref_margin_xperf1 (cost=0.00..16.23 rows=15,567 width=0) (actual time=2.649..2.649 rows=619,520 loops=1)

  • Index Cond: (id_filiale = 3)
71. 2.922 2.922 ↑ 1.7 665,600 1

Bitmap Index Scan on ref_margin_xperf5 (cost=0.00..32.46 rows=1,137,471 width=0) (actual time=2.922..2.922 rows=665,600 loops=1)

  • Index Cond: ((type_margin)::text = 'OUT'::text)
72. 2.718 2.718 ↑ 1.5 628,480 1

Bitmap Index Scan on ref_margin_xperf6 (cost=0.00..33.31 rows=919,967 width=0) (actual time=2.718..2.718 rows=628,480 loops=1)

  • Index Cond: ((ss_type_margin)::text = 'Agent'::text)
73. 0.516 70.975 ↓ 11.9 890 1

Hash (cost=5,110.15..5,110.15 rows=75 width=32) (actual time=70.974..70.975 rows=890 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 68kB
74. 6.077 70.459 ↓ 11.9 890 1

Nested Loop (cost=2,423.15..5,110.15 rows=75 width=32) (actual time=21.060..70.459 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
75. 0.025 0.025 ↑ 1.0 1 1

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

  • Filter: (id_wis = 3)
  • Rows Removed by Filter: 4
76. 49.765 64.357 ↓ 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=18.504..64.357 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
77. 1.950 14.592 ↓ 0.0 0 1

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

78. 2.270 2.270 ↓ 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.270..2.270 rows=492,800 loops=1)

  • Index Cond: (id_filiale = 3)
79. 10.372 10.372 ↓ 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=10.371..10.372 rows=133,788 loops=1)

80.          

SubPlan (forHash Join)

81. 33.820 388,842.780 ↑ 1.0 1 890

Aggregate (cost=198.10..198.13 rows=1 width=32) (actual time=436.902..436.902 rows=1 loops=890)

82. 383,756.430 388,808.960 ↓ 0.0 0 890

Bitmap Heap Scan on ref_margin a_1 (cost=32.46..198.10 rows=1 width=6) (actual time=421.356..436.864 rows=0 loops=890)

  • Recheck Cond: ((id_int_agent = ref_transaction_3.id_int_agent_out) AND (id_transaction = ref_margin_3.id_transaction))
  • Rows Removed by Index Recheck: 976268
  • Filter: ((id_ss_transaction IS NOT NULL) AND ((type_margin)::text = 'OUT'::text))
  • Rows Removed by Filter: 2
  • Heap Blocks: lossy=51761774
83. 528.660 5,052.530 ↓ 0.0 0 890

BitmapAnd (cost=32.46..32.46 rows=42 width=0) (actual time=5.677..5.677 rows=0 loops=890)

84. 2,136.000 2,136.000 ↓ 101.1 583,510 890

Bitmap Index Scan on ref_margin_xperf7 (cost=0.00..16.08 rows=5,772 width=0) (actual time=2.400..2.400 rows=583,510 loops=890)

  • Index Cond: (id_int_agent = ref_transaction_3.id_int_agent_out)
85. 2,387.870 2,387.870 ↓ 73.2 638,293 890

Bitmap Index Scan on ref_margin_xperf2 (cost=0.00..16.13 rows=8,717 width=0) (actual time=2.683..2.683 rows=638,293 loops=890)

  • Index Cond: (id_transaction = ref_margin_3.id_transaction)
86. 1.528 1,019,382.204 ↓ 3.8 347 1

Sort (cost=90,873.35..90,873.58 rows=91 width=308) (actual time=1,019,382.181..1,019,382.204 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
87. 29.128 1,019,380.676 ↓ 3.8 347 1

Nested Loop (cost=1,757.41..90,870.39 rows=91 width=308) (actual time=1,018,999.999..1,019,380.676 rows=347 loops=1)

88. 0.013 0.073 ↑ 1.0 1 1

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

89. 0.013 0.013 ↑ 1.0 1 1

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

90. 0.047 0.047 ↑ 1.0 1 1

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

  • Filter: (id_wis = 3)
  • Rows Removed by Filter: 4
91. 4.017 32.090 ↓ 3.8 347 1

Bitmap Heap Scan on ref_people (cost=1,757.41..5,011.57 rows=91 width=96) (actual time=28.605..32.090 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
92. 1.766 28.073 ↓ 0.0 0 1

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

93. 17.774 17.774 ↑ 1.0 41,521 1

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

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

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

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

SubPlan (forNested Loop)

96. 0.347 3.470 ↑ 1.0 1 347

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

97.          

Initplan (forResult)

98. 0.347 3.123 ↑ 1.0 1 347

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

99. 2.776 2.776 ↑ 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.008..0.008 rows=1 loops=347)

  • Index Cond: ((id_int = ref_people.id_int) AND (date_histo IS NOT NULL))
  • Filter: is_active
100. 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)

101.          

Initplan (forResult)

102. 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)

103. 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
104. 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)

105.          

Initplan (forResult)

106. 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)

107. 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.003..0.003 rows=1 loops=347)

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

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

109.          

Initplan (forResult)

110. 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)

111. 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
112. 0.347 2.082 ↑ 1.0 1 347

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

113.          

Initplan (forResult)

114. 0.347 1.735 ↑ 1.0 1 347

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

115. 1.388 1.388 ↑ 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.004..0.004 rows=1 loops=347)

  • Index Cond: ((id_int = ref_people.id_int) AND (date_histo IS NOT NULL))
  • Filter: is_active
116. 0.000 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)

117.          

Initplan (forResult)

118. 0.694 1.735 ↑ 1.0 1 347

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

119. 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
120. 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)

121.          

Initplan (forResult)

122. 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)

123. 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
124. 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)

125.          

Initplan (forResult)

126. 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)

127. 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
128. 0.000 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)

129.          

Initplan (forResult)

130. 0.347 1.388 ↑ 1.0 1 347

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

131. 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
132. 0.000 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)

133.          

Initplan (forResult)

134. 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)

135. 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
136. 0.295 1.770 ↑ 1.0 1 295

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

137.          

Initplan (forResult)

138. 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)

139. 1.180 1.180 ↑ 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.004..0.004 rows=1 loops=295)

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

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

141.          

Initplan (forResult)

142. 0.230 1.150 ↑ 1.0 1 230

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

143. 0.920 0.920 ↑ 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.004..0.004 rows=1 loops=230)

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

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

145.          

Initplan (forResult)

146. 0.460 1.150 ↑ 1.0 1 230

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

147. 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
148. 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)

149.          

Initplan (forResult)

150. 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)

151. 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
152. 2.776 183.216 ↑ 1.0 1 347

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

153. 6.291 180.440 ↓ 4.0 4 347

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

154. 5.552 5.552 ↓ 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.011..0.016 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
155. 168.597 168.597 ↓ 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.026..0.033 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
156. 1.041 39.905 ↑ 1.0 1 347

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

157. 0.856 38.864 ↑ 1.0 1 347

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

158. 2.082 2.082 ↓ 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.005..0.006 rows=5 loops=347)

  • Index Cond: ((id_int = ref_people.id_int) AND (level = 1))
  • Heap Fetches: 0
159. 35.926 35.926 ↓ 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.023 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
160. 1.041 38.864 ↑ 1.0 1 347

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

161. 1.634 37.823 ↑ 1.0 1 347

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

162. 1.735 1.735 ↓ 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.004..0.005 rows=4 loops=347)

  • Index Cond: ((id_int = ref_people.id_int) AND (level = 2))
  • Heap Fetches: 0
163. 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
164. 0.694 28.801 ↑ 1.0 1 347

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

165. 0.669 28.107 ↑ 1.0 1 347

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

166. 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
167. 26.050 26.050 ↓ 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.025 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
168. 0.694 19.085 ↑ 1.0 1 347

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

169. 0.653 18.391 ↓ 0.0 0 347

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

170. 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
171. 16.350 16.350 ↓ 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.020..0.025 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
172. 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)

173. 0.544 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)

174. 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
175. 8.825 8.825 ↓ 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.025 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
176. 0.694 1,018,974.869 ↑ 1.0 1 347

Aggregate (cost=0.10..0.10 rows=1 width=32) (actual time=2,936.527..2,936.527 rows=1 loops=347)

177. 1,018,974.175 1,018,974.175 ↑ 1.0 1 347

CTE Scan on fees_amount (cost=0.00..0.09 rows=1 width=32) (actual time=2,936.514..2,936.525 rows=1 loops=347)

  • Filter: (agent = ref_people.id_int)
  • Rows Removed by Filter: 305