explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OvXj : v1

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 213.506 ↑ 1.0 1 1

Sort (cost=98,162.88..98,162.88 rows=1 width=689) (actual time=213.506..213.506 rows=1 loops=1)

  • Sort Key: cmmparty.party_code_, cmmregdoc.frame_no_
  • Sort Method: quicksort Memory: 25kB
2.          

CTE mddealer

3. 0.367 3.598 ↓ 2.2 614 1

Hash Join (cost=188.15..538.08 rows=277 width=6) (actual time=2.189..3.598 rows=614 loops=1)

  • Hash Cond: ((relation.from_organization_id_)::text = (mst.organization_id_)::text)
4. 1.381 1.381 ↓ 1.1 614 1

Seq Scan on cmm_party_relation relation (cost=0.00..342.82 rows=579 width=36) (actual time=0.322..1.381 rows=614 loops=1)

  • Filter: ((party_relation_type_id_)::text = 'C028DEALERUNITMAINDEALER'::text)
  • Rows Removed by Filter: 6664
5. 0.768 1.850 ↑ 1.0 3,528 1

Hash (cost=142.51..142.51 rows=3,651 width=42) (actual time=1.850..1.850 rows=3,528 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 262kB
6. 1.082 1.082 ↑ 1.0 3,528 1

Seq Scan on cmm_site_mst mst (cost=0.00..142.51 rows=3,651 width=42) (actual time=0.003..1.082 rows=3,528 loops=1)

7. 0.005 213.488 ↑ 1.0 1 1

Nested Loop (cost=50.88..97,624.78 rows=1 width=689) (actual time=212.399..213.488 rows=1 loops=1)

8. 0.006 213.478 ↑ 1.0 1 1

Nested Loop (cost=50.74..97,624.60 rows=1 width=209) (actual time=212.390..213.478 rows=1 loops=1)

9. 0.015 213.463 ↑ 1.0 1 1

Nested Loop (cost=50.32..97,620.16 rows=1 width=210) (actual time=212.375..213.463 rows=1 loops=1)

  • Join Filter: ((cmmregdoc.organization_id_)::text = (cmmorginfo.party_id_)::text)
10. 0.970 213.439 ↑ 1.0 1 1

Nested Loop (cost=50.04..97,619.70 rows=1 width=266) (actual time=212.353..213.439 rows=1 loops=1)

  • Join Filter: ((cmmregdoc.organization_id_)::text = (cmmparty.party_id_)::text)
  • Rows Removed by Join Filter: 3976
11. 1.688 1.688 ↑ 1.0 3,977 1

Index Scan using cmm_party_info_pkey on cmm_party_info cmmparty (cost=0.28..292.36 rows=4,020 width=43) (actual time=0.011..1.688 rows=3,977 loops=1)

12. 0.585 210.781 ↑ 1.0 1 3,977

Materialize (cost=49.76..97,267.04 rows=1 width=223) (actual time=0.012..0.053 rows=1 loops=3,977)

13. 0.011 210.196 ↑ 1.0 1 1

Nested Loop (cost=49.76..97,267.04 rows=1 width=223) (actual time=47.094..210.196 rows=1 loops=1)

14. 1.056 209.735 ↓ 75.0 75 1

Nested Loop (cost=49.33..97,262.57 rows=1 width=223) (actual time=11.502..209.735 rows=75 loops=1)

  • Join Filter: ((mddealer.site_code_)::text = (cmmregdoc.site_id_)::text)
15. 0.925 6.059 ↓ 2.2 614 1

Merge Join (cost=17.06..220.12 rows=277 width=140) (actual time=4.596..6.059 rows=614 loops=1)

  • Merge Cond: ((cmmsitemst.site_id_)::text = (mddealer.site_code_)::text)
16. 0.978 0.978 ↑ 1.0 3,528 1

Index Scan using cmm_site_mst_idx01 on cmm_site_mst cmmsitemst (cost=0.28..190.06 rows=3,651 width=42) (actual time=0.009..0.978 rows=3,528 loops=1)

17. 0.381 4.156 ↓ 2.2 614 1

Sort (cost=16.78..17.47 rows=277 width=98) (actual time=4.041..4.156 rows=614 loops=1)

  • Sort Key: mddealer.site_code_
  • Sort Method: quicksort Memory: 53kB
18. 3.775 3.775 ↓ 2.2 614 1

CTE Scan on mddealer (cost=0.00..5.54 rows=277 width=98) (actual time=2.191..3.775 rows=614 loops=1)

19. 173.148 202.620 ↓ 0.0 0 614

Bitmap Heap Scan on cmm_registration_document cmmregdoc (cost=32.28..350.32 rows=1 width=194) (actual time=0.317..0.330 rows=0 loops=614)

  • Recheck Cond: ((site_id_)::text = (cmmsitemst.site_id_)::text)
  • Filter: (((request_faktur_date_)::text >= '20191005'::text) AND ((request_faktur_date_)::text <= '20191105'::text))
  • Rows Removed by Filter: 237
  • Heap Blocks: exact=116549
20. 29.472 29.472 ↑ 12.3 237 614

Bitmap Index Scan on cmm_registration_doc_idx02 (cost=0.00..32.28 rows=2,911 width=0) (actual time=0.048..0.048 rows=237 loops=614)

  • Index Cond: ((site_id_)::text = (cmmsitemst.site_id_)::text)
21. 0.450 0.450 ↓ 0.0 0 75

Index Scan using rrr on cmm_faktur_nik_document fk (cost=0.43..4.46 rows=1 width=24) (actual time=0.006..0.006 rows=0 loops=75)

  • Index Cond: ((registration_document_id_)::text = (cmmregdoc.registration_document_id_)::text)
  • Filter: ((faktur_print_times_ > 1) AND ((status_)::text = 'C176WAITINGFORALLOCAPPROVE'::text))
  • Rows Removed by Filter: 1
22. 0.009 0.009 ↑ 1.0 1 1

Index Scan using pk_cmm_organization_info on cmm_organization_info cmmorginfo (cost=0.28..0.44 rows=1 width=52) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: ((party_id_)::text = (cmmparty.party_id_)::text)
23. 0.009 0.009 ↑ 1.0 1 1

Index Scan using pk_product_add on cmm_product cmmpro (cost=0.42..4.44 rows=1 width=73) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: ((product_id_)::text = (cmmregdoc.product_id_)::text)
24. 0.005 0.005 ↑ 1.0 1 1

Index Scan using cmm_area_mst_pkey on cmm_area_mst cmmareamst (cost=0.14..0.17 rows=1 width=614) (actual time=0.004..0.005 rows=1 loops=1)

  • Index Cond: ((geography_id_)::text = (cmmsitemst.area_id_)::text)
Planning time : 3.927 ms
Execution time : 213.786 ms