explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KQPV

Settings
# exclusive inclusive rows x rows loops node
1. 157,422.063 157,422.063 ↑ 30,482,644,427,372,864.0 4,623 1

Sort (cost=1,172,786,962,869,657,010,176.00..1,173,139,266,032,626,434,048.00 rows=140,921,265,187,744,743,424 width=560) (actual time=157,420.748..157,422.063 rows=4,623 loops=1)

  • Sort Key: cte.total DESC
  • Sort Method: quicksort Memory: 2992kB
2.          

CTE vendors

3. 47.665 247.525 ↓ 1.0 4,623 1

Hash Join (cost=16,170.40..19,835.07 rows=4,614 width=86) (actual time=189.903..247.525 rows=4,623 loops=1)

  • Hash Cond: (v.user_id = u.id)
4. 10.795 11.898 ↓ 1.0 4,623 1

Bitmap Heap Scan on vendors v (cost=124.05..2,181.28 rows=4,615 width=67) (actual time=1.422..11.898 rows=4,623 loops=1)

  • Recheck Cond: (status = 2987)
  • Filter: (id <> 266)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=1844
5. 1.103 1.103 ↓ 1.0 4,625 1

Bitmap Index Scan on vendor_index (cost=0.00..122.90 rows=4,615 width=0) (actual time=1.103..1.103 rows=4,625 loops=1)

  • Index Cond: (status = 2987)
6. 82.742 187.962 ↓ 1.0 244,736 1

Hash (cost=11,554.93..11,554.93 rows=244,593 width=23) (actual time=187.962..187.962 rows=244,736 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 3932kB
7. 105.220 105.220 ↓ 1.0 244,736 1

Seq Scan on users u (cost=0.00..11,554.93 rows=244,593 width=23) (actual time=0.016..105.220 rows=244,736 loops=1)

8.          

CTE products_count

9. 24.610 359.529 ↓ 20.7 4,143 1

GroupAggregate (cost=29,641.24..30,899.41 rows=200 width=12) (actual time=290.053..359.529 rows=4,143 loops=1)

  • Group Key: v_1.id
10. 17.998 334.919 ↓ 1.3 63,759 1

Merge Join (cost=29,641.24..30,643.96 rows=50,589 width=8) (actual time=289.867..334.919 rows=63,759 loops=1)

  • Merge Cond: (v_1.id = p.sn_page_id)
11. 3.009 4.441 ↓ 1.0 4,623 1

Sort (cost=373.08..384.62 rows=4,614 width=4) (actual time=2.994..4.441 rows=4,623 loops=1)

  • Sort Key: v_1.id
  • Sort Method: quicksort Memory: 409kB
12. 1.432 1.432 ↓ 1.0 4,623 1

CTE Scan on vendors v_1 (cost=0.00..92.28 rows=4,614 width=4) (actual time=0.004..1.432 rows=4,623 loops=1)

13. 148.000 312.480 ↓ 1.0 97,294 1

Sort (cost=29,268.15..29,505.04 rows=94,753 width=8) (actual time=286.851..312.480 rows=97,294 loops=1)

  • Sort Key: p.sn_page_id
  • Sort Method: external sort Disk: 2120kB
14. 164.480 164.480 ↓ 1.0 98,333 1

Seq Scan on products p (cost=0.00..21,435.92 rows=94,753 width=8) (actual time=0.025..164.480 rows=98,333 loops=1)

  • Filter: ((deleted_at IS NULL) AND published AND (inventory > 0))
  • Rows Removed by Filter: 49674
15.          

CTE vendor_cover

16. 1.107 36.489 ↓ 7.8 4,302 1

Unique (cost=4,894.84..4,899.00 rows=555 width=8) (actual time=34.615..36.489 rows=4,302 loops=1)

17. 2.582 35.382 ↓ 7.8 4,302 1

Sort (cost=4,894.84..4,896.22 rows=555 width=8) (actual time=34.612..35.382 rows=4,302 loops=1)

  • Sort Key: v_2.id, ((CASE WHEN (fr.id)::boolean THEN 1 ELSE 0 END * 50))
  • Sort Method: quicksort Memory: 394kB
18. 2.943 32.800 ↓ 7.8 4,302 1

Hash Join (cost=3,782.69..4,869.54 rows=555 width=8) (actual time=28.457..32.800 rows=4,302 loops=1)

  • Hash Cond: (v_2.id = fr.entity_id)
19. 1.522 1.522 ↓ 1.0 4,623 1

CTE Scan on vendors v_2 (cost=0.00..92.28 rows=4,614 width=4) (actual time=0.002..1.522 rows=4,623 loops=1)

20. 2.487 28.335 ↑ 1.3 10,165 1

Hash (cost=3,617.70..3,617.70 rows=13,199 width=8) (actual time=28.335..28.335 rows=10,165 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 526kB
21. 23.079 25.848 ↑ 1.3 10,165 1

Bitmap Heap Scan on file_relations fr (cost=404.46..3,617.70 rows=13,199 width=8) (actual time=3.250..25.848 rows=10,165 loops=1)

  • Recheck Cond: (entity_type_id = 3202)
  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 5122
  • Heap Blocks: exact=2617
22. 2.769 2.769 ↑ 1.1 15,288 1

Bitmap Index Scan on relation_index (cost=0.00..401.17 rows=16,099 width=0) (actual time=2.769..2.769 rows=15,288 loops=1)

  • Index Cond: (entity_type_id = 3202)
23.          

CTE profile_image

24. 0.518 23.797 ↓ 2.9 1,978 1

Unique (cost=5,065.68..5,070.71 rows=671 width=8) (actual time=22.931..23.797 rows=1,978 loops=1)

25. 1.154 23.279 ↓ 2.9 1,978 1

Sort (cost=5,065.68..5,067.36 rows=671 width=8) (actual time=22.930..23.279 rows=1,978 loops=1)

  • Sort Key: v_3.id, ((CASE WHEN (fr_1.id)::boolean THEN 1 ELSE 0 END * 100))
  • Sort Method: quicksort Memory: 141kB
26. 1.972 22.125 ↓ 2.9 1,978 1

Hash Join (cost=3,945.59..5,034.17 rows=671 width=8) (actual time=18.735..22.125 rows=1,978 loops=1)

  • Hash Cond: (v_3.user_id = fr_1.entity_id)
27. 1.530 1.530 ↓ 1.0 4,623 1

CTE Scan on vendors v_3 (cost=0.00..92.28 rows=4,614 width=8) (actual time=0.002..1.530 rows=4,623 loops=1)

28. 2.159 18.623 ↑ 1.8 8,916 1

Hash (cost=3,745.97..3,745.97 rows=15,969 width=8) (actual time=18.623..18.623 rows=8,916 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 477kB
29. 13.081 16.464 ↑ 1.8 8,916 1

Bitmap Heap Scan on file_relations fr_1 (cost=490.50..3,745.97 rows=15,969 width=8) (actual time=3.805..16.464 rows=8,916 loops=1)

  • Recheck Cond: (entity_type_id = 3200)
  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 11127
  • Heap Blocks: exact=2506
30. 3.383 3.383 ↓ 1.0 20,060 1

Bitmap Index Scan on relation_index (cost=0.00..486.51 rows=19,478 width=0) (actual time=3.383..3.383 rows=20,060 loops=1)

  • Index Cond: (entity_type_id = 3200)
31.          

CTE vendor_app_activity

32. 14.371 321.676 ↓ 5.6 2,564 1

HashAggregate (cost=19,566.13..19,571.90 rows=461 width=20) (actual time=320.819..321.676 rows=2,564 loops=1)

  • Group Key: v_4.id, v_4.created_at
33. 12.020 307.305 ↓ 3.9 37,906 1

Hash Join (cost=16,596.97..19,492.45 rows=9,824 width=16) (actual time=293.827..307.305 rows=37,906 loops=1)

  • Hash Cond: (v_4.user_id = ula.user_id)
34. 1.745 1.745 ↓ 1.0 4,623 1

CTE Scan on vendors v_4 (cost=0.00..92.28 rows=4,614 width=16) (actual time=0.003..1.745 rows=4,623 loops=1)

35. 17.907 293.540 ↑ 1.1 54,605 1

Hash (cost=15,846.62..15,846.62 rows=60,028 width=8) (actual time=293.539..293.540 rows=54,605 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2646kB
36. 275.633 275.633 ↑ 1.1 54,605 1

Seq Scan on user_last_activity ula (cost=0.00..15,846.62 rows=60,028 width=8) (actual time=4.727..275.633 rows=54,605 loops=1)

  • Filter: ((client = 3389) AND (created_at > (('now'::cstring)::date - '60 days'::interval)))
  • Rows Removed by Filter: 396856
37.          

CTE customer_app_activity

38. 6.367 308.830 ↓ 2.5 1,152 1

HashAggregate (cost=20,544.54..20,550.30 rows=461 width=20) (actual time=308.457..308.830 rows=1,152 loops=1)

  • Group Key: v_5.id, v_5.created_at
39. 15.646 302.463 ↓ 1.3 17,206 1

Hash Join (cost=17,159.26..20,446.38 rows=13,088 width=16) (actual time=285.358..302.463 rows=17,206 loops=1)

  • Hash Cond: (v_5.user_id = ula_1.user_id)
40. 2.123 2.123 ↓ 1.0 4,623 1

CTE Scan on vendors v_5 (cost=0.00..92.28 rows=4,614 width=16) (actual time=0.003..2.123 rows=4,623 loops=1)

41. 26.559 284.694 ↓ 1.1 88,829 1

Hash (cost=15,846.62..15,846.62 rows=79,971 width=8) (actual time=284.694..284.694 rows=88,829 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2768kB
42. 258.135 258.135 ↓ 1.1 88,829 1

Seq Scan on user_last_activity ula_1 (cost=0.00..15,846.62 rows=79,971 width=8) (actual time=0.889..258.135 rows=88,829 loops=1)

  • Filter: ((client = 3388) AND (created_at > (('now'::cstring)::date - '60 days'::interval)))
  • Rows Removed by Filter: 362632
43.          

CTE web_public_activity

44. 4.300 308.940 ↓ 3.2 1,464 1

HashAggregate (cost=20,590.92..20,596.68 rows=461 width=20) (actual time=308.504..308.940 rows=1,464 loops=1)

  • Group Key: v_6.id, v_6.created_at
45. 12.227 304.640 ↑ 1.2 11,196 1

Hash Join (cost=17,191.97..20,490.33 rows=13,412 width=16) (actual time=291.281..304.640 rows=11,196 loops=1)

  • Hash Cond: (v_6.user_id = ula_2.user_id)
46. 1.759 1.759 ↓ 1.0 4,623 1

CTE Scan on vendors v_6 (cost=0.00..92.28 rows=4,614 width=16) (actual time=0.001..1.759 rows=4,623 loops=1)

47. 24.883 290.654 ↓ 1.0 82,366 1

Hash (cost=15,846.62..15,846.62 rows=81,948 width=8) (actual time=290.654..290.654 rows=82,366 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2634kB
48. 265.771 265.771 ↓ 1.0 82,366 1

Seq Scan on user_last_activity ula_2 (cost=0.00..15,846.62 rows=81,948 width=8) (actual time=0.930..265.771 rows=82,366 loops=1)

  • Filter: ((client = ANY ('{3386,3458}'::integer[])) AND (created_at > (('now'::cstring)::date - '60 days'::interval)))
  • Rows Removed by Filter: 369095
49.          

CTE about_a_life

50. 330.783 365.217 ↓ 1.0 4,623 1

Hash Join (cost=2,942.56..3,705.57 rows=4,614 width=8) (actual time=35.507..365.217 rows=4,623 loops=1)

  • Hash Cond: (v_7.id = v1.id)
51. 1.816 1.816 ↓ 1.0 4,623 1

CTE Scan on vendors v_7 (cost=0.00..92.28 rows=4,614 width=4) (actual time=0.002..1.816 rows=4,623 loops=1)

52. 7.274 32.618 ↓ 1.0 21,259 1

Hash (cost=2,200.25..2,200.25 rows=21,225 width=159) (actual time=32.618..32.618 rows=21,259 loops=1)

  • Buckets: 32768 Batches: 2 Memory Usage: 1007kB
53. 25.344 25.344 ↓ 1.0 21,259 1

Seq Scan on vendors v1 (cost=0.00..2,200.25 rows=21,225 width=159) (actual time=0.043..25.344 rows=21,259 loops=1)

54.          

CTE about_a_place

55. 117.545 150.114 ↓ 1.0 4,623 1

Hash Join (cost=2,465.56..2,713.57 rows=4,614 width=8) (actual time=30.914..150.114 rows=4,623 loops=1)

  • Hash Cond: (v_8.id = v1_1.id)
56. 1.909 1.909 ↓ 1.0 4,623 1

CTE Scan on vendors v_8 (cost=0.00..92.28 rows=4,614 width=4) (actual time=0.008..1.909 rows=4,623 loops=1)

57. 5.406 30.660 ↓ 1.0 21,259 1

Hash (cost=2,200.25..2,200.25 rows=21,225 width=96) (actual time=30.660..30.660 rows=21,259 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1444kB
58. 25.254 25.254 ↓ 1.0 21,259 1

Seq Scan on vendors v1_1 (cost=0.00..2,200.25 rows=21,225 width=96) (actual time=0.367..25.254 rows=21,259 loops=1)

59.          

CTE about_your_products

60. 435.417 471.204 ↓ 1.0 4,623 1

Hash Join (cost=3,067.56..3,955.57 rows=4,614 width=8) (actual time=34.177..471.204 rows=4,623 loops=1)

  • Hash Cond: (v_9.id = v1_2.id)
61. 1.836 1.836 ↓ 1.0 4,623 1

CTE Scan on vendors v_9 (cost=0.00..92.28 rows=4,614 width=4) (actual time=0.002..1.836 rows=4,623 loops=1)

62. 7.941 33.951 ↓ 1.0 21,259 1

Hash (cost=2,200.25..2,200.25 rows=21,225 width=206) (actual time=33.951..33.951 rows=21,259 loops=1)

  • Buckets: 32768 Batches: 2 Memory Usage: 1299kB
63. 26.010 26.010 ↓ 1.0 21,259 1

Seq Scan on vendors v1_2 (cost=0.00..2,200.25 rows=21,225 width=206) (actual time=0.016..26.010 rows=21,259 loops=1)

64.          

CTE purchase_count

65. 2.924 525.106 ↓ 3.7 749 1

GroupAggregate (cost=18,386.45..18,396.91 rows=200 width=12) (actual time=521.200..525.106 rows=749 loops=1)

  • Group Key: v_10.id
66. 4.166 522.182 ↓ 5.3 5,656 1

Sort (cost=18,386.45..18,389.10 rows=1,061 width=8) (actual time=521.143..522.182 rows=5,656 loops=1)

  • Sort Key: v_10.id
  • Sort Method: quicksort Memory: 458kB
67. 9.216 518.016 ↓ 5.3 5,656 1

Nested Loop Anti Join (cost=13,639.61..18,333.13 rows=1,061 width=8) (actual time=213.613..518.016 rows=5,656 loops=1)

  • Join Filter: ((pii.vendor_id = v_10.id) AND (pii.user_id = v_10.user_id))
  • Rows Removed by Join Filter: 14912
68. 12.205 250.707 ↓ 7.0 7,821 1

Merge Join (cost=13,639.18..13,793.95 rows=1,114 width=12) (actual time=213.517..250.707 rows=7,821 loops=1)

  • Merge Cond: (v_10.user_id = pi.user_id)
69. 2.828 4.854 ↓ 1.0 4,623 1

Sort (cost=373.08..384.62 rows=4,614 width=8) (actual time=3.601..4.854 rows=4,623 loops=1)

  • Sort Key: v_10.user_id
  • Sort Method: quicksort Memory: 409kB
70. 2.026 2.026 ↓ 1.0 4,623 1

CTE Scan on vendors v_10 (cost=0.00..92.28 rows=4,614 width=8) (actual time=0.003..2.026 rows=4,623 loops=1)

71. 128.499 233.648 ↓ 3.6 86,115 1

Sort (cost=13,266.10..13,326.38 rows=24,111 width=8) (actual time=209.819..233.648 rows=86,115 loops=1)

  • Sort Key: pi.user_id
  • Sort Method: external sort Disk: 1880kB
72. 90.836 105.149 ↓ 3.6 87,328 1

Bitmap Heap Scan on payment_invoices pi (cost=2,645.33..11,511.13 rows=24,111 width=8) (actual time=15.922..105.149 rows=87,328 loops=1)

  • Recheck Cond: ((status = 2999) AND (deleted_at IS NULL))
  • Filter: (paid_at IS NOT NULL)
  • Heap Blocks: exact=7752
73. 14.313 14.313 ↓ 1.0 87,328 1

Bitmap Index Scan on payment_invoice_status_id_index (cost=0.00..2,639.30 rows=86,784 width=0) (actual time=14.313..14.313 rows=87,328 loops=1)

  • Index Cond: (status = 2999)
74. 258.093 258.093 ↑ 3.5 2 7,821

Index Scan using index_invoice_id on payment_invoice_items pii (cost=0.42..3.97 rows=7 width=12) (actual time=0.027..0.033 rows=2 loops=7,821)

  • Index Cond: (invoice_id = pi.id)
  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 2
75.          

CTE socials

76. 64.915 90.417 ↓ 1.0 4,623 1

Hash Join (cost=2,465.56..2,805.84 rows=4,614 width=8) (actual time=24.091..90.417 rows=4,623 loops=1)

  • Hash Cond: (v_11.id = v1_3.id)
77. 1.671 1.671 ↓ 1.0 4,623 1

CTE Scan on vendors v_11 (cost=0.00..92.28 rows=4,614 width=4) (actual time=0.003..1.671 rows=4,623 loops=1)

78. 7.216 23.831 ↓ 1.0 21,259 1

Hash (cost=2,200.25..2,200.25 rows=21,225 width=76) (actual time=23.831..23.831 rows=21,259 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1945kB
79. 16.615 16.615 ↓ 1.0 21,259 1

Seq Scan on vendors v1_3 (cost=0.00..2,200.25 rows=21,225 width=76) (actual time=0.014..16.615 rows=21,259 loops=1)

80.          

CTE product_images

81. 0.964 2,146.923 ↑ 9.7 4,141 1

Unique (cost=38,375.30..38,675.30 rows=40,000 width=44) (actual time=2,145.093..2,146.923 rows=4,141 loops=1)

82. 6.169 2,145.959 ↑ 9.7 4,141 1

Sort (cost=38,375.30..38,475.30 rows=40,000 width=44) (actual time=2,145.088..2,145.959 rows=4,141 loops=1)

  • Sort Key: ((CASE COALESCE(count(DISTINCT fr_2.id), '0'::bigint) WHEN 0 THEN '0'::numeric ELSE trunc((count(DISTINCT fr_2.id))::numeric, 2) END * '10'::numeric)) DESC, v_12.id
  • Sort Method: quicksort Memory: 516kB
83. 69.085 2,139.790 ↑ 9.7 4,141 1

GroupAggregate (cost=32,825.86..35,317.76 rows=40,000 width=44) (actual time=2,033.526..2,139.790 rows=4,141 loops=1)

  • Group Key: v_12.id, pc.score
84. 218.201 2,070.705 ↓ 1.3 169,776 1

Sort (cost=32,825.86..33,164.24 rows=135,352 width=16) (actual time=2,033.339..2,070.705 rows=169,776 loops=1)

  • Sort Key: v_12.id, pc.score
  • Sort Method: external sort Disk: 4976kB
85. 44.264 1,852.504 ↓ 1.3 169,776 1

Merge Join (cost=16,922.72..18,976.07 rows=135,352 width=16) (actual time=1,757.630..1,852.504 rows=169,776 loops=1)

  • Merge Cond: (v_12.id = pc.id)
86. 2.999 4.618 ↓ 1.0 4,623 1

Sort (cost=373.08..384.62 rows=4,614 width=4) (actual time=3.251..4.618 rows=4,623 loops=1)

  • Sort Key: v_12.id
  • Sort Method: quicksort Memory: 409kB
87. 1.619 1.619 ↓ 1.0 4,623 1

CTE Scan on vendors v_12 (cost=0.00..92.28 rows=4,614 width=4) (actual time=0.003..1.619 rows=4,623 loops=1)

88. 255.903 1,803.622 ↓ 28.9 169,776 1

Sort (cost=16,549.64..16,564.30 rows=5,867 width=20) (actual time=1,754.368..1,803.622 rows=169,776 loops=1)

  • Sort Key: pc.id
  • Sort Method: external sort Disk: 5640kB
89. 140.583 1,547.719 ↓ 28.9 169,776 1

Nested Loop (cost=0.84..16,182.41 rows=5,867 width=20) (actual time=0.095..1,547.719 rows=169,776 loops=1)

90. 44.221 274.048 ↓ 27.7 94,424 1

Nested Loop (cost=0.42..12,623.87 rows=3,414 width=20) (actual time=0.041..274.048 rows=94,424 loops=1)

91. 1.962 1.962 ↓ 20.7 4,143 1

CTE Scan on products_count pc (cost=0.00..4.00 rows=200 width=12) (actual time=0.003..1.962 rows=4,143 loops=1)

92. 227.865 227.865 ↓ 1.4 23 4,143

Index Scan using product_index on products p_1 (cost=0.42..62.93 rows=17 width=8) (actual time=0.011..0.055 rows=23 loops=4,143)

  • Index Cond: (sn_page_id = pc.id)
93. 1,133.088 1,133.088 ↑ 1.0 2 94,424

Index Scan using relation_index on file_relations fr_2 (cost=0.42..1.02 rows=2 width=8) (actual time=0.004..0.012 rows=2 loops=94,424)

  • Index Cond: ((entity_type_id = ANY ('{3231,3232}'::integer[])) AND (entity_id = p_1.id))
  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 0
94.          

CTE sell_quantity

95. 76.320 900.779 ↑ 5.8 3,639 1

HashAggregate (cost=51,843.92..52,109.24 rows=21,225 width=12) (actual time=899.348..900.779 rows=3,639 loops=1)

  • Group Key: v_13.id
96. 84.865 824.459 ↓ 2.1 227,921 1

Hash Join (cost=15,416.16..51,295.16 rows=109,752 width=8) (actual time=95.829..824.459 rows=227,921 loops=1)

  • Hash Cond: (pii_1.vendor_id = v_13.id)
97. 228.621 726.196 ↓ 2.1 227,921 1

Hash Join (cost=12,950.60..47,320.32 rows=109,803 width=8) (actual time=82.258..726.196 rows=227,921 loops=1)

  • Hash Cond: (pii_1.invoice_id = pi_1.id)
98. 416.029 416.029 ↑ 1.0 434,999 1

Seq Scan on payment_invoice_items pii_1 (cost=0.00..27,023.03 rows=437,243 width=12) (actual time=0.019..416.029 rows=434,999 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 366623
99. 24.658 81.546 ↓ 1.0 87,328 1

Hash (cost=11,526.80..11,526.80 rows=86,784 width=4) (actual time=81.546..81.546 rows=87,328 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2574kB
100. 43.641 56.888 ↓ 1.0 87,328 1

Bitmap Heap Scan on payment_invoices pi_1 (cost=2,661.00..11,526.80 rows=86,784 width=4) (actual time=14.825..56.888 rows=87,328 loops=1)

  • Recheck Cond: ((status = 2999) AND (deleted_at IS NULL))
  • Heap Blocks: exact=7752
101. 13.247 13.247 ↓ 1.0 87,328 1

Bitmap Index Scan on payment_invoice_status_id_index (cost=0.00..2,639.30 rows=86,784 width=0) (actual time=13.247..13.247 rows=87,328 loops=1)

  • Index Cond: (status = 2999)
102. 4.733 13.398 ↓ 1.0 21,259 1

Hash (cost=2,200.25..2,200.25 rows=21,225 width=4) (actual time=13.398..13.398 rows=21,259 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1004kB
103. 8.665 8.665 ↓ 1.0 21,259 1

Seq Scan on vendors v_13 (cost=0.00..2,200.25 rows=21,225 width=4) (actual time=0.015..8.665 rows=21,259 loops=1)

104.          

CTE customer_count

105. 61.560 1,086.600 ↓ 12.0 2,402 1

GroupAggregate (cost=57,159.96..61,008.39 rows=200 width=12) (actual time=921.397..1,086.600 rows=2,402 loops=1)

  • Group Key: v_14.id
106. 44.636 1,025.040 ↓ 1.1 206,544 1

Merge Join (cost=57,159.96..60,050.18 rows=191,143 width=8) (actual time=921.312..1,025.040 rows=206,544 loops=1)

  • Merge Cond: (v_14.id = pii_2.vendor_id)
107. 2.820 4.572 ↓ 1.0 4,622 1

Sort (cost=373.08..384.62 rows=4,614 width=4) (actual time=3.362..4.572 rows=4,622 loops=1)

  • Sort Key: v_14.id
  • Sort Method: quicksort Memory: 409kB
108. 1.752 1.752 ↓ 1.0 4,623 1

CTE Scan on vendors v_14 (cost=0.00..92.28 rows=4,614 width=4) (actual time=0.002..1.752 rows=4,623 loops=1)

109. 282.081 975.832 ↓ 2.1 228,033 1

Sort (cost=56,786.88..57,063.22 rows=110,537 width=8) (actual time=917.938..975.832 rows=228,033 loops=1)

  • Sort Key: pii_2.vendor_id
  • Sort Method: external sort Disk: 4904kB
110. 200.575 693.751 ↓ 2.1 228,033 1

Hash Join (cost=13,147.04..47,527.10 rows=110,537 width=8) (actual time=159.676..693.751 rows=228,033 loops=1)

  • Hash Cond: (pii_2.invoice_id = pi_2.id)
111. 333.778 333.778 ↑ 1.0 434,999 1

Seq Scan on payment_invoice_items pii_2 (cost=0.00..27,023.03 rows=437,243 width=12) (actual time=0.016..333.778 rows=434,999 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 366623
112. 30.279 159.398 ↓ 1.0 87,420 1

Hash (cost=11,712.99..11,712.99 rows=87,364 width=4) (actual time=159.398..159.398 rows=87,420 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2576kB
113. 129.119 129.119 ↓ 1.0 87,420 1

Seq Scan on payment_invoices pi_2 (cost=0.00..11,712.99 rows=87,364 width=4) (actual time=0.021..129.119 rows=87,420 loops=1)

  • Filter: (status = 2999)
  • Rows Removed by Filter: 229732
114.          

CTE vendor_coupon

115. 11.267 635.441 ↓ 1.6 321 1

GroupAggregate (cost=44,163.85..44,381.85 rows=200 width=12) (actual time=619.487..635.441 rows=321 loops=1)

  • Group Key: v_15.id
116. 3.625 624.174 ↓ 1.4 13,856 1

Merge Join (cost=44,163.85..44,331.24 rows=9,622 width=11) (actual time=616.551..624.174 rows=13,856 loops=1)

  • Merge Cond: (v_15.id = pii_3.vendor_id)
117. 2.771 4.495 ↓ 1.0 4,622 1

Sort (cost=373.08..384.62 rows=4,614 width=4) (actual time=3.541..4.495 rows=4,622 loops=1)

  • Sort Key: v_15.id
  • Sort Method: quicksort Memory: 409kB
118. 1.724 1.724 ↓ 1.0 4,623 1

CTE Scan on vendors v_15 (cost=0.00..92.28 rows=4,614 width=4) (actual time=0.002..1.724 rows=4,623 loops=1)

119. 8.634 616.054 ↓ 2.7 14,993 1

Sort (cost=43,790.77..43,804.68 rows=5,564 width=11) (actual time=612.997..616.054 rows=14,993 loops=1)

  • Sort Key: pii_3.vendor_id
  • Sort Method: quicksort Memory: 1132kB
120. 9.146 607.420 ↓ 2.7 14,993 1

Hash Join (cost=11,907.81..43,444.63 rows=5,564 width=11) (actual time=103.303..607.420 rows=14,993 loops=1)

  • Hash Cond: (pii_3.invoice_id = pi_3.id)
121. 495.144 495.144 ↑ 7.0 17,666 1

Seq Scan on payment_invoice_items pii_3 (cost=0.00..31,018.54 rows=123,370 width=8) (actual time=0.063..495.144 rows=17,666 loops=1)

  • Filter: ((deleted_at IS NULL) AND (((vendor_delivery_discount IS NOT NULL) AND (vendor_delivery_discount <> '0'::numeric)) OR ((vendor_product_discount IS NOT NULL) AND (vendor_product_discount <> '0'::numeric))))
  • Rows Removed by Filter: 783956
122. 14.436 103.130 ↓ 2.6 40,175 1

Hash (cost=11,712.99..11,712.99 rows=15,586 width=11) (actual time=103.130..103.130 rows=40,175 loops=1)

  • Buckets: 65536 (originally 16384) Batches: 1 (originally 1) Memory Usage: 2320kB
123. 88.694 88.694 ↓ 2.6 40,175 1

Seq Scan on payment_invoices pi_3 (cost=0.00..11,712.99 rows=15,586 width=11) (actual time=0.017..88.694 rows=40,175 loops=1)

  • Filter: ((coupon_code IS NOT NULL) AND (status = 2999))
  • Rows Removed by Filter: 276977
124.          

CTE customer_return_count

125. 2.550 1,160.744 ↑ 1.9 2,402 1

Hash Join (cost=57,688.79..61,984.33 rows=4,614 width=12) (actual time=838.375..1,160.744 rows=2,402 loops=1)

  • Hash Cond: (pii_4.vendor_id = v_16.id)
126. 25.032 1,156.312 ↓ 18.2 3,639 1

GroupAggregate (cost=57,538.83..61,658.95 rows=200 width=12) (actual time=836.431..1,156.312 rows=3,639 loops=1)

  • Group Key: pii_4.vendor_id
127. 201.291 1,131.280 ↑ 1.2 88,823 1

GroupAggregate (cost=57,538.83..59,734.89 rows=109,803 width=16) (actual time=836.355..1,131.280 rows=88,823 loops=1)

  • Group Key: pii_4.vendor_id, pi_4.user_id
128. 329.396 929.989 ↓ 2.1 227,921 1

Sort (cost=57,538.83..57,813.34 rows=109,803 width=12) (actual time=836.329..929.989 rows=227,921 loops=1)

  • Sort Key: pii_4.vendor_id, pi_4.user_id
  • Sort Method: external merge Disk: 4888kB
129. 203.382 600.593 ↓ 2.1 227,921 1

Hash Join (cost=12,950.60..46,466.32 rows=109,803 width=12) (actual time=82.154..600.593 rows=227,921 loops=1)

  • Hash Cond: (pii_4.invoice_id = pi_4.id)
130. 315.728 315.728 ↑ 1.0 434,999 1

Seq Scan on payment_invoice_items pii_4 (cost=0.00..27,023.03 rows=437,243 width=8) (actual time=0.017..315.728 rows=434,999 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 366623
131. 24.041 81.483 ↓ 1.0 87,328 1

Hash (cost=11,526.80..11,526.80 rows=86,784 width=8) (actual time=81.483..81.483 rows=87,328 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2746kB
132. 44.331 57.442 ↓ 1.0 87,328 1

Bitmap Heap Scan on payment_invoices pi_4 (cost=2,661.00..11,526.80 rows=86,784 width=8) (actual time=14.602..57.442 rows=87,328 loops=1)

  • Recheck Cond: ((status = 2999) AND (deleted_at IS NULL))
  • Heap Blocks: exact=7752
133. 13.111 13.111 ↓ 1.0 87,328 1

Bitmap Index Scan on payment_invoice_status_id_index (cost=0.00..2,639.30 rows=86,784 width=0) (actual time=13.111..13.111 rows=87,328 loops=1)

  • Index Cond: (status = 2999)
134. 0.991 1.882 ↓ 1.0 4,623 1

Hash (cost=92.28..92.28 rows=4,614 width=4) (actual time=1.882..1.882 rows=4,623 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 227kB
135. 0.891 0.891 ↓ 1.0 4,623 1

CTE Scan on vendors v_16 (cost=0.00..92.28 rows=4,614 width=4) (actual time=0.002..0.891 rows=4,623 loops=1)

136.          

CTE city_customer_count

137. 49.181 1,064.341 ↓ 12.0 2,402 1

GroupAggregate (cost=56,305.96..60,154.39 rows=200 width=12) (actual time=911.395..1,064.341 rows=2,402 loops=1)

  • Group Key: v_17.id
138. 46.155 1,015.160 ↓ 1.1 206,544 1

Merge Join (cost=56,305.96..59,196.18 rows=191,143 width=8) (actual time=911.313..1,015.160 rows=206,544 loops=1)

  • Merge Cond: (v_17.id = pii_5.vendor_id)
139. 2.749 4.919 ↓ 1.0 4,622 1

Sort (cost=373.08..384.62 rows=4,614 width=4) (actual time=3.859..4.919 rows=4,622 loops=1)

  • Sort Key: v_17.id
  • Sort Method: quicksort Memory: 409kB
140. 2.170 2.170 ↓ 1.0 4,623 1

CTE Scan on vendors v_17 (cost=0.00..92.28 rows=4,614 width=4) (actual time=0.002..2.170 rows=4,623 loops=1)

141. 289.686 964.086 ↓ 2.1 228,033 1

Sort (cost=55,932.88..56,209.22 rows=110,537 width=8) (actual time=907.439..964.086 rows=228,033 loops=1)

  • Sort Key: pii_5.vendor_id
  • Sort Method: external sort Disk: 4904kB
142. 206.400 674.400 ↓ 2.1 228,033 1

Hash Join (cost=13,147.04..46,673.10 rows=110,537 width=8) (actual time=139.737..674.400 rows=228,033 loops=1)

  • Hash Cond: (pii_5.invoice_id = pi_5.id)
143. 328.930 328.930 ↑ 1.0 434,999 1

Seq Scan on payment_invoice_items pii_5 (cost=0.00..27,023.03 rows=437,243 width=8) (actual time=0.017..328.930 rows=434,999 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 366623
144. 26.126 139.070 ↓ 1.0 87,420 1

Hash (cost=11,712.99..11,712.99 rows=87,364 width=8) (actual time=139.070..139.070 rows=87,420 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2748kB
145. 112.944 112.944 ↓ 1.0 87,420 1

Seq Scan on payment_invoices pi_5 (cost=0.00..11,712.99 rows=87,364 width=8) (actual time=0.020..112.944 rows=87,420 loops=1)

  • Filter: (status = 2999)
  • Rows Removed by Filter: 229732
146.          

CTE rezayat_count

147. 65.142 1,465.513 ↓ 10.7 2,144 1

GroupAggregate (cost=79,802.79..80,828.10 rows=200 width=12) (actual time=1,299.314..1,465.513 rows=2,144 loops=1)

  • Group Key: v_18.id
148. 46.352 1,400.371 ↓ 4.0 197,743 1

Merge Join (cost=79,802.79..80,575.67 rows=49,987 width=8) (actual time=1,299.240..1,400.371 rows=197,743 loops=1)

  • Merge Cond: (v_18.id = pii_6.vendor_id)
149. 2.777 4.539 ↓ 1.0 4,622 1

Sort (cost=373.08..384.62 rows=4,614 width=4) (actual time=3.477..4.539 rows=4,622 loops=1)

  • Sort Key: v_18.id
  • Sort Method: quicksort Memory: 409kB
150. 1.762 1.762 ↓ 1.0 4,623 1

CTE Scan on vendors v_18 (cost=0.00..92.28 rows=4,614 width=4) (actual time=0.001..1.762 rows=4,623 loops=1)

151. 250.283 1,349.480 ↓ 7.5 215,594 1

Sort (cost=79,429.71..79,501.98 rows=28,907 width=8) (actual time=1,295.750..1,349.480 rows=215,594 loops=1)

  • Sort Key: pii_6.vendor_id
  • Sort Method: external sort Disk: 4632kB
152. 183.536 1,099.197 ↓ 7.5 215,594 1

Hash Join (cost=54,348.70..77,287.82 rows=28,907 width=8) (actual time=771.398..1,099.197 rows=215,594 loops=1)

  • Hash Cond: (piis.invoice_item_id = pii_6.id)
153. 148.563 181.952 ↓ 1.0 215,600 1

Bitmap Heap Scan on payment_invoice_item_status piis (cost=5,007.89..24,808.25 rows=208,984 width=8) (actual time=36.929..181.952 rows=215,600 loops=1)

  • Recheck Cond: (status = 3195)
  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 2896
  • Heap Blocks: exact=14332
154. 33.389 33.389 ↓ 1.0 218,646 1

Bitmap Index Scan on payment_invoice_item_status_status_idx (cost=0.00..4,955.64 rows=216,429 width=0) (actual time=33.389..33.389 rows=218,646 loops=1)

  • Index Cond: (status = 3195)
155. 73.543 733.709 ↓ 2.1 228,033 1

Hash (cost=47,527.10..47,527.10 rows=110,537 width=8) (actual time=733.708..733.709 rows=228,033 loops=1)

  • Buckets: 131072 (originally 131072) Batches: 4 (originally 2) Memory Usage: 3263kB
156. 207.351 660.166 ↓ 2.1 228,033 1

Hash Join (cost=13,147.04..47,527.10 rows=110,537 width=8) (actual time=140.201..660.166 rows=228,033 loops=1)

  • Hash Cond: (pii_6.invoice_id = pi_6.id)
157. 313.403 313.403 ↑ 1.0 434,999 1

Seq Scan on payment_invoice_items pii_6 (cost=0.00..27,023.03 rows=437,243 width=12) (actual time=0.015..313.403 rows=434,999 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 366623
158. 27.976 139.412 ↓ 1.0 87,420 1

Hash (cost=11,712.99..11,712.99 rows=87,364 width=4) (actual time=139.412..139.412 rows=87,420 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2576kB
159. 111.436 111.436 ↓ 1.0 87,420 1

Seq Scan on payment_invoices pi_6 (cost=0.00..11,712.99 rows=87,364 width=4) (actual time=0.032..111.436 rows=87,420 loops=1)

  • Filter: (status = 2999)
  • Rows Removed by Filter: 229732
160.          

CTE cancels

161. 0.226 224.386 ↓ 12.1 740 1

Unique (cost=7,562.48..7,563.09 rows=61 width=12) (actual time=224.036..224.386 rows=740 loops=1)

162. 0.793 224.160 ↓ 12.1 740 1

Sort (cost=7,562.48..7,562.63 rows=61 width=12) (actual time=224.033..224.160 rows=740 loops=1)

  • Sort Key: v_19.id, pi_7.id, pii_7.id
  • Sort Method: quicksort Memory: 59kB
163. 0.998 223.367 ↓ 12.1 740 1

Hash Join (cost=185.63..7,560.67 rows=61 width=12) (actual time=10.944..223.367 rows=740 loops=1)

  • Hash Cond: (pii_7.vendor_id = v_19.id)
164. 0.762 220.369 ↓ 49.9 1,748 1

Nested Loop (cost=35.67..7,389.85 rows=35 width=12) (actual time=8.649..220.369 rows=1,748 loops=1)

165. 1.096 209.119 ↓ 12.5 1,748 1

Nested Loop (cost=35.25..7,308.87 rows=140 width=12) (actual time=8.615..209.119 rows=1,748 loops=1)

166. 11.550 13.250 ↓ 5.0 1,891 1

Bitmap Heap Scan on payment_invoice_item_status piis_1 (cost=34.83..4,237.79 rows=376 width=4) (actual time=8.472..13.250 rows=1,891 loops=1)

  • Recheck Cond: (status = 3067)
  • Filter: ((deleted_at IS NULL) AND (created_at >= '2019-06-16 18:54:41'::timestamp without time zone))
  • Rows Removed by Filter: 6060
  • Heap Blocks: exact=3816
167. 1.700 1.700 ↓ 5.8 7,958 1

Bitmap Index Scan on payment_invoice_item_status_status_idx (cost=0.00..34.73 rows=1,374 width=0) (actual time=1.700..1.700 rows=7,958 loops=1)

  • Index Cond: (status = 3067)
168. 194.773 194.773 ↑ 1.0 1 1,891

Index Scan using payment_invoice_items_pkey on payment_invoice_items pii_7 (cost=0.42..8.16 rows=1 width=12) (actual time=0.101..0.103 rows=1 loops=1,891)

  • Index Cond: (id = piis_1.invoice_item_id)
  • Filter: ((deleted_at IS NULL) AND (location_deployment <> 3018))
  • Rows Removed by Filter: 0
169. 10.488 10.488 ↑ 1.0 1 1,748

Index Scan using payment_invoices_pkey on payment_invoices pi_7 (cost=0.42..0.57 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=1,748)

  • Index Cond: (id = pii_7.invoice_id)
  • Filter: (status = 2999)
170. 0.984 2.000 ↓ 1.0 4,623 1

Hash (cost=92.28..92.28 rows=4,614 width=4) (actual time=2.000..2.000 rows=4,623 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 227kB
171. 1.016 1.016 ↓ 1.0 4,623 1

CTE Scan on vendors v_19 (cost=0.00..92.28 rows=4,614 width=4) (actual time=0.001..1.016 rows=4,623 loops=1)

172.          

CTE cancel_customer

173. 0.058 392.098 ↓ 110.5 221 1

Unique (cost=4,452.42..4,452.44 rows=2 width=12) (actual time=392.012..392.098 rows=221 loops=1)

174. 0.348 392.040 ↓ 110.5 221 1

Sort (cost=4,452.42..4,452.43 rows=2 width=12) (actual time=392.009..392.040 rows=221 loops=1)

  • Sort Key: v_20.id, pi_8.id, pii_8.id
  • Sort Method: quicksort Memory: 35kB
175. 173.347 391.692 ↓ 110.5 221 1

Nested Loop (cost=35.58..4,452.41 rows=2 width=12) (actual time=11.058..391.692 rows=221 loops=1)

  • Join Filter: (pii_8.vendor_id = v_20.id)
  • Rows Removed by Join Filter: 1229497
176. 0.256 17.781 ↓ 266.0 266 1

Nested Loop (cost=35.58..4,302.46 rows=1 width=12) (actual time=9.596..17.781 rows=266 loops=1)

177. 0.249 15.397 ↓ 66.5 266 1

Nested Loop (cost=35.16..4,300.30 rows=4 width=12) (actual time=9.552..15.397 rows=266 loops=1)

178. 10.879 12.488 ↓ 38.0 266 1

Bitmap Heap Scan on payment_invoice_item_status piis_2 (cost=34.73..4,241.14 rows=7 width=4) (actual time=9.518..12.488 rows=266 loops=1)

  • Recheck Cond: (status = 3067)
  • Filter: ((deleted_at IS NULL) AND ((meta_data ->> 'cancelReason'::text) = '3478'::text))
  • Rows Removed by Filter: 7685
  • Heap Blocks: exact=3816
179. 1.609 1.609 ↓ 5.8 7,958 1

Bitmap Index Scan on payment_invoice_item_status_status_idx (cost=0.00..34.73 rows=1,374 width=0) (actual time=1.609..1.609 rows=7,958 loops=1)

  • Index Cond: (status = 3067)
180. 2.660 2.660 ↑ 1.0 1 266

Index Scan using payment_invoice_items_pkey on payment_invoice_items pii_8 (cost=0.42..8.44 rows=1 width=12) (actual time=0.009..0.010 rows=1 loops=266)

  • Index Cond: (id = piis_2.invoice_item_id)
  • Filter: (deleted_at IS NULL)
181. 2.128 2.128 ↑ 1.0 1 266

Index Scan using payment_invoices_pkey on payment_invoices pi_8 (cost=0.42..0.53 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=266)

  • Index Cond: (id = pii_8.invoice_id)
  • Filter: (status = 2999)
182. 200.564 200.564 ↓ 1.0 4,623 266

CTE Scan on vendors v_20 (cost=0.00..92.28 rows=4,614 width=4) (actual time=0.000..0.754 rows=4,623 loops=266)

183.          

CTE cancel_vendor_reason

184. 0.520 617.543 ↓ 10.0 607 1

HashSetOp Except (cost=0.00..2.36 rows=61 width=16) (actual time=617.450..617.543 rows=607 loops=1)

185. 0.120 617.023 ↓ 15.3 961 1

Append (cost=0.00..1.89 rows=63 width=16) (actual time=224.050..617.023 rows=961 loops=1)

186. 0.131 224.702 ↓ 12.1 740 1

Subquery Scan on *SELECT* 1 (cost=0.00..1.83 rows=61 width=16) (actual time=224.047..224.702 rows=740 loops=1)

187. 224.571 224.571 ↓ 12.1 740 1

CTE Scan on cancels (cost=0.00..1.22 rows=61 width=12) (actual time=224.044..224.571 rows=740 loops=1)

188. 0.039 392.201 ↓ 110.5 221 1

Subquery Scan on *SELECT* 2 (cost=0.00..0.06 rows=2 width=16) (actual time=392.024..392.201 rows=221 loops=1)

189. 392.162 392.162 ↓ 110.5 221 1

CTE Scan on cancel_customer (cost=0.00..0.04 rows=2 width=12) (actual time=392.021..392.162 rows=221 loops=1)

190.          

CTE cancel_count

191. 0.728 620.298 ↓ 1.4 289 1

GroupAggregate (cost=199.21..212.26 rows=200 width=12) (actual time=619.526..620.298 rows=289 loops=1)

  • Group Key: v_21.id
192. 0.279 619.570 ↑ 2.3 607 1

Sort (cost=199.21..202.73 rows=1,407 width=8) (actual time=619.481..619.570 rows=607 loops=1)

  • Sort Key: v_21.id
  • Sort Method: quicksort Memory: 53kB
193. 0.741 619.291 ↑ 2.3 607 1

Hash Join (cost=1.98..125.64 rows=1,407 width=8) (actual time=617.941..619.291 rows=607 loops=1)

  • Hash Cond: (v_21.id = cvr.id)
194. 0.641 0.641 ↓ 1.0 4,623 1

CTE Scan on vendors v_21 (cost=0.00..92.28 rows=4,614 width=4) (actual time=0.000..0.641 rows=4,623 loops=1)

195. 0.132 617.909 ↓ 10.0 607 1

Hash (cost=1.22..1.22 rows=61 width=8) (actual time=617.908..617.909 rows=607 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
196. 617.777 617.777 ↓ 10.0 607 1

CTE Scan on cancel_vendor_reason cvr (cost=0.00..1.22 rows=61 width=8) (actual time=617.453..617.777 rows=607 loops=1)

197.          

CTE sell_price

198. 261.031 1,429.260 ↓ 12.0 2,402 1

GroupAggregate (cost=60,281.96..66,797.02 rows=200 width=8) (actual time=971.630..1,429.260 rows=2,402 loops=1)

  • Group Key: v_22.id
199. 65.356 1,168.229 ↓ 1.1 206,544 1

Merge Join (cost=60,281.96..63,448.52 rows=191,143 width=19) (actual time=971.490..1,168.229 rows=206,544 loops=1)

  • Merge Cond: (v_22.id = pii_9.vendor_id)
200. 2.824 4.752 ↓ 1.0 4,622 1

Sort (cost=373.08..384.62 rows=4,614 width=4) (actual time=3.550..4.752 rows=4,622 loops=1)

  • Sort Key: v_22.id
  • Sort Method: quicksort Memory: 409kB
201. 1.928 1.928 ↓ 1.0 4,623 1

CTE Scan on vendors v_22 (cost=0.00..92.28 rows=4,614 width=4) (actual time=0.009..1.928 rows=4,623 loops=1)

202. 38.644 1,098.121 ↓ 2.1 228,033 1

Materialize (cost=59,908.88..60,461.56 rows=110,537 width=19) (actual time=967.928..1,098.121 rows=228,033 loops=1)

203. 256.699 1,059.477 ↓ 2.1 228,033 1

Sort (cost=59,908.88..60,185.22 rows=110,537 width=19) (actual time=967.921..1,059.477 rows=228,033 loops=1)

  • Sort Key: pii_9.vendor_id
  • Sort Method: external merge Disk: 7336kB
204. 220.431 802.778 ↓ 2.1 228,033 1

Hash Join (cost=13,147.04..48,381.10 rows=110,537 width=19) (actual time=154.977..802.778 rows=228,033 loops=1)

  • Hash Cond: (pii_9.invoice_id = pi_9.id)
205. 428.100 428.100 ↑ 1.0 434,999 1

Seq Scan on payment_invoice_items pii_9 (cost=0.00..27,023.03 rows=437,243 width=23) (actual time=0.041..428.100 rows=434,999 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 366623
206. 30.127 154.247 ↓ 1.0 87,420 1

Hash (cost=11,712.99..11,712.99 rows=87,364 width=4) (actual time=154.247..154.247 rows=87,420 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2576kB
207. 124.120 124.120 ↓ 1.0 87,420 1

Seq Scan on payment_invoices pi_9 (cost=0.00..11,712.99 rows=87,364 width=4) (actual time=0.022..124.120 rows=87,420 loops=1)

  • Filter: (status = 2999)
  • Rows Removed by Filter: 229732
208.          

CTE product_review

209. 4.273 214.404 ↓ 7.0 1,392 1

GroupAggregate (cost=28,264.84..28,669.06 rows=200 width=12) (actual time=196.781..214.404 rows=1,392 loops=1)

  • Group Key: v_23.id
210. 6.577 210.131 ↓ 1.8 27,384 1

Merge Join (cost=28,264.84..28,588.86 rows=15,541 width=8) (actual time=196.748..210.131 rows=27,384 loops=1)

  • Merge Cond: (v_23.id = p_2.sn_page_id)
211. 2.639 4.371 ↓ 1.0 4,622 1

Sort (cost=373.08..384.62 rows=4,614 width=4) (actual time=3.377..4.371 rows=4,622 loops=1)

  • Sort Key: v_23.id
  • Sort Method: quicksort Memory: 409kB
212. 1.732 1.732 ↓ 1.0 4,623 1

CTE Scan on vendors v_23 (cost=0.00..92.28 rows=4,614 width=4) (actual time=0.001..1.732 rows=4,623 loops=1)

213. 14.595 199.183 ↑ 1.0 29,069 1

Sort (cost=27,891.76..27,964.53 rows=29,108 width=8) (actual time=193.360..199.183 rows=29,069 loops=1)

  • Sort Key: p_2.sn_page_id
  • Sort Method: quicksort Memory: 2131kB
214. 31.274 184.588 ↑ 1.0 29,069 1

Hash Join (cost=23,488.06..25,733.52 rows=29,108 width=8) (actual time=138.649..184.588 rows=29,069 loops=1)

  • Hash Cond: (pr.product_id = p_2.id)
215. 14.939 14.939 ↑ 1.0 29,069 1

Seq Scan on product_reviews pr (cost=0.00..1,040.22 rows=29,108 width=8) (actual time=0.038..14.939 rows=29,069 loops=1)

  • Filter: (star > 3)
  • Rows Removed by Filter: 3630
216. 41.642 138.375 ↓ 1.0 148,007 1

Hash (cost=21,067.14..21,067.14 rows=147,514 width=8) (actual time=138.375..138.375 rows=148,007 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3926kB
217. 96.733 96.733 ↓ 1.0 148,007 1

Seq Scan on products p_2 (cost=0.00..21,067.14 rows=147,514 width=8) (actual time=0.014..96.733 rows=148,007 loops=1)

218.          

CTE product_review_low

219. 0.720 32.080 ↓ 2.1 423 1

GroupAggregate (cost=15,321.05..15,375.37 rows=200 width=12) (actual time=28.429..32.080 rows=423 loops=1)

  • Group Key: v_24.id
220. 1.525 31.360 ↓ 1.7 1,984 1

Merge Join (cost=15,321.05..15,366.97 rows=1,180 width=8) (actual time=28.384..31.360 rows=1,984 loops=1)

  • Merge Cond: (p_3.sn_page_id = v_24.id)
221. 1.293 24.929 ↑ 1.0 2,208 1

Sort (cost=14,947.96..14,953.49 rows=2,210 width=8) (actual time=24.457..24.929 rows=2,208 loops=1)

  • Sort Key: p_3.sn_page_id
  • Sort Method: quicksort Memory: 200kB
222. 1.644 23.636 ↑ 1.0 2,208 1

Nested Loop (cost=0.42..14,825.20 rows=2,210 width=8) (actual time=0.074..23.636 rows=2,208 loops=1)

223. 8.744 8.744 ↑ 1.0 2,208 1

Seq Scan on product_reviews pr_1 (cost=0.00..1,040.22 rows=2,210 width=8) (actual time=0.037..8.744 rows=2,208 loops=1)

  • Filter: (star < 3)
  • Rows Removed by Filter: 30491
224. 13.248 13.248 ↑ 1.0 1 2,208

Index Scan using products_pkey on products p_3 (cost=0.42..6.23 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=2,208)

  • Index Cond: (id = pr_1.product_id)
225. 2.892 4.906 ↓ 1.3 6,114 1

Sort (cost=373.08..384.62 rows=4,614 width=4) (actual time=3.917..4.906 rows=6,114 loops=1)

  • Sort Key: v_24.id
  • Sort Method: quicksort Memory: 409kB
226. 2.014 2.014 ↓ 1.0 4,623 1

CTE Scan on vendors v_24 (cost=0.00..92.28 rows=4,614 width=4) (actual time=0.015..2.014 rows=4,623 loops=1)

227.          

CTE product_comment

228. 3.559 216.000 ↓ 8.7 1,732 1

GroupAggregate (cost=29,044.79..29,361.42 rows=200 width=12) (actual time=200.888..216.000 rows=1,732 loops=1)

  • Group Key: v_25.id
229. 5.547 212.441 ↓ 1.8 21,307 1

Merge Join (cost=29,044.79..29,299.19 rows=11,946 width=8) (actual time=200.835..212.441 rows=21,307 loops=1)

  • Merge Cond: (v_25.id = p_4.sn_page_id)
230. 2.582 4.220 ↓ 1.0 4,621 1

Sort (cost=373.08..384.62 rows=4,614 width=4) (actual time=3.278..4.220 rows=4,621 loops=1)

  • Sort Key: v_25.id
  • Sort Method: quicksort Memory: 409kB
231. 1.638 1.638 ↓ 1.0 4,623 1

CTE Scan on vendors v_25 (cost=0.00..92.28 rows=4,614 width=4) (actual time=0.002..1.638 rows=4,623 loops=1)

232. 13.092 202.674 ↓ 1.1 23,969 1

Sort (cost=28,671.70..28,727.64 rows=22,374 width=8) (actual time=197.547..202.674 rows=23,969 loops=1)

  • Sort Key: p_4.sn_page_id
  • Sort Method: quicksort Memory: 1892kB
233. 29.711 189.582 ↓ 1.1 23,969 1

Hash Join (cost=23,488.06..27,055.23 rows=22,374 width=8) (actual time=137.055..189.582 rows=23,969 loops=1)

  • Hash Cond: (c.entity_id = p_4.id)
234. 23.483 23.483 ↓ 1.1 23,969 1

Seq Scan on comments c (cost=0.00..2,506.53 rows=22,374 width=8) (actual time=0.024..23.483 rows=23,969 loops=1)

  • Filter: ((answer IS NOT NULL) AND (entity_type_id = 2971))
  • Rows Removed by Filter: 15428
235. 41.695 136.388 ↓ 1.0 148,007 1

Hash (cost=21,067.14..21,067.14 rows=147,514 width=8) (actual time=136.388..136.388 rows=148,007 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3925kB
236. 94.693 94.693 ↓ 1.0 148,007 1

Seq Scan on products p_4 (cost=0.00..21,067.14 rows=147,514 width=8) (actual time=0.009..94.693 rows=148,007 loops=1)

237.          

CTE product_comment_not_answer

238. 1.466 195.499 ↓ 6.7 1,348 1

GroupAggregate (cost=27,633.70..27,762.80 rows=200 width=12) (actual time=187.795..195.499 rows=1,348 loops=1)

  • Group Key: v_26.id
239. 3.034 194.033 ↓ 1.2 5,094 1

Merge Join (cost=27,633.70..27,739.05 rows=4,249 width=8) (actual time=187.749..194.033 rows=5,094 loops=1)

  • Merge Cond: (p_5.sn_page_id = v_26.id)
240. 4.628 185.134 ↑ 1.1 7,061 1

Sort (cost=27,260.62..27,280.51 rows=7,958 width=8) (actual time=183.481..185.134 rows=7,061 loops=1)

  • Sort Key: p_5.sn_page_id
  • Sort Method: quicksort Memory: 523kB
241. 21.283 180.506 ↑ 1.1 7,061 1

Hash Join (cost=23,488.06..26,745.01 rows=7,958 width=8) (actual time=140.732..180.506 rows=7,061 loops=1)

  • Hash Cond: (c_1.entity_id = p_5.id)
242. 19.120 19.120 ↑ 1.1 7,063 1

Seq Scan on comments c_1 (cost=0.00..2,506.53 rows=7,958 width=8) (actual time=0.033..19.120 rows=7,063 loops=1)

  • Filter: ((answer IS NULL) AND (entity_type_id = 2971))
  • Rows Removed by Filter: 32334
243. 42.464 140.103 ↓ 1.0 148,007 1

Hash (cost=21,067.14..21,067.14 rows=147,514 width=8) (actual time=140.103..140.103 rows=148,007 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3925kB
244. 97.639 97.639 ↓ 1.0 148,007 1

Seq Scan on products p_5 (cost=0.00..21,067.14 rows=147,514 width=8) (actual time=0.015..97.639 rows=148,007 loops=1)

245. 3.720 5.865 ↓ 1.8 8,353 1

Sort (cost=373.08..384.62 rows=4,614 width=4) (actual time=4.257..5.865 rows=8,353 loops=1)

  • Sort Key: v_26.id
  • Sort Method: quicksort Memory: 409kB
246. 2.145 2.145 ↓ 1.0 4,623 1

CTE Scan on vendors v_26 (cost=0.00..92.28 rows=4,614 width=4) (actual time=0.004..2.145 rows=4,623 loops=1)

247.          

CTE product_likes

248. 78.545 767.346 ↓ 20.1 4,016 1

HashAggregate (cost=54,110.05..54,112.55 rows=200 width=12) (actual time=766.133..767.346 rows=4,016 loops=1)

  • Group Key: v_27.id
249. 688.801 688.801 ↓ 1.7 267,262 1

Hash Join (cost=39,333.64..53,316.41 rows=158,729 width=8) (actual time=348.527..688.801 rows=267,262 loops=1)