explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7GMX

Settings
# exclusive inclusive rows x rows loops node
1. 15.952 484.069 ↓ 35,311.0 105,933 1

Nested Loop (cost=182.44..4,936.81 rows=3 width=222) (actual time=0.093..484.069 rows=105,933 loops=1)

2.          

CTE prop_def_predicate

3. 0.034 0.896 ↑ 1.0 71 1

Nested Loop (cost=0.41..179.82 rows=71 width=51) (actual time=0.023..0.896 rows=71 loops=1)

4. 0.010 0.010 ↑ 1.0 71 1

Seq Scan on prop_def_temp pt (cost=0.00..1.71 rows=71 width=71) (actual time=0.004..0.010 rows=71 loops=1)

5. 0.852 0.852 ↑ 1.0 1 71

Index Scan using property_definition_key on property_definition p_1 (cost=0.41..2.51 rows=1 width=51) (actual time=0.012..0.012 rows=1 loops=71)

  • Index Cond: (key = pt.prop_def_input)
6. 1.155 18.117 ↓ 1,500.0 1,500 1

Nested Loop (cost=2.05..4,366.35 rows=1 width=133) (actual time=0.055..18.117 rows=1,500 loops=1)

  • Join Filter: (i.ptn = partition.ptn)
7. 1.008 15.462 ↓ 9.0 1,500 1

Nested Loop (cost=1.77..4,224.75 rows=167 width=113) (actual time=0.048..15.462 rows=1,500 loops=1)

  • Join Filter: (entity_type.id = e.entity_type_ref)
8. 0.743 0.954 ↑ 1.0 1,500 1

Hash Join (cost=1.20..47.00 rows=1,500 width=109) (actual time=0.030..0.954 rows=1,500 loops=1)

  • Hash Cond: (i.entity_type = entity_type.value)
9. 0.202 0.202 ↑ 1.0 1,500 1

Seq Scan on fqeid_temp i (cost=0.00..40.00 rows=1,500 width=99) (actual time=0.008..0.202 rows=1,500 loops=1)

10. 0.004 0.009 ↑ 1.0 9 1

Hash (cost=1.09..1.09 rows=9 width=21) (actual time=0.008..0.009 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
11. 0.005 0.005 ↑ 1.0 9 1

Seq Scan on entity_type (cost=0.00..1.09 rows=9 width=21) (actual time=0.003..0.005 rows=9 loops=1)

12. 13.500 13.500 ↑ 1.0 1 1,500

Index Scan using entity_id_value_key on entity e (cost=0.56..2.77 rows=1 width=49) (actual time=0.009..0.009 rows=1 loops=1,500)

  • Index Cond: (id_value = i.id_value)
13. 1.500 1.500 ↑ 1.0 1 1,500

Index Scan using partition_pkey on partition (cost=0.29..0.84 rows=1 width=79) (actual time=0.001..0.001 rows=1 loops=1,500)

  • Index Cond: (id = e.partition_ref)
14. 13.500 450.000 ↑ 2.0 71 1,500

Nested Loop (cost=0.57..389.22 rows=142 width=111) (actual time=0.003..0.300 rows=71 loops=1,500)

15. 10.500 10.500 ↑ 1.0 71 1,500

CTE Scan on prop_def_predicate p (cost=0.00..1.42 rows=71 width=36) (actual time=0.000..0.007 rows=71 loops=1,500)

16. 0.000 426.000 ↑ 2.0 1 106,500

Append (cost=0.57..5.44 rows=2 width=83) (actual time=0.003..0.004 rows=1 loops=106,500)

17. 319.500 319.500 ↑ 1.0 1 106,500

Index Scan using property_property_definition_id_entity_ref on property (cost=0.57..2.80 rows=1 width=83) (actual time=0.002..0.003 rows=1 loops=106,500)

  • Index Cond: ((property_definition_id = p.id) AND (entity_ref = e.id))
  • Filter: (((label_value IS NOT NULL) OR (metric_value IS NOT NULL)) AND (effective_at_range @> '2020-01-01 00:00:00+00'::timestamp with time zone) AND (as_at_range @> transaction_timestamp()))
18. 106.500 106.500 ↓ 0.0 0 106,500

Index Scan using property_history_property_definition_id_entity_ref on property_history (cost=0.42..2.63 rows=1 width=107) (actual time=0.001..0.001 rows=0 loops=106,500)

  • Index Cond: ((property_definition_id = p.id) AND (entity_ref = e.id))
  • Filter: (((label_value IS NOT NULL) OR (metric_value IS NOT NULL)) AND (effective_at_range @> '2020-01-01 00:00:00+00'::timestamp with time zone) AND (as_at_range @> transaction_timestamp()))
Planning time : 1.747 ms
Execution time : 488.018 ms