explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IMtM : Without index

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 11,223.386 ↑ 1.0 200 1

Limit (cost=817,792.40..819,458.05 rows=200 width=61) (actual time=11,212.101..11,223.386 rows=200 loops=1)

2.          

Initplan (for Limit)

3. 0.000 2,147.513 ↑ 1.0 1 1

Limit (cost=118,605.49..118,605.60 rows=1 width=4) (actual time=2,147.511..2,147.513 rows=1 loops=1)

4. 11.174 2,147.564 ↑ 3,388,196.0 1 1

Gather Merge (cost=118,605.49..513,922.62 rows=3,388,196 width=4) (actual time=2,147.508..2,147.564 rows=1 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 958.887 2,136.390 ↑ 1,694,098.0 1 3 / 3

Sort (cost=117,605.46..121,840.71 rows=1,694,098 width=4) (actual time=2,136.389..2,136.390 rows=1 loops=3)

  • Sort Key: fact_monthly.report_date DESC
  • Sort Method: top-N heapsort Memory: 25kB
  • Worker 0: Sort Method: top-N heapsort Memory: 25kB
  • Worker 1: Sort Method: top-N heapsort Memory: 25kB
6. 1,177.503 1,177.503 ↑ 1.2 1,355,437 3 / 3

Parallel Seq Scan on fact_monthly (cost=0.00..109,134.97 rows=1,694,098 width=4) (actual time=0.019..1,177.503 rows=1,355,437 loops=3)

  • Filter: (model_id = ANY ('{2,3}'::integer[]))
  • Rows Removed by Filter: 5330
7. 2,148.001 11,222.744 ↑ 101.6 200 1

Nested Loop (cost=699,186.79..868,491.64 rows=20,329 width=61) (actual time=11,212.099..11,222.744 rows=200 loops=1)

8. 0.369 9,074.543 ↑ 101.6 200 1

Nested Loop (cost=699,186.64..865,073.80 rows=20,329 width=29) (actual time=9,064.574..9,074.543 rows=200 loops=1)

9. 2.177 9,073.774 ↑ 101.6 200 1

Subquery Scan on cte (cost=699,186.50..861,819.86 rows=20,329 width=18) (actual time=9,064.551..9,073.774 rows=200 loops=1)

  • Filter: (cte.rank = 1)
  • Rows Removed by Filter: 1022
10. 6.022 9,071.597 ↑ 3,327.2 1,222 1

WindowAgg (cost=699,186.50..810,996.94 rows=4,065,834 width=34) (actual time=9,064.548..9,071.597 rows=1,222 loops=1)

11. 5,727.509 9,065.575 ↑ 3,324.5 1,223 1

Sort (cost=699,186.50..709,351.09 rows=4,065,834 width=26) (actual time=9,064.530..9,065.575 rows=1,223 loops=1)

  • Sort Key: fm.eff_month, fm.thing_id, fm.item_id, fm.report_date DESC, fm.model_version_id DESC
  • Sort Method: external merge Disk: 146600kB
12. 3,338.066 3,338.066 ↓ 1.0 4,066,310 1

Seq Scan on fact_monthly fm (cost=0.00..138,901.74 rows=4,065,834 width=26) (actual time=0.019..3,338.066 rows=4,066,310 loops=1)

  • Filter: (model_id = ANY ('{2,3}'::integer[]))
  • Rows Removed by Filter: 15989
13. 0.400 0.400 ↑ 1.0 1 200

Index Scan using thing_pkey on thing t (cost=0.14..0.16 rows=1 width=15) (actual time=0.002..0.002 rows=1 loops=200)

  • Index Cond: (thing_id = cte.thing_id)
14. 0.200 0.200 ↑ 1.0 1 200

Index Scan using item_pkey on item i (cost=0.15..0.17 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=200)

  • Index Cond: (item_id = cte.item_id)
Planning time : 0.353 ms
Execution time : 11,223.771 ms