explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E8ra

Settings
# exclusive inclusive rows x rows loops node
1. 0.061 88,502.661 ↑ 1.0 200 1

Hash Left Join (cost=12,305,827.37..12,305,857.88 rows=200 width=172) (actual time=88,502.383..88,502.661 rows=200 loops=1)

  • Hash Cond: (people.id = country_of_citizenship.id)
2.          

CTE people

3. 0.020 1.367 ↑ 1.0 200 1

Limit (cost=0.56..2,810.97 rows=200 width=60) (actual time=0.015..1.367 rows=200 loops=1)

4. 0.000 1.347 ↑ 4,502.4 200 1

Nested Loop (cost=0.56..12,653,651.44 rows=900,486 width=60) (actual time=0.014..1.347 rows=200 loops=1)

5. 0.750 0.750 ↑ 4,502.4 200 1

Seq Scan on qpq_item_statements people_1 (cost=0.00..6,570,441.48 rows=900,486 width=4) (actual time=0.006..0.750 rows=200 loops=1)

  • Filter: ((edge_property_id = 31) AND (target_item_id = 5))
  • Rows Removed by Filter: 6119
6. 0.600 0.600 ↑ 1.0 1 200

Index Scan using item_pkey on item names (cost=0.56..6.75 rows=1 width=60) (actual time=0.002..0.003 rows=1 loops=200)

  • Index Cond: (id = people_1.source_item_id)
7.          

CTE aliases

8. 0.088 0.488 ↑ 2.4 82 1

HashAggregate (cost=6,375.56..6,378.06 rows=200 width=36) (actual time=0.475..0.488 rows=82 loops=1)

  • Group Key: people_2.id
9. 0.000 0.400 ↑ 9.2 150 1

Nested Loop (cost=0.43..6,368.65 rows=1,383 width=21) (actual time=0.011..0.400 rows=150 loops=1)

10. 0.029 0.029 ↑ 1.0 200 1

CTE Scan on people people_2 (cost=0.00..4.00 rows=200 width=4) (actual time=0.000..0.029 rows=200 loops=1)

11. 0.400 0.400 ↑ 7.0 1 200

Index Scan using item_aliases_item_id_idx on item_aliases aliases_1 (cost=0.43..31.75 rows=7 width=21) (actual time=0.001..0.002 rows=1 loops=200)

  • Index Cond: (item_id = people_2.id)
  • Filter: (lang = 'en'::text)
12.          

CTE date_of_birth

13. 0.126 40.261 ↓ 4.6 197 1

GroupAggregate (cost=4,886,974.98..4,886,976.27 rows=43 width=8) (actual time=40.139..40.261 rows=197 loops=1)

  • Group Key: raw_statements.source_item_id
14. 0.084 40.135 ↓ 4.7 203 1

Sort (cost=4,886,974.98..4,886,975.08 rows=43 width=102) (actual time=40.124..40.135 rows=203 loops=1)

  • Sort Key: raw_statements.source_item_id
  • Sort Method: quicksort Memory: 78kB
15. 0.203 40.051 ↓ 4.7 203 1

Nested Loop (cost=120.46..4,886,973.81 rows=43 width=102) (actual time=0.027..40.051 rows=203 loops=1)

16. 1.448 1.448 ↑ 1.0 200 1

CTE Scan on people people_3 (cost=0.00..4.00 rows=200 width=4) (actual time=0.000..1.448 rows=200 loops=1)

17. 35.000 38.400 ↑ 1.0 1 200

Bitmap Heap Scan on item_statements raw_statements (cost=120.46..24,434.84 rows=1 width=102) (actual time=0.078..0.192 rows=1 loops=200)

  • Recheck Cond: (source_item_id = people_3.id)
  • Filter: ((edge_property_id = 569) AND ("substring"(((target_datavalue -> 'value'::text) ->> 'time'::text), 1, 1) = '+'::text))
  • Rows Removed by Filter: 40
  • Heap Blocks: exact=463
18. 3.400 3.400 ↑ 156.0 41 200

Bitmap Index Scan on item_statements_source_item_id_idx (cost=0.00..120.46 rows=6,396 width=0) (actual time=0.017..0.017 rows=41 loops=200)

  • Index Cond: (source_item_id = people_3.id)
19.          

CTE education

20. 0.064 88,355.761 ↑ 1.1 101 1

GroupAggregate (cost=338,342.26..338,344.48 rows=111 width=36) (actual time=88,355.699..88,355.761 rows=101 loops=1)

  • Group Key: people_4.id
21. 0.109 88,355.697 ↓ 1.4 151 1

Sort (cost=338,342.26..338,342.53 rows=111 width=60) (actual time=88,355.685..88,355.697 rows=151 loops=1)

  • Sort Key: people_4.id
  • Sort Method: quicksort Memory: 37kB
22. 0.063 88,355.588 ↓ 1.4 151 1

Nested Loop (cost=1,842.37..338,338.49 rows=111 width=60) (actual time=255.745..88,355.588 rows=151 loops=1)

23. 135.525 88,220.682 ↓ 1.4 151 1

Hash Join (cost=1,841.80..337,489.36 rows=111 width=8) (actual time=255.731..88,220.682 rows=151 loops=1)

  • Hash Cond: (education_1.source_item_id = people_4.id)
24. 87,944.044 88,085.114 ↓ 9.1 890,019 1

Bitmap Heap Scan on qpq_item_statements education_1 (cost=1,835.30..334,789.48 rows=97,901 width=8) (actual time=255.229..88,085.114 rows=890,019 loops=1)

  • Recheck Cond: (edge_property_id = 69)
  • Heap Blocks: exact=385957
25. 141.070 141.070 ↓ 9.1 890,019 1

Bitmap Index Scan on qpq_item_statements_edge_property_id_idx (cost=0.00..1,810.83 rows=97,901 width=0) (actual time=141.069..141.070 rows=890,019 loops=1)

  • Index Cond: (edge_property_id = 69)
26. 0.018 0.043 ↑ 1.0 200 1

Hash (cost=4.00..4.00 rows=200 width=4) (actual time=0.043..0.043 rows=200 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
27. 0.025 0.025 ↑ 1.0 200 1

CTE Scan on people people_4 (cost=0.00..4.00 rows=200 width=4) (actual time=0.000..0.025 rows=200 loops=1)

28. 134.843 134.843 ↑ 1.0 1 151

Index Scan using item_pkey on item schools (cost=0.56..7.64 rows=1 width=60) (actual time=0.892..0.893 rows=1 loops=151)

  • Index Cond: (id = education_1.target_item_id)
29.          

CTE occupation

30. 0.120 92.096 ↑ 1.0 195 1

GroupAggregate (cost=1,095,124.25..1,095,161.40 rows=200 width=36) (actual time=91.952..92.096 rows=195 loops=1)

  • Group Key: people_5.id
31. 0.160 91.976 ↑ 10.0 460 1

Sort (cost=1,095,124.25..1,095,135.80 rows=4,620 width=60) (actual time=91.940..91.976 rows=460 loops=1)

  • Sort Key: people_5.id
  • Sort Method: quicksort Memory: 51kB
32. 0.070 91.816 ↑ 10.0 460 1

Nested Loop (cost=1.14..1,094,843.04 rows=4,620 width=60) (actual time=0.027..91.816 rows=460 loops=1)

33. 0.176 3.426 ↑ 10.0 460 1

Nested Loop (cost=0.57..1,064,259.20 rows=4,620 width=8) (actual time=0.019..3.426 rows=460 loops=1)

34. 0.050 0.050 ↑ 1.0 200 1

CTE Scan on people people_5 (cost=0.00..4.00 rows=200 width=4) (actual time=0.001..0.050 rows=200 loops=1)

35. 3.200 3.200 ↑ 11.5 2 200

Index Scan using qpq_item_statements_source_item_id_idx on qpq_item_statements occupations_enc (cost=0.57..5,321.05 rows=23 width=8) (actual time=0.014..0.016 rows=2 loops=200)

  • Index Cond: (source_item_id = people_5.id)
  • Filter: (edge_property_id = 106)
  • Rows Removed by Filter: 18
36. 88.320 88.320 ↑ 1.0 1 460

Index Scan using item_pkey on item occupations_labels (cost=0.56..6.61 rows=1 width=60) (actual time=0.192..0.192 rows=1 loops=460)

  • Index Cond: (id = occupations_enc.target_item_id)
37.          

CTE date_of_death

38. 0.073 3.186 ↓ 5.2 114 1

GroupAggregate (cost=4,886,974.30..4,886,974.96 rows=22 width=8) (actual time=3.109..3.186 rows=114 loops=1)

  • Group Key: raw_statements_1.source_item_id
39. 0.077 3.113 ↓ 5.4 118 1

Sort (cost=4,886,974.30..4,886,974.36 rows=22 width=102) (actual time=3.094..3.113 rows=118 loops=1)

  • Sort Key: raw_statements_1.source_item_id
  • Sort Method: quicksort Memory: 56kB
40. 0.208 3.036 ↓ 5.4 118 1

Nested Loop (cost=120.46..4,886,973.81 rows=22 width=102) (actual time=0.042..3.036 rows=118 loops=1)

41. 0.028 0.028 ↑ 1.0 200 1

CTE Scan on people people_6 (cost=0.00..4.00 rows=200 width=4) (actual time=0.000..0.028 rows=200 loops=1)

42. 1.800 2.800 ↑ 1.0 1 200

Bitmap Heap Scan on item_statements raw_statements_1 (cost=120.46..24,434.84 rows=1 width=102) (actual time=0.012..0.014 rows=1 loops=200)

  • Recheck Cond: (source_item_id = people_6.id)
  • Filter: ((edge_property_id = 570) AND ("substring"(((target_datavalue -> 'value'::text) ->> 'time'::text), 1, 1) = '+'::text))
  • Rows Removed by Filter: 40
  • Heap Blocks: exact=463
43. 1.000 1.000 ↑ 156.0 41 200

Bitmap Index Scan on item_statements_source_item_id_idx (cost=0.00..120.46 rows=6,396 width=0) (actual time=0.005..0.005 rows=41 loops=200)

  • Index Cond: (source_item_id = people_6.id)
44.          

CTE country_of_citizenship

45. 0.070 10.067 ↑ 1.0 194 1

GroupAggregate (cost=1,089,125.62..1,089,156.01 rows=200 width=36) (actual time=9.983..10.067 rows=194 loops=1)

  • Group Key: people_7.id
46. 0.072 9.997 ↑ 15.6 239 1

Sort (cost=1,089,125.62..1,089,134.92 rows=3,719 width=60) (actual time=9.974..9.997 rows=239 loops=1)

  • Sort Key: people_7.id
  • Sort Method: quicksort Memory: 38kB
47. 0.191 9.925 ↑ 15.6 239 1

Nested Loop (cost=1.14..1,088,905.07 rows=3,719 width=60) (actual time=0.018..9.925 rows=239 loops=1)

48. 0.104 1.130 ↑ 15.6 239 1

Nested Loop (cost=0.57..1,064,251.20 rows=3,719 width=8) (actual time=0.012..1.130 rows=239 loops=1)

49. 0.026 0.026 ↑ 1.0 200 1

CTE Scan on people people_7 (cost=0.00..4.00 rows=200 width=4) (actual time=0.000..0.026 rows=200 loops=1)

50. 1.000 1.000 ↑ 19.0 1 200

Index Scan using qpq_item_statements_source_item_id_idx on qpq_item_statements countries_enc (cost=0.57..5,321.05 rows=19 width=8) (actual time=0.003..0.005 rows=1 loops=200)

  • Index Cond: (source_item_id = people_7.id)
  • Filter: (edge_property_id = 27)
  • Rows Removed by Filter: 19
51. 8.604 8.604 ↑ 1.0 1 239

Index Scan using item_pkey on item countries_labeled (cost=0.56..6.62 rows=1 width=60) (actual time=0.036..0.036 rows=1 loops=239)

  • Index Cond: (id = countries_enc.target_item_id)
52. 0.055 88,492.440 ↑ 1.0 200 1

Hash Left Join (cost=18.72..41.73 rows=200 width=140) (actual time=88,492.207..88,492.440 rows=200 loops=1)

  • Hash Cond: (people.id = date_of_death.id)
53. 0.059 88,489.153 ↑ 1.0 200 1

Hash Left Join (cost=18.00..40.05 rows=200 width=136) (actual time=88,488.951..88,489.153 rows=200 loops=1)

  • Hash Cond: (people.id = occupation.id)
54. 0.054 88,396.902 ↑ 1.0 200 1

Hash Left Join (cost=11.50..26.04 rows=200 width=104) (actual time=88,396.739..88,396.902 rows=200 loops=1)

  • Hash Cond: (people.id = education.id)
55. 0.064 41.028 ↑ 1.0 200 1

Hash Left Join (cost=7.90..20.58 rows=200 width=72) (actual time=40.907..41.028 rows=200 loops=1)

  • Hash Cond: (people.id = aliases.id)
56. 0.056 40.430 ↑ 1.0 200 1

Hash Left Join (cost=1.40..6.58 rows=200 width=40) (actual time=40.365..40.430 rows=200 loops=1)

  • Hash Cond: (people.id = date_of_birth.id)
57. 0.030 0.030 ↑ 1.0 200 1

CTE Scan on people (cost=0.00..4.00 rows=200 width=36) (actual time=0.017..0.030 rows=200 loops=1)

58. 0.031 40.344 ↓ 4.6 197 1

Hash (cost=0.86..0.86 rows=43 width=8) (actual time=40.343..40.344 rows=197 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
59. 40.313 40.313 ↓ 4.6 197 1

CTE Scan on date_of_birth (cost=0.00..0.86 rows=43 width=8) (actual time=40.141..40.313 rows=197 loops=1)

60. 0.018 0.534 ↑ 2.4 82 1

Hash (cost=4.00..4.00 rows=200 width=36) (actual time=0.534..0.534 rows=82 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
61. 0.516 0.516 ↑ 2.4 82 1

CTE Scan on aliases (cost=0.00..4.00 rows=200 width=36) (actual time=0.476..0.516 rows=82 loops=1)

62. 0.033 88,355.820 ↑ 1.1 101 1

Hash (cost=2.22..2.22 rows=111 width=36) (actual time=88,355.820..88,355.820 rows=101 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
63. 88,355.787 88,355.787 ↑ 1.1 101 1

CTE Scan on education (cost=0.00..2.22 rows=111 width=36) (actual time=88,355.701..88,355.787 rows=101 loops=1)

64. 0.041 92.192 ↑ 1.0 195 1

Hash (cost=4.00..4.00 rows=200 width=36) (actual time=92.192..92.192 rows=195 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
65. 92.151 92.151 ↑ 1.0 195 1

CTE Scan on occupation (cost=0.00..4.00 rows=200 width=36) (actual time=91.954..92.151 rows=195 loops=1)

66. 0.027 3.232 ↓ 5.2 114 1

Hash (cost=0.44..0.44 rows=22 width=8) (actual time=3.232..3.232 rows=114 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
67. 3.205 3.205 ↓ 5.2 114 1

CTE Scan on date_of_death (cost=0.00..0.44 rows=22 width=8) (actual time=3.110..3.205 rows=114 loops=1)

68. 0.046 10.160 ↑ 1.0 194 1

Hash (cost=4.00..4.00 rows=200 width=36) (actual time=10.160..10.160 rows=194 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
69. 10.114 10.114 ↑ 1.0 194 1

CTE Scan on country_of_citizenship (cost=0.00..4.00 rows=200 width=36) (actual time=9.983..10.114 rows=194 loops=1)

Planning time : 1.349 ms
Execution time : 88,502.870 ms