explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tKeI

Settings
# exclusive inclusive rows x rows loops node
1. 0.029 14,139.660 ↑ 1.0 100 1

Hash Left Join (cost=6,288,619.48..6,288,627.60 rows=100 width=172) (actual time=14,139.509..14,139.660 rows=100 loops=1)

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

CTE people

3. 0.011 1.133 ↑ 1.0 100 1

Limit (cost=0.56..1,405.77 rows=100 width=60) (actual time=0.037..1.133 rows=100 loops=1)

4. 0.025 1.122 ↑ 9,004.9 100 1

Nested Loop (cost=0.56..12,653,651.44 rows=900,486 width=60) (actual time=0.036..1.122 rows=100 loops=1)

5. 0.697 0.697 ↑ 9,004.9 100 1

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

  • Filter: ((edge_property_id = 31) AND (target_item_id = 5))
  • Rows Removed by Filter: 5419
6. 0.400 0.400 ↑ 1.0 1 100

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

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

CTE aliases

8. 0.050 0.254 ↑ 2.4 42 1

HashAggregate (cost=3,207.43..3,208.68 rows=100 width=36) (actual time=0.244..0.254 rows=42 loops=1)

  • Group Key: people_2.id
9. 0.000 0.204 ↑ 6.3 109 1

Nested Loop (cost=0.43..3,203.97 rows=691 width=21) (actual time=0.016..0.204 rows=109 loops=1)

10. 0.009 0.009 ↑ 1.0 100 1

CTE Scan on people people_2 (cost=0.00..2.00 rows=100 width=4) (actual time=0.000..0.009 rows=100 loops=1)

11. 0.200 0.200 ↑ 7.0 1 100

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

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

CTE date_of_birth

13. 0.066 59.341 ↓ 4.7 98 1

GroupAggregate (cost=2,489,573.77..2,489,574.40 rows=21 width=8) (actual time=59.273..59.341 rows=98 loops=1)

  • Group Key: raw_statements.source_item_id
14. 0.065 59.275 ↓ 4.8 101 1

Sort (cost=2,489,573.77..2,489,573.82 rows=21 width=102) (actual time=59.262..59.275 rows=101 loops=1)

  • Sort Key: raw_statements.source_item_id
  • Sort Method: quicksort Memory: 51kB
15. 0.172 59.210 ↓ 4.8 101 1

Nested Loop (cost=120.50..2,489,573.31 rows=21 width=102) (actual time=1.286..59.210 rows=101 loops=1)

16. 1.138 1.138 ↑ 1.0 100 1

CTE Scan on people people_3 (cost=0.00..2.00 rows=100 width=4) (actual time=0.000..1.138 rows=100 loops=1)

17. 34.800 57.900 ↑ 1.0 1 100

Bitmap Heap Scan on item_statements raw_statements (cost=120.50..24,895.70 rows=1 width=102) (actual time=0.345..0.579 rows=1 loops=100)

  • 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: 37
  • Heap Blocks: exact=201
18. 23.100 23.100 ↑ 168.3 38 100

Bitmap Index Scan on item_statements_source_item_id_idx (cost=0.00..120.50 rows=6,396 width=0) (actual time=0.231..0.231 rows=38 loops=100)

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

CTE education

20. 0.029 14,004.720 ↑ 1.4 39 1

GroupAggregate (cost=187,095.88..187,096.98 rows=55 width=36) (actual time=14,004.690..14,004.720 rows=39 loops=1)

  • Group Key: people_4.id
21. 0.111 14,004.691 ↑ 1.1 50 1

Sort (cost=187,095.88..187,096.01 rows=55 width=60) (actual time=14,004.682..14,004.691 rows=50 loops=1)

  • Sort Key: people_4.id
  • Sort Method: quicksort Memory: 29kB
22. 0.168 14,004.580 ↑ 1.1 50 1

Nested Loop (cost=1,863.26..187,094.29 rows=55 width=60) (actual time=299.721..14,004.580 rows=50 loops=1)

23. 0.597 13,940.862 ↑ 1.1 50 1

Nested Loop (cost=1,862.69..186,673.55 rows=55 width=8) (actual time=297.201..13,940.862 rows=50 loops=1)

24. 0.165 0.165 ↑ 1.0 100 1

CTE Scan on people people_4 (cost=0.00..2.00 rows=100 width=4) (actual time=0.000..0.165 rows=100 loops=1)

25. 0.500 13,940.100 ↓ 0.0 0 100

Bitmap Heap Scan on qpq_item_statements education_1 (cost=1,862.69..1,866.71 rows=1 width=8) (actual time=139.400..139.401 rows=0 loops=100)

  • Recheck Cond: ((source_item_id = people_4.id) AND (edge_property_id = 69))
  • Heap Blocks: exact=39
26. 145.100 13,939.600 ↓ 0.0 0 100

BitmapAnd (cost=1,862.69..1,862.69 rows=1 width=0) (actual time=139.396..139.396 rows=0 loops=100)

27. 7.900 7.900 ↑ 78.3 18 100

Bitmap Index Scan on qpq_item_statements_source_item_id_idx (cost=0.00..27.14 rows=1,409 width=0) (actual time=0.079..0.079 rows=18 loops=100)

  • Index Cond: (source_item_id = people_4.id)
28. 13,786.600 13,786.600 ↓ 9.1 890,019 100

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

  • Index Cond: (edge_property_id = 69)
29. 63.550 63.550 ↑ 1.0 1 50

Index Scan using item_pkey on item schools (cost=0.56..7.64 rows=1 width=60) (actual time=1.270..1.271 rows=1 loops=50)

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

CTE occupation

31. 0.059 62.359 ↑ 1.0 97 1

GroupAggregate (cost=560,356.95..560,375.52 rows=100 width=36) (actual time=62.290..62.359 rows=97 loops=1)

  • Group Key: people_5.id
32. 0.083 62.300 ↑ 10.5 219 1

Sort (cost=560,356.95..560,362.72 rows=2,310 width=60) (actual time=62.282..62.300 rows=219 loops=1)

  • Sort Key: people_5.id
  • Sort Method: quicksort Memory: 37kB
33. 0.145 62.217 ↑ 10.5 219 1

Nested Loop (cost=1.14..560,227.89 rows=2,310 width=60) (actual time=0.026..62.217 rows=219 loops=1)

34. 0.016 0.752 ↑ 10.5 219 1

Nested Loop (cost=0.57..544,935.97 rows=2,310 width=8) (actual time=0.018..0.752 rows=219 loops=1)

35. 0.036 0.036 ↑ 1.0 100 1

CTE Scan on people people_5 (cost=0.00..2.00 rows=100 width=4) (actual time=0.000..0.036 rows=100 loops=1)

36. 0.700 0.700 ↑ 11.5 2 100

Index Scan using qpq_item_statements_source_item_id_idx on qpq_item_statements occupations_enc (cost=0.57..5,449.11 rows=23 width=8) (actual time=0.004..0.007 rows=2 loops=100)

  • Index Cond: (source_item_id = people_5.id)
  • Filter: (edge_property_id = 106)
  • Rows Removed by Filter: 16
37. 61.320 61.320 ↑ 1.0 1 219

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

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

CTE date_of_death

39. 0.050 1.268 ↓ 5.8 64 1

GroupAggregate (cost=2,489,573.50..2,489,573.83 rows=11 width=8) (actual time=1.226..1.268 rows=64 loops=1)

  • Group Key: raw_statements_1.source_item_id
40. 0.017 1.218 ↓ 5.9 65 1

Sort (cost=2,489,573.50..2,489,573.52 rows=11 width=102) (actual time=1.216..1.218 rows=65 loops=1)

  • Sort Key: raw_statements_1.source_item_id
  • Sort Method: quicksort Memory: 42kB
41. 0.077 1.201 ↓ 5.9 65 1

Nested Loop (cost=120.50..2,489,573.31 rows=11 width=102) (actual time=0.028..1.201 rows=65 loops=1)

42. 0.024 0.024 ↑ 1.0 100 1

CTE Scan on people people_6 (cost=0.00..2.00 rows=100 width=4) (actual time=0.001..0.024 rows=100 loops=1)

43. 0.700 1.100 ↑ 1.0 1 100

Bitmap Heap Scan on item_statements raw_statements_1 (cost=120.50..24,895.70 rows=1 width=102) (actual time=0.009..0.011 rows=1 loops=100)

  • 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: 37
  • Heap Blocks: exact=201
44. 0.400 0.400 ↑ 168.3 38 100

Bitmap Index Scan on item_statements_source_item_id_idx (cost=0.00..120.50 rows=6,396 width=0) (actual time=0.004..0.004 rows=38 loops=100)

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

CTE country_of_citizenship

46. 0.039 11.237 ↑ 1.1 93 1

GroupAggregate (cost=557,356.54..557,371.73 rows=100 width=36) (actual time=11.198..11.237 rows=93 loops=1)

  • Group Key: people_7.id
47. 0.025 11.198 ↑ 16.6 112 1

Sort (cost=557,356.54..557,361.19 rows=1,859 width=60) (actual time=11.194..11.198 rows=112 loops=1)

  • Sort Key: people_7.id
  • Sort Method: quicksort Memory: 31kB
48. 0.078 11.173 ↑ 16.6 112 1

Nested Loop (cost=1.14..557,255.59 rows=1,859 width=60) (actual time=0.016..11.173 rows=112 loops=1)

49. 0.047 0.567 ↑ 16.6 112 1

Nested Loop (cost=0.57..544,931.97 rows=1,859 width=8) (actual time=0.009..0.567 rows=112 loops=1)

50. 0.020 0.020 ↑ 1.0 100 1

CTE Scan on people people_7 (cost=0.00..2.00 rows=100 width=4) (actual time=0.000..0.020 rows=100 loops=1)

51. 0.500 0.500 ↑ 19.0 1 100

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

  • Index Cond: (source_item_id = people_7.id)
  • Filter: (edge_property_id = 27)
  • Rows Removed by Filter: 17
52. 10.528 10.528 ↑ 1.0 1 112

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

  • Index Cond: (id = countries_enc.target_item_id)
53. 0.032 14,128.344 ↑ 1.0 100 1

Hash Left Join (cost=9.33..16.07 rows=100 width=140) (actual time=14,128.214..14,128.344 rows=100 loops=1)

  • Hash Cond: (people.id = date_of_death.id)
54. 0.041 14,127.019 ↑ 1.0 100 1

Hash Left Join (cost=8.97..15.23 rows=100 width=136) (actual time=14,126.913..14,127.019 rows=100 loops=1)

  • Hash Cond: (people.id = occupation.id)
55. 0.029 14,064.570 ↑ 1.0 100 1

Hash Left Join (cost=5.72..10.61 rows=100 width=104) (actual time=14,064.491..14,064.570 rows=100 loops=1)

  • Hash Cond: (people.id = education.id)
56. 0.040 59.784 ↑ 1.0 100 1

Hash Left Join (cost=3.93..7.89 rows=100 width=72) (actual time=59.724..59.784 rows=100 loops=1)

  • Hash Cond: (people.id = aliases.id)
57. 0.031 59.462 ↑ 1.0 100 1

Hash Left Join (cost=0.68..3.27 rows=100 width=40) (actual time=59.431..59.462 rows=100 loops=1)

  • Hash Cond: (people.id = date_of_birth.id)
58. 0.047 0.047 ↑ 1.0 100 1

CTE Scan on people (cost=0.00..2.00 rows=100 width=36) (actual time=0.039..0.047 rows=100 loops=1)

59. 0.025 59.384 ↓ 4.7 98 1

Hash (cost=0.42..0.42 rows=21 width=8) (actual time=59.384..59.384 rows=98 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
60. 59.359 59.359 ↓ 4.7 98 1

CTE Scan on date_of_birth (cost=0.00..0.42 rows=21 width=8) (actual time=59.275..59.359 rows=98 loops=1)

61. 0.015 0.282 ↑ 2.4 42 1

Hash (cost=2.00..2.00 rows=100 width=36) (actual time=0.282..0.282 rows=42 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
62. 0.267 0.267 ↑ 2.4 42 1

CTE Scan on aliases (cost=0.00..2.00 rows=100 width=36) (actual time=0.245..0.267 rows=42 loops=1)

63. 0.013 14,004.757 ↑ 1.4 39 1

Hash (cost=1.10..1.10 rows=55 width=36) (actual time=14,004.757..14,004.757 rows=39 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
64. 14,004.744 14,004.744 ↑ 1.4 39 1

CTE Scan on education (cost=0.00..1.10 rows=55 width=36) (actual time=14,004.693..14,004.744 rows=39 loops=1)

65. 0.021 62.408 ↑ 1.0 97 1

Hash (cost=2.00..2.00 rows=100 width=36) (actual time=62.408..62.408 rows=97 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
66. 62.387 62.387 ↑ 1.0 97 1

CTE Scan on occupation (cost=0.00..2.00 rows=100 width=36) (actual time=62.292..62.387 rows=97 loops=1)

67. 0.004 1.293 ↓ 5.8 64 1

Hash (cost=0.22..0.22 rows=11 width=8) (actual time=1.293..1.293 rows=64 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
68. 1.289 1.289 ↓ 5.8 64 1

CTE Scan on date_of_death (cost=0.00..0.22 rows=11 width=8) (actual time=1.227..1.289 rows=64 loops=1)

69. 0.023 11.287 ↑ 1.1 93 1

Hash (cost=2.00..2.00 rows=100 width=36) (actual time=11.287..11.287 rows=93 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
70. 11.264 11.264 ↑ 1.1 93 1

CTE Scan on country_of_citizenship (cost=0.00..2.00 rows=100 width=36) (actual time=11.200..11.264 rows=93 loops=1)

Planning time : 1.592 ms
Execution time : 14,139.850 ms