explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DfuI : Optimization for: Optimization for: Without index; plan #IMtM; plan #gNOm After Vacuum

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 2,114.747 ↑ 1.0 200 1

Limit (cost=118,777.74..122,206.78 rows=200 width=61) (actual time=2,107.674..2,114.747 rows=200 loops=1)

2.          

Initplan (for Limit)

3. 0.000 2,107.617 ↑ 1.0 1 1

Limit (cost=118,776.90..118,777.02 rows=1 width=4) (actual time=2,107.615..2,107.617 rows=1 loops=1)

4. 15.723 2,110.127 ↑ 3,407,566.0 1 1

Gather Merge (cost=118,776.90..516,354.03 rows=3,407,566 width=4) (actual time=2,107.613..2,110.127 rows=1 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 941.050 2,094.404 ↑ 1,703,783.0 1 3 / 3

Sort (cost=117,776.88..122,036.34 rows=1,703,783 width=4) (actual time=2,094.404..2,094.404 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,153.354 1,153.354 ↑ 1.3 1,355,437 3 / 3

Parallel Seq Scan on fact_monthly (cost=0.00..109,257.96 rows=1,703,783 width=4) (actual time=0.022..1,153.354 rows=1,355,437 loops=3)

  • Filter: (model_id = ANY ('{2,3}'::integer[]))
  • Rows Removed by Filter: 5330
7. 2,108.135 2,114.487 ↑ 102.2 200 1

Nested Loop (cost=0.72..350,533.98 rows=20,445 width=61) (actual time=2,107.672..2,114.487 rows=200 loops=1)

8. 0.537 6.152 ↑ 102.2 200 1

Nested Loop (cost=0.57..347,096.72 rows=20,445 width=29) (actual time=0.044..6.152 rows=200 loops=1)

9. 1.076 5.415 ↑ 102.2 200 1

Subquery Scan on cte (cost=0.43..343,824.24 rows=20,445 width=18) (actual time=0.033..5.415 rows=200 loops=1)

  • Filter: (cte.rank = 1)
  • Rows Removed by Filter: 1022
10. 2.740 4.339 ↑ 3,346.2 1,222 1

WindowAgg (cost=0.43..292,710.75 rows=4,089,079 width=34) (actual time=0.031..4.339 rows=1,222 loops=1)

11. 1.599 1.599 ↑ 3,343.5 1,223 1

Index Scan using idx_mdl_fact_monthly_sort on fact_monthly fm (cost=0.43..190,483.77 rows=4,089,079 width=26) (actual time=0.018..1.599 rows=1,223 loops=1)

  • Filter: (model_id = ANY ('{2,3}'::integer[]))
12. 0.200 0.200 ↑ 1.0 1 200

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

  • Index Cond: (thing_id = cte.thing_id)
13. 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.357 ms
Execution time : 2,117.760 ms