explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SYJY

Settings
# exclusive inclusive rows x rows loops node
1. 196.849 468,914.862 ↑ 7.8 100 1

Nested Loop (cost=114,201.42..388,190.97 rows=783 width=2,516) (actual time=468,523.242..468,914.862 rows=100 loops=1)

2. 0.203 468,502.213 ↑ 7.8 100 1

HashAggregate (cost=114,200.99..114,208.82 rows=783 width=4) (actual time=468,502.086..468,502.213 rows=100 loops=1)

  • Group Key: e0.archive_id
3. 0.106 468,502.010 ↑ 7.8 100 1

Nested Loop (cost=0.86..114,199.03 rows=783 width=4) (actual time=468,475.602..468,502.010 rows=100 loops=1)

4. 0.018 468,497.904 ↑ 1.0 100 1

Limit (cost=0.43..110,534.40 rows=100 width=18) (actual time=468,474.701..468,497.904 rows=100 loops=1)

5. 0.057 468,497.886 ↑ 14.2 100 1

Group (cost=0.43..1,567,372.13 rows=1,418 width=18) (actual time=468,474.700..468,497.886 rows=100 loops=1)

  • Group Key: e00.works_ref
6. 0.288 468,497.829 ↑ 14.2 100 1

Nested Loop (cost=0.43..1,567,368.59 rows=1,418 width=18) (actual time=468,474.698..468,497.829 rows=100 loops=1)

  • Join Filter: ((e00.entity_category)::text = (entity_category.nsa_code)::text)
  • Rows Removed by Join Filter: 1656
7. 468,494.493 468,494.493 ↑ 591.9 127 1

Index Scan Backward using idx_entity_raw_works_ref on entity_raw e00 (cost=0.43..1,566,230.57 rows=75,168 width=26) (actual time=468,429.052..468,494.493 rows=127 loops=1)

  • Filter: (entity_type = ANY ('{4,5}'::integer[]))
  • Rows Removed by Filter: 1098912
8. 0.218 3.048 ↓ 14.0 14 127

Materialize (cost=0.00..10.51 rows=1 width=118) (actual time=0.012..0.024 rows=14 loops=127)

9. 2.830 2.830 ↓ 24.0 24 1

Seq Scan on entity_category (cost=0.00..10.50 rows=1 width=118) (actual time=1.453..2.830 rows=24 loops=1)

  • Filter: ((entitygroupid)::text = 'INC'::text)
  • Rows Removed by Filter: 69
10. 4.000 4.000 ↑ 8.0 1 100

Index Scan using idx_entity_raw_works_ref on entity_raw e0 (cost=0.43..36.56 rows=8 width=22) (actual time=0.040..0.040 rows=1 loops=100)

  • Index Cond: ((works_ref)::text = (e00.works_ref)::text)
11. 14.700 14.700 ↑ 1.0 1 100

Index Scan using idx_entity_raw_archive_id on entity_raw e (cost=0.43..0.83 rows=1 width=2,498) (actual time=0.146..0.147 rows=1 loops=100)

  • Index Cond: (archive_id = e0.archive_id)
12.          

SubPlan (forNested Loop)

13. 0.600 167.900 ↑ 84.0 1 100

GroupAggregate (cost=0.57..340.37 rows=84 width=16) (actual time=1.679..1.679 rows=1 loops=100)

  • Group Key: csl.entity_id
14. 167.300 167.300 ↑ 16.8 5 100

Index Scan using idx_log_daily_agg_by_worksref_entity_id on log_daily_agg_by_worksref csl (cost=0.57..339.11 rows=84 width=12) (actual time=1.072..1.673 rows=5 loops=100)

  • Index Cond: (entity_id = e.entity_id)
15. 0.100 33.200 ↑ 1.0 1 100

Aggregate (cost=8.44..8.45 rows=1 width=2) (actual time=0.332..0.332 rows=1 loops=100)

16. 33.100 33.100 ↓ 0.0 0 100

Index Scan using tomtom_max_impact_xref_entity_id_pkey on tomtom_max_impact_xref_entity_id tt (cost=0.42..8.44 rows=1 width=2) (actual time=0.331..0.331 rows=0 loops=100)

  • Index Cond: (entity_id = e.entity_id)
Planning time : 2.716 ms
Execution time : 468,916.080 ms