explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l12J

Settings
# exclusive inclusive rows x rows loops node
1. 997.831 459,966.999 ↓ 21.1 191,047 1

Nested Loop Left Join (cost=345,382.39..781,002.02 rows=9,041 width=195) (actual time=54,678.727..459,966.999 rows=191,047 loops=1)

2. 161.646 254,930.972 ↓ 21.1 191,047 1

Nested Loop (cost=345,381.95..712,995.06 rows=9,041 width=73) (actual time=54,676.923..254,930.972 rows=191,047 loops=1)

3. 358.073 75,511.026 ↓ 12.2 212,140 1

Hash Join (cost=345,381.39..675,035.19 rows=17,413 width=79) (actual time=54,675.808..75,511.026 rows=212,140 loops=1)

  • Hash Cond: (prek.stotv_id = prek_stotv.stotv_id)
4. 20,498.008 75,152.666 ↑ 1.5 212,140 1

Bitmap Heap Scan on docs_post prek (cost=345,280.16..673,538.17 rows=325,773 width=71) (actual time=54,675.495..75,152.666 rows=212,140 loops=1)

  • Recheck Cond: ((docs_post_main_id IS NOT NULL) AND (dat_sost >= date_trunc('year'::text, (('now'::cstring)::date - '1 year'::interval))) AND (dat_sost <= (('now'::cstring)::date - '1 day'::interval)))
  • Rows Removed by Index Recheck: 2,558,145
  • Filter: (doc_state = 2)
  • Rows Removed by Filter: 5,753
  • Heap Blocks: exact=28,106 lossy=225,528
5. 32.326 54,654.658 ↓ 0.0 0 1

BitmapAnd (cost=345,280.16..345,280.16 rows=325,991 width=0) (actual time=54,654.658..54,654.658 rows=0 loops=1)

6. 548.103 548.103 ↓ 1.0 883,989 1

Bitmap Index Scan on docs_post_idx2 (cost=0.00..11,353.00 rows=854,996 width=0) (actual time=548.103..548.103 rows=883,989 loops=1)

7. 54,074.229 54,074.229 ↑ 1.0 5,939,103 1

Bitmap Index Scan on docs_post_idx1 (cost=0.00..333,764.02 rows=6,010,894 width=0) (actual time=54,074.229..54,074.229 rows=5,939,103 loops=1)

  • Index Cond: ((dat_sost >= date_trunc('year'::text, (('now'::cstring)::date - '1 year'::interval))) AND (dat_sost <= (('now'::cstring)::date - '1 day'::interval)))
8. 0.010 0.287 ↑ 1.0 24 1

Hash (cost=100.93..100.93 rows=24 width=25) (actual time=0.287..0.287 rows=24 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
9. 0.003 0.277 ↑ 1.0 24 1

Nested Loop (cost=0.27..100.93 rows=24 width=25) (actual time=0.016..0.277 rows=24 loops=1)

10. 0.226 0.226 ↑ 1.0 24 1

Seq Scan on s_stotv (cost=0.00..63.73 rows=24 width=5) (actual time=0.012..0.226 rows=24 loops=1)

  • Filter: (((priz_prekr)::text = '+'::text) OR ((priz_prekr_ispoln)::text = '+'::text))
  • Rows Removed by Filter: 424
11. 0.048 0.048 ↑ 1.0 1 24

Index Scan using s_stotv_pkey on s_stotv prek_stotv (cost=0.27..1.54 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=24)

  • Index Cond: (stotv_id = s_stotv.stotv_id)
12. 179,258.300 179,258.300 ↑ 1.0 1 212,140

Index Scan using delo_pkey on delo (cost=0.56..2.17 rows=1 width=6) (actual time=0.843..0.845 rows=1 loops=212,140)

  • Index Cond: (delo_id = prek.delo_id)
  • Filter: ((ogai_nah_kod)::text = '45597'::text)
  • Rows Removed by Filter: 0
13. 31,713.802 31,713.802 ↑ 1.0 1 191,047

Index Scan using docs_post_pkey on docs_post post (cost=0.43..1.84 rows=1 width=46) (actual time=0.166..0.166 rows=1 loops=191,047)

  • Index Cond: (docs_id = prek.docs_post_main_id)
14.          

SubPlan (for Nested Loop Left Join)

15. 170,413.924 170,413.924 ↑ 1.0 1 191,047

Index Scan using decis_docs_post_id on decis (cost=0.56..2.98 rows=1 width=5) (actual time=0.891..0.892 rows=1 loops=191,047)

  • Index Cond: (docs_post_id = post.docs_id)
  • Filter: (decis_kod = '65'::numeric)
  • Rows Removed by Filter: 0
16. 1,910.470 1,910.470 ↑ 1.0 1 191,047

Index Scan using s_stotv_pkey on s_stotv stotv (cost=0.27..2.69 rows=1 width=15) (actual time=0.010..0.010 rows=1 loops=191,047)

  • Index Cond: (stotv_id = post.stotv_id)
Planning time : 0.997 ms
Execution time : 460,005.910 ms