explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qWoF

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 52.811 ↓ 0.0 0 1

Sort (cost=1,363,196.80..1,363,196.81 rows=5 width=8) (actual time=52.811..52.811 rows=0 loops=1)

  • Sort Key: p.feature_id
  • Sort Method: quicksort Memory: 25kB
2.          

CTE datasets_for_countries

3. 0.000 0.000 ↓ 0.0 0

Seq Scan on dataset (cost=0.00..23.38 rows=5 width=8) (never executed)

  • Filter: (country_id = 113)
4. 0.003 52.805 ↓ 0.0 0 1

Hash Join (cost=1,248,115.05..1,363,173.37 rows=5 width=8) (actual time=52.805..52.805 rows=0 loops=1)

  • Hash Cond: (f.feature_id = p.feature_id)
5. 47.181 52.802 ↓ 0.0 0 1

Bitmap Heap Scan on feature f (cost=1,325.54..114,269.82 rows=358 width=8) (actual time=52.802..52.802 rows=0 loops=1)

  • Recheck Cond: (domain_subclass_id = 394)
  • Filter: ((((attribute_tags -> 'attributeTags'::text) -> 'evidenceproduct'::text) ->> 'status'::text) = 'released'::text)
  • Rows Removed by Filter: 74123
  • Heap Blocks: exact=11614
6. 5.621 5.621 ↓ 1.0 74,123 1

Bitmap Index Scan on feature_domain_subclass_id (cost=0.00..1,325.45 rows=71,603 width=0) (actual time=5.621..5.621 rows=74,123 loops=1)

  • Index Cond: (domain_subclass_id = 394)
7. 0.000 0.000 ↓ 0.0 0

Hash (cost=1,246,199.29..1,246,199.29 rows=47,218 width=8) (never executed)

8. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=1,245,254.93..1,245,727.11 rows=47,218 width=8) (never executed)

  • Group Key: p.feature_id
9. 0.000 0.000 ↓ 0.0 0

Append (cost=83,863.19..1,245,136.88 rows=47,218 width=8) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=83,863.19..755,530.13 rows=37,303 width=8) (never executed)

  • Hash Cond: (p.feature_id = fd.feature_id)
11. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on point p (cost=12,361.21..676,477.81 rows=544,617 width=8) (never executed)

  • Recheck Cond: (domain_class_id = 44)
  • Filter: (modification_time > '1970-01-01 01:00:00'::timestamp without time zone)
12. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on point_domain_class_id_index (cost=0.00..12,225.06 rows=544,617 width=0) (never executed)

  • Index Cond: (domain_class_id = 44)
13. 0.000 0.000 ↓ 0.0 0

Hash (cost=67,811.38..67,811.38 rows=224,928 width=8) (never executed)

14. 0.000 0.000 ↓ 0.0 0

Hash Semi Join (cost=0.16..67,811.38 rows=224,928 width=8) (never executed)

  • Hash Cond: (fd.dataset_id = datasets_for_countries.dataset_id)
15. 0.000 0.000 ↓ 0.0 0

Seq Scan on feature_datasets fd (cost=0.00..56,688.52 rows=3,283,952 width=16) (never executed)

16. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.10..0.10 rows=5 width=8) (never executed)

17. 0.000 0.000 ↓ 0.0 0

CTE Scan on datasets_for_countries (cost=0.00..0.10 rows=5 width=8) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=86,682.32..489,134.57 rows=9,915 width=8) (never executed)

  • Join Filter: (oe.evd_feature_id = p_1.feature_id)
19. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=86,681.89..139,897.65 rows=59,704 width=16) (never executed)

  • Hash Cond: (oe.evd_feature_id = fd_1.feature_id)
20. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on observation_evidence oe (cost=15,179.91..56,840.85 rows=871,675 width=8) (never executed)

  • Recheck Cond: (modification_time > '1970-01-01 01:00:00+01'::timestamp with time zone)
21. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on observation_evidence_modification_time (cost=0.00..14,961.99 rows=871,675 width=0) (never executed)

  • Index Cond: (modification_time > '1970-01-01 01:00:00+01'::timestamp with time zone)
22. 0.000 0.000 ↓ 0.0 0

Hash (cost=67,811.38..67,811.38 rows=224,928 width=8) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Hash Semi Join (cost=0.16..67,811.38 rows=224,928 width=8) (never executed)

  • Hash Cond: (fd_1.dataset_id = datasets_for_countries_1.dataset_id)
24. 0.000 0.000 ↓ 0.0 0

Seq Scan on feature_datasets fd_1 (cost=0.00..56,688.52 rows=3,283,952 width=16) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.10..0.10 rows=5 width=8) (never executed)

26. 0.000 0.000 ↓ 0.0 0

CTE Scan on datasets_for_countries datasets_for_countries_1 (cost=0.00..0.10 rows=5 width=8) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_point on point p_1 (cost=0.43..5.84 rows=1 width=8) (never executed)

  • Index Cond: (feature_id = fd_1.feature_id)
  • Filter: (domain_class_id = 44)
Planning time : 1.053 ms
Execution time : 52.992 ms