explain.depesz.com

PostgreSQL's explain analyze made readable

Result: W341 : Optimization for: Optimization for: plan #yb5J; plan #815

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.002 3,275.078 ↓ 0.0 0 1

Merge Join (cost=1,252,616.27..1,252,854.20 rows=5 width=8) (actual time=3,275.078..3,275.078 rows=0 loops=1)

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

CTE datasets_for_countries

3. 0.055 0.055 ↑ 5.0 1 1

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

  • Filter: (country_id = 113)
  • Rows Removed by Filter: 555
4. 0.005 3,275.076 ↓ 0.0 0 1

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

  • Sort Key: p.feature_id
  • Sort Method: quicksort Memory: 25kB
5. 0.093 3,275.071 ↓ 0.0 0 1

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

  • Group Key: p.feature_id
6. 0.002 3,274.978 ↓ 0.0 0 1

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

7. 0.159 1,599.142 ↓ 0.0 0 1

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

  • Hash Cond: (p.feature_id = fd.feature_id)
8. 7.442 81.380 ↑ 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=81.380..81.380 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. 73.938 73.938 ↓ 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=73.938..73.938 rows=569,755 loops=1)

  • Index Cond: (domain_class_id = 44)
10. 0.001 1,517.603 ↓ 0.0 0 1

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

  • Buckets: 131072 Batches: 4 Memory Usage: 1024kB
11. 774.333 1,517.602 ↓ 0.0 0 1

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

  • Hash Cond: (fd.dataset_id = datasets_for_countries.dataset_id)
12. 743.209 743.209 ↑ 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.007..743.209 rows=3,279,508 loops=1)

13. 0.003 0.060 ↑ 5.0 1 1

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

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

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

15. 0.002 1,675.834 ↓ 0.0 0 1

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

  • Join Filter: (oe.evd_feature_id = p_1.feature_id)
16. 0.155 1,675.832 ↓ 0.0 0 1

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

  • Hash Cond: (oe.evd_feature_id = fd_1.feature_id)
17. 5.795 155.540 ↑ 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=155.540..155.540 rows=1 loops=1)

  • Recheck Cond: (modification_time > '1970-01-01 01:00:00+01'::timestamp with time zone)
  • Heap Blocks: exact=1
18. 149.745 149.745 ↓ 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=149.745..149.745 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.137 ↓ 0.0 0 1

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

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

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

  • Hash Cond: (fd_1.dataset_id = datasets_for_countries_1.dataset_id)
21. 744.875 744.875 ↑ 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.006..744.875 rows=3,279,508 loops=1)

22. 0.005 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.002 0.002 ↑ 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.002 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=2,727.83..2,728.72 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,330.01..2,712.64 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,330.01..1,330.01 rows=358 width=0) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on feature_evp_tags (cost=0.00..4.13 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 : 1.133 ms
Execution time : 3,275.275 ms