explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IN9bz

Settings
# exclusive inclusive rows x rows loops node
1. 1,148.731 467,836.627 ↓ 21.1 191,047 1

Nested Loop Left Join (cost=345,386.13..780,985.54 rows=9,041 width=195) (actual time=49,453.783..467,836.627 rows=191,047 loops=1)

2. 196.603 259,019.807 ↓ 21.1 191,047 1

Nested Loop (cost=345,385.69..713,001.17 rows=9,041 width=73) (actual time=49,448.092..259,019.807 rows=191,047 loops=1)

3. 364.453 67,897.204 ↓ 12.2 212,140 1

Hash Join (cost=345,385.13..675,041.23 rows=17,413 width=79) (actual time=49,444.115..67,897.204 rows=212,140 loops=1)

  • Hash Cond: (prek.stotv_id = prek_stotv.stotv_id)
4. 18,096.316 67,532.468 ↑ 1.5 212,140 1

Bitmap Heap Scan on docs_post prek (cost=345,283.90..673,544.20 rows=325,776 width=71) (actual time=49,443.784..67,532.468 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,557,776
  • Filter: (doc_state = 2)
  • Rows Removed by Filter: 5,753
  • Heap Blocks: exact=28,106 lossy=225,497
5. 33.414 49,436.152 ↓ 0.0 0 1

BitmapAnd (cost=345,283.90..345,283.90 rows=325,993 width=0) (actual time=49,436.152..49,436.152 rows=0 loops=1)

6. 671.625 671.625 ↓ 1.0 883,989 1

Bitmap Index Scan on docs_post_idx2 (cost=0.00..11,353.04 rows=855,003 width=0) (actual time=671.625..671.625 rows=883,989 loops=1)

7. 48,731.113 48,731.113 ↑ 1.0 5,938,803 1

Bitmap Index Scan on docs_post_idx1 (cost=0.00..333,767.72 rows=6,010,944 width=0) (actual time=48,731.113..48,731.113 rows=5,938,803 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.007 0.283 ↑ 1.0 24 1

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

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

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

10. 0.225 0.225 ↑ 1.0 24 1

Seq Scan on s_stotv (cost=0.00..63.73 rows=24 width=5) (actual time=0.011..0.225 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. 190,926.000 190,926.000 ↑ 1.0 1 212,140

Index Scan using delo_pkey on delo (cost=0.56..2.17 rows=1 width=6) (actual time=0.899..0.900 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. 30,758.567 30,758.567 ↑ 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.161..0.161 rows=1 loops=191,047)

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

SubPlan (for Nested Loop Left Join)

15. 174,808.005 174,808.005 ↑ 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.914..0.915 rows=1 loops=191,047)

  • Index Cond: (docs_post_id = post.docs_id)
16. 2,101.517 2,101.517 ↑ 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.011..0.011 rows=1 loops=191,047)

  • Index Cond: (stotv_id = post.stotv_id)
Planning time : 2.597 ms
Execution time : 467,877.884 ms