explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AhFX

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=453,454.64..453,454.64 rows=1 width=4,728) (actual rows= loops=)

  • Sort Key: tender_lots.tender_lot_id, bidderques0_.questionid
  • Sort Key: tender_lots.tender_lot_id, bidderques0_.questionid
  • Sort Method: quicksort Memory: 314kB
2. 0.000 0.000 ↓ 0.0

Nested Loop (cost=4,295.96..453,454.63 rows=1 width=4,728) (actual rows= loops=)

  • Join Filter: (users_1.company_id = companies.company_id)
3. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=4.10..1,877.87 rows=1 width=3,916) (actual rows= loops=)

  • Filter: (answerses5_.answerid IS NULL)
4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.82..1,810.30 rows=87 width=3,662) (actual rows= loops=)

  • Join Filter: (tender_lots.tender_lot_id = tender_lot_data.tender_lot_id)
5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.53..1,707.05 rows=80 width=2,626) (actual rows= loops=)

6. 645,001.957 645,001.957 ↓ 73.0 73 1

Nested Loop (cost=3.11..1630.80 ro"Sort (cost=453,454.64..453,454.64 rows=1 width=4,728) (actual time=645,001.874..645,001.957 rows=73 loops=1)

7. 4,195.839 645,000.399 ↓ 73.0 73 1

Nested Loop (cost=4,295.96..453,454.63 rows=1 width=4,728) (actual time=6,429.394..645,000.399 rows=73 loops=1)

  • Join Filter: (users_1.company_id = companies.company_id)
  • Rows Removed by Join Filter: 4,031,133
8. 1.589 62.244 ↓ 73.0 73 1

Nested Loop Left Join (cost=4.10..1,877.87 rows=1 width=3,916) (actual time=0.251..62.244 rows=73 loops=1)

  • Filter: (answerses5_.answerid IS NULL)
  • Rows Removed by Filter: 436
9. 1.937 58.691 ↓ 5.6 491 1

Nested Loop (cost=3.82..1,810.30 rows=87 width=3,662) (actual time=0.115..58.691 rows=491 loops=1)

  • Join Filter: (tender_lots.tender_lot_id = tender_lot_data.tender_lot_id)
10. 2.089 54.790 ↓ 6.1 491 1

Nested Loop (cost=3.53..1,707.05 rows=80 width=2,626) (actual time=0.105..54.790 rows=491 loops=1)

11. 1.646 50.737 ↓ 6.1 491 1

Nested Loop (cost=3.11..1,630.80 rows=80 width=2,301) (actual time=0.097..50.737 rows=491 loops=1)

12. 1.942 47.127 ↓ 6.1 491 1

Nested Loop (cost=2.82..1,567.79 rows=80 width=2,284) (actual time=0.089..47.127 rows=491 loops=1)

13. 3.744 43.712 ↓ 6.1 491 1

Nested Loop (cost=2.53..1,505.86 rows=80 width=2,280) (actual time=0.081..43.712 rows=491 loops=1)

  • Join Filter: (bidderques0_.statuslid = bidderques1_.lookupid)
  • Rows Removed by Join Filter: 1,473
14. 5.004 37.513 ↓ 6.1 491 1

Nested Loop (cost=1.44..1,499.91 rows=80 width=966) (actual time=0.041..37.513 rows=491 loops=1)

15. 9.884 28.946 ↓ 5.9 509 1

Nested Loop (cost=0.70..1,340.76 rows=86 width=961) (actual time=0.021..28.946 rows=509 loops=1)

16. 10.540 10.540 ↓ 7.4 4,261 1

Index Scan using tender_lot_search_index_lot_state on tender_lots (cost=0.42..548.51 rows=572 width=555) (actual time=0.012..10.540 rows=4,261 loops=1)

  • Index Cond: (tender_lot_state_id = 2)
17. 8.522 8.522 ↓ 0.0 0 4,261

Index Scan using bidder_q_on_item on bidder_questions bidderques0_ (cost=0.29..1.37 rows=2 width=406) (actual time=0.002..0.002 rows=0 loops=4,261)

  • Index Cond: (itemid = tender_lots.tender_lot_id)
  • Filter: (statuslid = ANY ('{0,217}'::integer[]))
  • Rows Removed by Filter: 0
18. 2.036 3.563 ↑ 1.0 1 509

Bitmap Heap Scan on tenders_new (cost=0.74..1.85 rows=1 width=9) (actual time=0.007..0.007 rows=1 loops=509)

  • Recheck Cond: (tender_id = tender_lots.tender_id)
  • Filter: tender_is_active
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=509
19. 1.527 1.527 ↑ 1.0 1 509

Bitmap Index Scan on tenders_pkey (cost=0.00..0.74 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=509)

  • Index Cond: (tender_id = tender_lots.tender_id)
20. 2.415 2.455 ↑ 1.0 4 491

Materialize (cost=1.09..1.16 rows=4 width=1,314) (actual time=0.001..0.005 rows=4 loops=491)

21. 0.011 0.040 ↑ 1.0 4 1

Subquery Scan on bidderques1_ (cost=1.09..1.14 rows=4 width=1,314) (actual time=0.027..0.040 rows=4 loops=1)

22. 0.014 0.029 ↑ 1.0 4 1

Sort (cost=1.09..1.10 rows=4 width=1,385) (actual time=0.024..0.029 rows=4 loops=1)

  • Sort Key: bid_question_states.bid_question_state_order
  • Sort Method: quicksort Memory: 25kB
23. 0.015 0.015 ↑ 1.0 4 1

Seq Scan on bid_question_states (cost=0.00..1.05 rows=4 width=1,385) (actual time=0.010..0.015 rows=4 loops=1)

24. 1.473 1.473 ↑ 1.0 1 491

Index Scan using users_pkey on users (cost=0.29..0.77 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=491)

  • Index Cond: (user_id = tenders_new.user_id)
25. 1.964 1.964 ↑ 1.0 1 491

Index Scan using users_person_id_unique on users users_1 (cost=0.29..0.79 rows=1 width=17) (actual time=0.004..0.004 rows=1 loops=491)

  • Index Cond: (person_id = bidderques0_.employeeid)
26. 1.964 1.964 ↑ 1.0 1 491

Index Scan using persons_pkey on persons (cost=0.41..0.95 rows=1 width=329) (actual time=0.004..0.004 rows=1 loops=491)

  • Index Cond: (person_id = users_1.person_id)
27. 1.964 1.964 ↑ 1.0 1 491

Index Scan using tender_lot_data_tender_lot_id_idx on tender_lot_data (cost=0.29..1.28 rows=1 width=1,040) (actual time=0.004..0.004 rows=1 loops=491)

  • Index Cond: (tender_lot_id = bidderques0_.itemid)
28. 1.964 1.964 ↑ 1.0 1 491

Index Scan using bga_on_q_id on bidder_questions_answers answerses5_ (cost=0.29..0.77 rows=1 width=254) (actual time=0.003..0.004 rows=1 loops=491)

  • Index Cond: (bidderques0_.questionid = questionid)
  • Filter: isactive
29. 12,811.427 640,742.316 ↑ 3.1 55,222 73

Nested Loop Left Join (cost=4,291.85..449,428.11 rows=171,885 width=1,070) (actual time=2.104..8,777.292 rows=55,222 loops=73)

30. 16,605.310 535,213.151 ↑ 1.0 55,222 73

Nested Loop Left Join (cost=4,288.93..275,251.31 rows=57,295 width=1,058) (actual time=2.076..7,331.687 rows=55,222 loops=73)

31. 10,190.620 14,707.091 ↑ 1.0 55,222 73

Hash Join (cost=4,288.50..6,942.86 rows=57,295 width=520) (actual time=1.879..201.467 rows=55,222 loops=73)

  • Hash Cond: (companies_data.company_id = companies.company_id)
32. 4,380.219 4,380.219 ↑ 1.0 55,222 73

Seq Scan on companies_data (cost=0.00..2,503.95 rows=57,295 width=329) (actual time=0.003..60.003 rows=55,222 loops=73)

33. 75.014 136.252 ↑ 1.0 55,222 1

Hash (cost=3,598.22..3,598.22 rows=55,222 width=191) (actual time=136.251..136.252 rows=55,222 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 12,909kB
34. 61.238 61.238 ↑ 1.0 55,222 1

Seq Scan on companies (cost=0.00..3,598.22 rows=55,222 width=191) (actual time=0.015..61.238 rows=55,222 loops=1)

35. 28,218.442 503,900.750 ↓ 0.0 0 4,031,206

Limit (cost=0.44..4.66 rows=1 width=538) (actual time=0.125..0.125 rows=0 loops=4,031,206)

36. 253,049.098 475,682.308 ↓ 0.0 0 4,031,206

Nested Loop (cost=0.44..13.11 rows=3 width=538) (actual time=0.118..0.118 rows=0 loops=4,031,206)

  • Join Filter: (comp_address.countryid = utl_countries.countryid)
  • Rows Removed by Join Filter: 0
37. 112,873.768 112,873.768 ↑ 1.6 27 4,031,206

Seq Scan on utl_countries (cost=0.00..1.44 rows=44 width=520) (actual time=0.002..0.028 rows=27 loops=4,031,206)

38. 85,572.206 109,759.442 ↓ 0.0 0 109,759,442

Materialize (cost=0.44..9.70 rows=3 width=26) (actual time=0.001..0.001 rows=0 loops=109,759,442)

39. 12,831.794 24,187.236 ↓ 0.0 0 4,031,206

Nested Loop (cost=0.44..9.69 rows=3 width=26) (actual time=0.006..0.006 rows=0 loops=4,031,206)

40. 8,062.412 8,062.412 ↓ 0.0 0 4,031,206

Index Scan using comp_address_compid_iswithdrawn_idx on comp_address (cost=0.29..3.68 rows=3 width=8) (actual time=0.002..0.002 rows=0 loops=4,031,206)

  • Index Cond: ((compid = companies.company_id) AND (iswithdrawn = false))
  • Filter: (NOT iswithdrawn)
41. 3,293.030 3,293.030 ↑ 1.0 1 1,646,515

Index Scan using utl_cities_pkey on utl_cities (cost=0.15..2.00 rows=1 width=26) (actual time=0.002..0.002 rows=1 loops=1,646,515)

  • Index Cond: (cityid = comp_address.cityid)
42. 12,093.618 92,717.738 ↓ 0.0 0 4,031,206

HashAggregate (cost=2.92..2.98 rows=3 width=44) (actual time=0.023..0.023 rows=0 loops=4,031,206)

  • Group Key: sum((count(*))) OVER (?)
43. 8,062.412 80,624.120 ↓ 0.0 0 4,031,206

WindowAgg (cost=2.74..2.81 rows=3 width=44) (actual time=0.020..0.020 rows=0 loops=4,031,206)

44. 32,249.648 72,561.708 ↓ 0.0 0 4,031,206

HashAggregate (cost=2.74..2.77 rows=3 width=12) (actual time=0.018..0.018 rows=0 loops=4,031,206)

  • Group Key: ratings2.value
45. 16,124.824 40,312.060 ↓ 0.0 0 4,031,206

Nested Loop (cost=0.41..2.70 rows=5 width=4) (actual time=0.009..0.010 rows=0 loops=4,031,206)

46. 12,093.618 12,093.618 ↑ 1.0 1 4,031,206

Seq Scan on rating_statuss (cost=0.00..1.04 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=4,031,206)

  • Filter: ((rating_status_constant)::text = 'RATE_MARK_SET'::text)
  • Rows Removed by Filter: 2
47. 12,093.618 12,093.618 ↓ 0.0 0 4,031,206

Index Only Scan using ix_ratings2 on ratings2 (cost=0.41..1.62 rows=5 width=8) (actual time=0.002..0.003 rows=0 loops=4,031,206)

  • Index Cond: ((objectcompid = companies.company_id) AND (ratingstatuslid = rating_statuss.rating_status_id))
  • Heap Fetches: 0
48. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.82..1,567.79 rows=80 width=2,284) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.53..1,505.86 rows=80 width=2,280) (actual rows= loops=)

  • Join Filter: (bidderques0_.statuslid = bidderques1_.lookupid)
50. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.44..1,499.91 rows=80 width=966) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.70..1,340.76 rows=86 width=961) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Index Scan using tender_lot_search_index_lot_state on tender_lots (cost=0.42..548.51 rows=572 width=555) (actual rows= loops=)

  • Index Cond: (tender_lot_state_id = 2)
53. 0.000 0.000 ↓ 0.0

Index Scan using bidder_q_on_item on bidder_questions bidderques0_ (cost=0.29..1.37 rows=2 width=406) (actual rows= loops=)

  • Index Cond: (itemid = tender_lots.tender_lot_id)
  • Filter: (statuslid = ANY ('{0,217}'::integer[]))
54. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on tenders_new (cost=0.74..1.85 rows=1 width=9) (actual rows= loops=)

  • Recheck Cond: (tender_id = tender_lots.tender_id)
  • Filter: tender_is_active
55. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on tenders_pkey (cost=0.00..0.74 rows=1 width=0) (actual rows= loops=)

  • Index Cond: (tender_id = tender_lots.tender_id)
56. 0.000 0.000 ↓ 0.0

Materialize (cost=1.09..1.16 rows=4 width=1,314) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Subquery Scan on bidderques1_ (cost=1.09..1.14 rows=4 width=1,314) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Sort (cost=1.09..1.10 rows=4 width=1,385) (actual rows= loops=)

  • Sort Key: bid_question_states.bid_question_state_order
59. 0.000 0.000 ↓ 0.0

Seq Scan on bid_question_states (cost=0.00..1.05 rows=4 width=1,385) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Index Scan using users_pkey on users (cost=0.29..0.77 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (user_id = tenders_new.user_id)
61. 0.000 0.000 ↓ 0.0

Index Scan using users_person_id_unique on users users_1 (cost=0.29..0.79 rows=1 width=17) (actual rows= loops=)

  • Index Cond: (person_id = bidderques0_.employeeid)
62. 0.000 0.000 ↓ 0.0

Index Scan using persons_pkey on persons (cost=0.41..0.95 rows=1 width=329) (actual rows= loops=)

  • Index Cond: (person_id = users_1.person_id)
63. 0.000 0.000 ↓ 0.0

Index Scan using tender_lot_data_tender_lot_id_idx on tender_lot_data (cost=0.29..1.28 rows=1 width=1,040) (actual rows= loops=)

  • Index Cond: (tender_lot_id = bidderques0_.itemid)
64. 0.000 0.000 ↓ 0.0

Index Scan using bga_on_q_id on bidder_questions_answers answerses5_ (cost=0.29..0.77 rows=1 width=254) (actual rows= loops=)

  • Index Cond: (bidderques0_.questionid = questionid)
  • Filter: isactive
65. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=4,291.85..449,428.11 rows=171,885 width=1,070) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=4,288.93..275,251.31 rows=57,295 width=1,058) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Hash Join (cost=4,288.50..6,942.86 rows=57,295 width=520) (actual rows= loops=)

  • Hash Cond: (companies_data.company_id = companies.company_id)
68. 0.000 0.000 ↓ 0.0

Seq Scan on companies_data (cost=0.00..2,503.95 rows=57,295 width=329) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Hash (cost=3,598.22..3,598.22 rows=55,222 width=191) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Seq Scan on companies (cost=0.00..3,598.22 rows=55,222 width=191) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Limit (cost=0.44..4.66 rows=1 width=538) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.44..13.11 rows=3 width=538) (actual rows= loops=)

  • Join Filter: (comp_address.countryid = utl_countries.countryid)
73. 0.000 0.000 ↓ 0.0

Seq Scan on utl_countries (cost=0.00..1.44 rows=44 width=520) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

Materialize (cost=0.44..9.70 rows=3 width=26) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.44..9.69 rows=3 width=26) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Index Scan using comp_address_compid_iswithdrawn_idx on comp_address (cost=0.29..3.68 rows=3 width=8) (actual rows= loops=)

  • Index Cond: ((compid = companies.company_id) AND (iswithdrawn = false))
  • Filter: (NOT iswithdrawn)
77. 0.000 0.000 ↓ 0.0

Index Scan using utl_cities_pkey on utl_cities (cost=0.15..2.00 rows=1 width=26) (actual rows= loops=)

  • Index Cond: (cityid = comp_address.cityid)
78. 0.000 0.000 ↓ 0.0

HashAggregate (cost=2.92..2.98 rows=3 width=44) (actual rows= loops=)

  • Group Key: sum((count(*))) OVER (?)
79. 0.000 0.000 ↓ 0.0

WindowAgg (cost=2.74..2.81 rows=3 width=44) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

HashAggregate (cost=2.74..2.77 rows=3 width=12) (actual rows= loops=)

  • Group Key: ratings2.value
81. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.41..2.70 rows=5 width=4) (actual rows= loops=)

82. 0.000 0.000 ↓ 0.0

Seq Scan on rating_statuss (cost=0.00..1.04 rows=1 width=4) (actual rows= loops=)

  • Filter: ((rating_status_constant)::text = 'RATE_MARK_SET'::text)
83. 0.000 0.000 ↓ 0.0

Index Only Scan using ix_ratings2 on ratings2 (cost=0.41..1.62 rows=5 width=8) (actual rows= loops=)

  • Index Cond: ((objectcompid = companies.company_id) AND (ratingstatuslid = rating_statuss.rating_status_id))
Planning time : 4.162 ms