explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YzV : Optimization for: Optimization for: plan #yb5J; plan #swnB

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.002 5,522.274 ↓ 0.0 0 1

Merge Join (cost=1,252,915.55..1,253,153.48 rows=5 width=8) (actual time=5,522.274..5,522.274 rows=0 loops=1)

  • Merge Cond: (p.feature_id = f.feature_id)
2.          

CTE datasets_for_countries

3. 0.058 0.058 ↑ 5.0 1 1

Seq Scan on dataset (cost=0.00..23.38 rows=5 width=8) (actual time=0.029..0.058 rows=1 loops=1)

  • Filter: (country_id = 113)
  • Rows Removed by Filter: 555
4. 0.008 5,522.272 ↓ 0.0 0 1

Sort (cost=1,249,865.07..1,249,983.11 rows=47,218 width=8) (actual time=5,522.272..5,522.272 rows=0 loops=1)

  • Sort Key: p.feature_id
  • Sort Method: quicksort Memory: 25kB
5. 0.082 5,522.264 ↓ 0.0 0 1

HashAggregate (cost=1,245,254.93..1,245,727.11 rows=47,218 width=8) (actual time=5,522.264..5,522.264 rows=0 loops=1)

  • Group Key: p.feature_id
6. 0.003 5,522.182 ↓ 0.0 0 1

Append (cost=83,863.19..1,245,136.88 rows=47,218 width=8) (actual time=5,522.182..5,522.182 rows=0 loops=1)

7. 0.524 3,176.852 ↓ 0.0 0 1

Hash Join (cost=83,863.19..755,530.13 rows=37,303 width=8) (actual time=3,176.852..3,176.852 rows=0 loops=1)

  • Hash Cond: (p.feature_id = fd.feature_id)
8. 9.781 1,121.449 ↑ 544,617.0 1 1

Bitmap Heap Scan on point p (cost=12,361.21..676,477.81 rows=544,617 width=8) (actual time=1,121.449..1,121.449 rows=1 loops=1)

  • Recheck Cond: (domain_class_id = 44)
  • Filter: (modification_time > '1970-01-01 01:00:00'::timestamp without time zone)
  • Heap Blocks: exact=1
9. 1,111.668 1,111.668 ↓ 1.0 569,755 1

Bitmap Index Scan on point_domain_class_id_index (cost=0.00..12,225.06 rows=544,617 width=0) (actual time=1,111.668..1,111.668 rows=569,755 loops=1)

  • Index Cond: (domain_class_id = 44)
10. 0.002 2,054.879 ↓ 0.0 0 1

Hash (cost=67,811.38..67,811.38 rows=224,928 width=8) (actual time=2,054.879..2,054.879 rows=0 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 1024kB
11. 784.259 2,054.877 ↓ 0.0 0 1

Hash Semi Join (cost=0.16..67,811.38 rows=224,928 width=8) (actual time=2,054.877..2,054.877 rows=0 loops=1)

  • Hash Cond: (fd.dataset_id = datasets_for_countries.dataset_id)
12. 1,270.553 1,270.553 ↑ 1.0 3,279,508 1

Seq Scan on feature_datasets fd (cost=0.00..56,688.52 rows=3,283,952 width=16) (actual time=0.008..1,270.553 rows=3,279,508 loops=1)

13. 0.003 0.065 ↑ 5.0 1 1

Hash (cost=0.10..0.10 rows=5 width=8) (actual time=0.065..0.065 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.062 0.062 ↑ 5.0 1 1

CTE Scan on datasets_for_countries (cost=0.00..0.10 rows=5 width=8) (actual time=0.033..0.062 rows=1 loops=1)

15. 0.002 2,345.327 ↓ 0.0 0 1

Nested Loop (cost=86,682.32..489,134.57 rows=9,915 width=8) (actual time=2,345.327..2,345.327 rows=0 loops=1)

  • Join Filter: (oe.evd_feature_id = p_1.feature_id)
16. 0.529 2,345.325 ↓ 0.0 0 1

Hash Join (cost=86,681.89..139,897.65 rows=59,704 width=16) (actual time=2,345.325..2,345.325 rows=0 loops=1)

  • Hash Cond: (oe.evd_feature_id = fd_1.feature_id)
17. 7.421 824.149 ↑ 871,675.0 1 1

Bitmap Heap Scan on observation_evidence oe (cost=15,179.91..56,840.85 rows=871,675 width=8) (actual time=824.149..824.149 rows=1 loops=1)

  • Recheck Cond: (modification_time > '1970-01-01 01:00:00+01'::timestamp with time zone)
  • Heap Blocks: exact=1
18. 816.728 816.728 ↓ 2.6 2,303,519 1

Bitmap Index Scan on observation_evidence_modification_time (cost=0.00..14,961.99 rows=871,675 width=0) (actual time=816.728..816.728 rows=2,303,519 loops=1)

  • Index Cond: (modification_time > '1970-01-01 01:00:00+01'::timestamp with time zone)
19. 0.002 1,520.647 ↓ 0.0 0 1

Hash (cost=67,811.38..67,811.38 rows=224,928 width=8) (actual time=1,520.647..1,520.647 rows=0 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 1024kB
20. 775.576 1,520.645 ↓ 0.0 0 1

Hash Semi Join (cost=0.16..67,811.38 rows=224,928 width=8) (actual time=1,520.645..1,520.645 rows=0 loops=1)

  • Hash Cond: (fd_1.dataset_id = datasets_for_countries_1.dataset_id)
21. 745.062 745.062 ↑ 1.0 3,279,508 1

Seq Scan on feature_datasets fd_1 (cost=0.00..56,688.52 rows=3,283,952 width=16) (actual time=0.009..745.062 rows=3,279,508 loops=1)

22. 0.004 0.007 ↑ 5.0 1 1

Hash (cost=0.10..0.10 rows=5 width=8) (actual time=0.007..0.007 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.003 0.003 ↑ 5.0 1 1

CTE Scan on datasets_for_countries datasets_for_countries_1 (cost=0.00..0.10 rows=5 width=8) (actual time=0.002..0.003 rows=1 loops=1)

24. 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)
25. 0.000 0.000 ↓ 0.0 0

Sort (cost=3,027.11..3,028.00 rows=358 width=8) (never executed)

  • Sort Key: f.feature_id
26. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on feature f (cost=1,629.30..3,011.92 rows=358 width=8) (never executed)

  • Recheck Cond: (((((attribute_tags -> 'attributeTags'::text) -> 'evidenceproduct'::text) ->> 'status'::text) = 'released'::text) AND (domain_subclass_id = 394))
27. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=1,629.30..1,629.30 rows=358 width=0) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on feature_evp_tags (cost=0.00..303.42 rows=16,398 width=0) (never executed)

  • Index Cond: ((((attribute_tags -> 'attributeTags'::text) -> 'evidenceproduct'::text) ->> 'status'::text) = 'released'::text)
29. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on feature_domain_subclass_id (cost=0.00..1,325.45 rows=71,603 width=0) (never executed)

  • Index Cond: (domain_subclass_id = 394)
Planning time : 3.243 ms
Execution time : 5,522.695 ms