explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eGyq

Settings
# exclusive inclusive rows x rows loops node
1. 167.308 250.638 ↓ 2.4 13,659 1

Sort (cost=101,076.83..101,091.02 rows=5,678 width=2,444) (actual time=245.075..250.638 rows=13,659 loops=1)

  • Sort Key: o.tag_recommend DESC NULLS LAST, o.order_order_cost DESC
  • Sort Method: quicksort Memory: 31006kB
2. 6.116 83.330 ↓ 2.4 13,659 1

Nested Loop Left Join (cost=72,913.01..100,722.77 rows=5,678 width=2,444) (actual time=50.791..83.330 rows=13,659 loops=1)

3. 1.052 68.002 ↑ 1.9 658 1

Nested Loop Left Join (cost=72,912.59..95,247.51 rows=1,271 width=2,444) (actual time=50.775..68.002 rows=658 loops=1)

4. 0.411 60.370 ↑ 1.9 658 1

Hash Left Join (cost=72,912.15..84,638.85 rows=1,271 width=2,420) (actual time=50.757..60.370 rows=658 loops=1)

  • Hash Cond: (o.id_lang_trans = lt.id_lang)
5. 0.570 59.907 ↑ 1.9 658 1

Hash Join (cost=72,909.13..84,630.84 rows=1,271 width=2,395) (actual time=50.691..59.907 rows=658 loops=1)

  • Hash Cond: (o.id_lang = l.id_lang)
6. 0.425 59.296 ↑ 1.9 658 1

Nested Loop (cost=72,906.10..84,610.33 rows=1,271 width=2,370) (actual time=50.639..59.296 rows=658 loops=1)

7. 0.605 52.291 ↑ 1.9 658 1

Hash Join (cost=72,905.67..73,952.85 rows=1,271 width=2,352) (actual time=50.626..52.291 rows=658 loops=1)

  • Hash Cond: (o.id_cat = c.id_cat)
8. 1.084 51.527 ↑ 1.9 658 1

Hash Right Join (cost=72,716.32..73,746.03 rows=1,271 width=2,305) (actual time=50.454..51.527 rows=658 loops=1)

  • Hash Cond: (v.id_post = o.id_post)
9. 0.017 0.017 ↑ 155.0 2 1

Index Scan using pk_blog_post_votes on post_votes v (cost=0.56..1,029.09 rows=310 width=9) (actual time=0.016..0.017 rows=2 loops=1)

  • Index Cond: (id_user = 27241601)
10. 1.519 50.426 ↑ 1.9 658 1

Hash (cost=72,699.87..72,699.87 rows=1,271 width=2,296) (actual time=50.426..50.426 rows=658 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 1100kB
11. 0.847 48.907 ↑ 1.9 658 1

Subquery Scan on o (cost=72,683.99..72,699.87 rows=1,271 width=2,296) (actual time=47.890..48.907 rows=658 loops=1)

12. 9.129 48.060 ↑ 1.9 658 1

Sort (cost=72,683.99..72,687.16 rows=1,271 width=2,232) (actual time=47.887..48.060 rows=658 loops=1)

  • Sort Key: (((sum((t1.p_weight + t2.p_weight))) + ((o_1.order_tag_money_rating * 5))::numeric)) DESC NULLS LAST, o_1.order_order_cost DESC
  • Sort Method: quicksort Memory: 1351kB
13. 1.044 38.931 ↑ 1.9 658 1

Hash Right Join (cost=72,464.71..72,618.45 rows=1,271 width=2,232) (actual time=37.902..38.931 rows=658 loops=1)

  • Hash Cond: (t2.id_order = o_1.id_order)
14. 0.007 0.031 ↓ 0.0 0 1

Sort (cost=1,248.43..1,268.66 rows=8,092 width=24) (actual time=0.031..0.031 rows=0 loops=1)

  • Sort Key: (sum((t1.p_weight + t2.p_weight))) DESC
  • Sort Method: quicksort Memory: 25kB
15. 0.016 0.024 ↓ 0.0 0 1

HashAggregate (cost=622.02..723.17 rows=8,092 width=24) (actual time=0.024..0.024 rows=0 loops=1)

  • Group Key: t2.id_order
16. 0.000 0.008 ↓ 0.0 0 1

Nested Loop (cost=9.08..493.30 rows=17,162 width=24) (actual time=0.008..0.008 rows=0 loops=1)

17. 0.008 0.008 ↓ 0.0 0 1

Index Scan using idx_authors_tags_id_user on authors_tags t1 (cost=0.43..24.17 rows=10 width=28) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (id_user = 27241601)
18. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on orders_tags t2 (cost=8.65..46.81 rows=10 width=38) (never executed)

  • Recheck Cond: (((p_tag)::text = (t1.p_tag)::text) AND (p_val = t1.p_val))
19. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_orders_tags (cost=0.00..8.64 rows=10 width=0) (never executed)

  • Index Cond: (((p_tag)::text = (t1.p_tag)::text) AND (p_val = t1.p_val))
20. 1.608 37.856 ↑ 1.1 658 1

Hash (cost=71,207.45..71,207.45 rows=706 width=2,200) (actual time=37.856..37.856 rows=658 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1092kB
21. 1.051 36.248 ↑ 1.1 658 1

Nested Loop Anti Join (cost=754.24..71,207.45 rows=706 width=2,200) (actual time=0.112..36.248 rows=658 loops=1)

22. 32.490 32.565 ↑ 1.1 658 1

Hash Anti Join (cost=753.82..68,379.68 rows=713 width=2,200) (actual time=0.103..32.565 rows=658 loops=1)

  • Hash Cond: (o_1.id_order = otb.id_order)
  • -> Index Scan using orders_active_order_tag_money_rating_idx1 on orders_active o_1 (cost=7.05..67615.02 rows=747 width=2200) (actual time=0.090..32.015 rows=658 loops=1 (...)
  • Index Cond: (order_tag_money_rating > 0)
  • Filter: (is_active AND is_white AND (jobs_can_take > 0) AND ((themes_active AND (themes_jobs_can_take > 0)) OR (NOT themes_active) OR (themes_active IS NULL)) AND ( (...)
  • Rows Removed by Filter: 191
  • -> Index Scan using tender_requests_id_author_request_state_idx on tender_requests r_3 (cost=0.42..8.44 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=1 (...)
  • Index Cond: ((id_author = 27241601) AND (request_state = 7))
23. 0.000 0.008 ↓ 0.0 0 1

Hash (cost=744.18..744.18 rows=207 width=8) (actual time=0.008..0.008 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
24. 0.008 0.008 ↓ 0.0 0 1

Index Scan using idx_orders_to_black_author on orders_to_black otb (cost=0.43..744.18 rows=207 width=8) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (id_author = 27241601)
25.          

SubPlan (forHash Anti Join)

26. 0.005 0.005 ↑ 3.0 2 1

Index Only Scan using idx_author_list on white_list (cost=0.42..4.53 rows=6 width=8) (actual time=0.004..0.005 rows=2 loops=1)

  • Index Cond: (id_author = 27241601)
  • Heap Fetches: 0
27. 0.000 0.000 ↓ 0.0 0

Index Scan using tender_requests_id_order_id_author_idx on tender_requests r (cost=0.41..8.44 rows=1 width=0) (never executed)

  • Index Cond: ((id_order = o_1.id_order) AND (id_author = 27241601))
  • Filter: ((date_end > now()) AND (request_state = ANY ('{2,7,6,5,8}'::integer[])))
28. 0.026 0.026 ↓ 0.0 0 1

Index Scan using idx_tender_requests_author on tender_requests r_1 (cost=0.42..20.37 rows=1 width=8) (actual time=0.026..0.026 rows=0 loops=1)

  • Index Cond: (id_author = 27241601)
  • Filter: ((date_end > now()) AND (request_state = ANY ('{2,7,6,5,8}'::integer[])))
  • Rows Removed by Filter: 8
29. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_author_list on white_list white_list_1 (cost=0.42..4.44 rows=1 width=0) (never executed)

  • Index Cond: ((id_author = 27241601) AND (id_list = o_1.id_list))
  • Heap Fetches: 0
30. 0.006 0.006 ↑ 3.0 2 1

Index Only Scan using idx_author_list on white_list white_list_2 (cost=0.42..4.53 rows=6 width=8) (actual time=0.005..0.006 rows=2 loops=1)

  • Index Cond: (id_author = 27241601)
  • Heap Fetches: 0
31. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_orders_to on orders_to (cost=0.56..8.58 rows=1 width=0) (never executed)

  • Index Cond: ((id_order = o_1.id_order) AND (id_author = 27241601))
  • Heap Fetches: 0
32. 0.010 0.010 ↓ 0.0 0 1

Index Scan using idx_orders_to_author on orders_to orders_to_1 (cost=0.56..2,460.76 rows=1,041 width=8) (actual time=0.010..0.010 rows=0 loops=1)

  • Index Cond: (id_author = 27241601)
33. 0.004 0.004 ↑ 1.0 4 1

Seq Scan on advego_lists (cost=0.00..1.04 rows=4 width=8) (actual time=0.002..0.004 rows=4 loops=1)

34. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_author_list on white_list white_list_3 (cost=0.42..4.44 rows=1 width=0) (never executed)

  • Index Cond: ((id_author = 27241601) AND (id_list = o_1.id_list))
  • Heap Fetches: 0
35. 0.009 0.009 ↑ 3.0 2 1

Index Only Scan using idx_author_list on white_list white_list_4 (cost=0.42..4.53 rows=6 width=8) (actual time=0.008..0.009 rows=2 loops=1)

  • Index Cond: (id_author = 27241601)
  • Heap Fetches: 0
36. 0.000 0.000 ↓ 0.0 0

Index Scan using tender_requests_id_order_id_author_idx on tender_requests r_2 (cost=0.41..8.43 rows=1 width=0) (never executed)

  • Index Cond: ((id_order = o_1.id_order) AND (id_author = 27241601))
  • Filter: (request_state = 7)
37. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_tender_requests_order on tender_requests (cost=0.42..8.49 rows=4 width=0) (never executed)

  • Index Cond: (id_order = o_1.id_order)
  • Heap Fetches: 0
38. 0.000 0.000 ↓ 0.0 0

Seq Scan on tender_requests tender_requests_1 (cost=0.00..11,768.02 rows=249,902 width=8) (never executed)

39. 0.003 0.003 ↑ 1.0 4 1

Seq Scan on advego_lists advego_lists_1 (cost=0.00..1.04 rows=4 width=8) (actual time=0.002..0.003 rows=4 loops=1)

40. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_author_list on white_list white_list_5 (cost=0.42..4.44 rows=1 width=0) (never executed)

  • Index Cond: ((id_author = 27241601) AND (id_list = o_1.id_list))
  • Heap Fetches: 0
41. 0.004 0.004 ↑ 3.0 2 1

Index Only Scan using idx_author_list on white_list white_list_6 (cost=0.42..4.53 rows=6 width=8) (actual time=0.004..0.004 rows=2 loops=1)

  • Index Cond: (id_author = 27241601)
  • Heap Fetches: 0
42. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_orders_to on orders_to orders_to_2 (cost=0.56..8.58 rows=1 width=0) (never executed)

  • Index Cond: ((id_order = o_1.id_order) AND (id_author = 27241601))
  • Heap Fetches: 0
43. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_orders_to_author on orders_to orders_to_3 (cost=0.56..2,460.76 rows=1,041 width=8) (never executed)

  • Index Cond: (id_author = 27241601)
44. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_tender_requests_order on tender_requests r_4 (cost=4.96..19.40 rows=1 width=0) (never executed)

  • Index Cond: (id_order = o_1.id_order)
  • Filter: ((request_state = 3) AND ((id_list = 0) OR (hashed SubPlan 20)))
45.          

SubPlan (forIndex Scan)

46. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_author_list on white_list wl (cost=0.42..4.53 rows=6 width=8) (never executed)

  • Index Cond: (id_author = 27241601)
  • Heap Fetches: 0
47. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on tender_requests r_5 (cost=577.26..10,231.89 rows=12,101 width=8) (never executed)

  • Recheck Cond: (request_state = 3)
  • Filter: ((id_list = 0) OR (hashed SubPlan 22))
48. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on tender_requests_request_state_idx (cost=0.00..569.69 rows=22,036 width=0) (never executed)

  • Index Cond: (request_state = 3)
49.          

SubPlan (forBitmap Heap Scan)

50. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_author_list on white_list wl_1 (cost=0.42..4.53 rows=6 width=8) (never executed)

  • Index Cond: (id_author = 27241601)
  • Heap Fetches: 0
51. 2.632 2.632 ↓ 0.0 0 658

Index Only Scan using uq_black_list_author on black_list bl (cost=0.42..3.97 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=658)

  • Index Cond: ((id_user = o_1.id_user) AND (id_author = 27241601))
  • Heap Fetches: 0
52. 0.010 0.159 ↑ 1.0 60 1

Hash (cost=188.60..188.60 rows=60 width=55) (actual time=0.159..0.159 rows=60 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
53. 0.149 0.149 ↑ 1.0 60 1

Seq Scan on categories c (cost=0.00..188.60 rows=60 width=55) (actual time=0.003..0.149 rows=60 loops=1)

54. 6.580 6.580 ↑ 1.0 1 658

Index Scan using pk_users on users u (cost=0.43..8.38 rows=1 width=26) (actual time=0.006..0.010 rows=1 loops=658)

  • Index Cond: (id_user = o.id_user)
55. 0.016 0.041 ↑ 1.0 90 1

Hash (cost=1.90..1.90 rows=90 width=33) (actual time=0.041..0.041 rows=90 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
56. 0.025 0.025 ↑ 1.0 90 1

Seq Scan on languages l (cost=0.00..1.90 rows=90 width=33) (actual time=0.006..0.025 rows=90 loops=1)

57. 0.022 0.052 ↑ 1.0 90 1

Hash (cost=1.90..1.90 rows=90 width=33) (actual time=0.052..0.052 rows=90 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
58. 0.030 0.030 ↑ 1.0 90 1

Seq Scan on languages lt (cost=0.00..1.90 rows=90 width=33) (actual time=0.005..0.030 rows=90 loops=1)

59. 6.580 6.580 ↑ 1.0 1 658

Index Scan using pk_blog_posts on posts p (cost=0.43..8.34 rows=1 width=32) (actual time=0.006..0.010 rows=1 loops=658)

  • Index Cond: (o.id_post = id_post)
60. 9.212 9.212 ↓ 5.0 20 658

Index Only Scan using idx_tender_requests_order on tender_requests tr (cost=0.42..4.27 rows=4 width=8) (actual time=0.005..0.014 rows=20 loops=658)

  • Index Cond: (id_order = o.id_order)
  • Heap Fetches: 5116