explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yb5J

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.026 15,032.898 ↓ 0.0 0 1

Sort (cost=1,447,977.09..1,447,977.10 rows=5 width=8) (actual time=15,032.898..15,032.898 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.002 15,032.872 ↓ 0.0 0 1

Hash Join (cost=1,246,789.51..1,447,953.65 rows=5 width=8) (actual time=15,032.872..15,032.872 rows=0 loops=1)

  • Hash Cond: (f.feature_id = p.feature_id)
5. 15,032.870 15,032.870 ↓ 0.0 0 1

Seq Scan on feature f (cost=0.00..199,050.10 rows=358 width=8) (actual time=15,032.870..15,032.870 rows=0 loops=1)

  • Filter: ((domain_subclass_id = 394) AND ((((attribute_tags -> 'attributeTags'::text) -> 'evidenceproduct'::text) ->> 'status'::text) = 'released'::text))
  • Rows Removed by Filter: 3279508
6. 0.000 0.000 ↓ 0.0 0

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

7. 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
8. 0.000 0.000 ↓ 0.0 0

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

9. 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)
10. 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)
11. 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)
12. 0.000 0.000 ↓ 0.0 0

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

13. 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)
14. 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)

15. 0.000 0.000 ↓ 0.0 0

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

16. 0.000 0.000 ↓ 0.0 0

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

17. 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)
18. 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)
19. 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)
20. 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)
21. 0.000 0.000 ↓ 0.0 0

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

22. 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)
23. 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)

24. 0.000 0.000 ↓ 0.0 0

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

25. 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)

26. 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 : 17.916 ms
Execution time : 15,033.440 ms