explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hrtc

Settings
# exclusive inclusive rows x rows loops node
1. 1,161.641 27,889.231 ↓ 1.2 569,837 1

Gather (cost=97,606.46..760,631.7 rows=476,510 width=6,500) (actual time=2,393.42..27,889.231 rows=569,837 loops=1)

  • Buffers: shared hit=6919155 read=3131, temp read=58549 written=58620
2. 1,862.751 26,727.590 ↑ 1.0 189,946 3

Hash Join (cost=96,606.46..711,980.7 rows=198,546 width=6,500) (actual time=2,360.174..26,727.59 rows=189,946 loops=3)

  • Buffers: shared hit=6919155 read=3131, temp read=58549 written=58620
3. 1,207.886 24,864.721 ↑ 1.0 189,946 3

Hash Join (cost=96,601.03..703,491.8 rows=198,546 width=6,062) (actual time=2,360.012..24,864.721 rows=189,946 loops=3)

  • Buffers: shared hit=6919146 read=3131, temp read=58549 written=58620
4. 2,284.486 23,516.562 ↑ 1.0 189,946 3

Nested Loop (cost=92,304.02..698,163.22 rows=196,409 width=5,909) (actual time=2,218.161..23,516.562 rows=189,946 loops=3)

  • Buffers: shared hit=6918654, temp read=58549 written=58620
5. 942.124 21,232.070 ↑ 1.0 189,946 3

Hash Join (cost=92,303.6..605,531.83 rows=196,409 width=4,389) (actual time=2,218.13..21,232.07 rows=189,946 loops=3)

  • Buffers: shared hit=4639306, temp read=58549 written=58620
6. 934.375 20,247.965 ↑ 1.0 189,946 3

Hash Join (cost=81,754.45..591,892.34 rows=196,409 width=4,367) (actual time=2,175.887..20,247.965 rows=189,946 loops=3)

  • Buffers: shared hit=4633434, temp read=58549 written=58620
7. 13,112.540 19,232.085 ↑ 1.0 189,946 3

Nested Loop (cost=76,853.43..585,960.17 rows=196,409 width=4,315) (actual time=2,093.761..19,232.085 rows=189,946 loops=3)

  • Buffers: shared hit=4630951, temp read=58549 written=58620
8. 1,918.343 6,119.481 ↑ 1.0 189,946 3

Nested Loop (cost=76,853.28..551,067.58 rows=196,409 width=4,281) (actual time=2,093.433..6,119.481 rows=189,946 loops=3)

  • Buffers: shared hit=2349039, temp read=58549 written=58620
9. 602.219 4,201.132 ↑ 1.0 189,946 3

Hash Join (cost=76,852.86..458,436.18 rows=196,409 width=2,761) (actual time=2,093.392..4,201.132 rows=189,946 loops=3)

  • Buffers: shared hit=69689, temp read=58549 written=58620
10. 566.957 3,598.879 ↑ 1.0 189,946 3

Hash Join (cost=76,851.54..455,734.23 rows=196,409 width=2,656) (actual time=2,093.336..3,598.879 rows=189,946 loops=3)

  • Buffers: shared hit=69686, temp read=58549 written=58620
11. 1,366.275 3,009.083 ↑ 1.0 189,946 3

Hash Join (cost=66,534.28..402,695.85 rows=196,409 width=2,647) (actual time=2,070.405..3,009.083 rows=189,946 loops=3)

  • Buffers: shared hit=63851, temp read=58549 written=58620
12. 715.794 1,478.665 ↑ 1.0 189,946 3

Hash Join (cost=42,909.76..310,668.74 rows=196,409 width=1,127) (actual time=1,002.488..1,478.665 rows=189,946 loops=3)

  • Buffers: shared hit=54381, temp read=16992 written=17020
13. 237.878 634.589 ↑ 1.0 189,946 3

Hash Join (cost=24,225.81..269,879.2 rows=196,409 width=206) (actual time=151.351..634.589 rows=189,946 loops=3)

  • Buffers: shared hit=48187
14. 253.853 339.252 ↑ 1.0 189,946 3

Bitmap Heap Scan on fact_sale fs (cost=13,246.28..258,384.1 rows=196,409 width=205) (actual time=93.429..339.252 rows=189,946 loops=3)

  • Buffers: shared hit=38717
15. 85.399 85.399 ↓ 1.2 569,837 1

Bitmap Index Scan on fact_sale_experiment_idx (cost=0..13,128.43 rows=471,381 width=0) (actual time=85.398..85.399 rows=569,837 loops=1)

  • Index Cond: ((fs.client_sk = 11) AND (fs.sale_reporting_date >= '2017-01-01'::date))
  • Buffers: shared hit=6200
16. 22.694 57.459 ↑ 1.4 49,440 3

Hash (cost=10,140.9..10,140.9 rows=67,090 width=5) (actual time=57.459..57.459 rows=49,440 loops=3)

  • Buffers: shared hit=9470
17. 34.765 34.765 ↑ 1.4 49,440 3

Seq Scan on dim_company cmp (cost=0..10,140.9 rows=67,090 width=5) (actual time=0.009..34.765 rows=49,440 loops=3)

  • Buffers: shared hit=9470
18. 51.152 128.282 ↑ 1.6 57,752 3

Hash (cost=7,094.43..7,094.43 rows=90,042 width=921) (actual time=128.282..128.282 rows=57,752 loops=3)

  • Buffers: shared hit=6194, temp written=2216
19. 77.130 77.130 ↑ 1.6 57,752 3

Seq Scan on dim_product dim_product (cost=0..7,094.43 rows=90,042 width=921) (actual time=0.013..77.13 rows=57,752 loops=3)

  • Buffers: shared hit=6194
20. 71.563 164.143 ↑ 1.4 49,440 3

Hash (cost=10,140.9..10,140.9 rows=67,090 width=1,520) (actual time=164.143..164.143 rows=49,440 loops=3)

  • Buffers: shared hit=9470, temp written=3360
21. 92.580 92.580 ↑ 1.4 49,440 3

Seq Scan on dim_company dim_company (cost=0..10,140.9 rows=67,090 width=1,520) (actual time=0.013..92.58 rows=49,440 loops=3)

  • Buffers: shared hit=9470
22. 0.040 22.839 ↓ 0.0 0 3

Hash (cost=10,315.98..10,315.98 rows=85 width=40) (actual time=22.839..22.839 rows=0 loops=3)

  • Buffers: shared hit=5835
23. 17.583 22.799 ↓ 0.0 0 3

Bitmap Heap Scan on dim_company dim_company_plan_to (cost=578.93..10,315.98 rows=85 width=40) (actual time=22.799..22.799 rows=0 loops=3)

  • Filter: ((dim_company_plan_to.attribute_01)::text = 'PLAN'::text)
  • Buffers: shared hit=5835
24. 5.216 5.216 ↑ 1.1 39,280 1

Bitmap Index Scan on dim_company_client_sk_idx (cost=0..578.88 rows=42,728 width=0) (actual time=5.216..5.216 rows=39,280 loops=1)

  • Index Cond: (dim_company_plan_to.client_sk = 11)
  • Buffers: shared hit=114
25. 0.004 0.034 ↑ 1.0 1 3

Hash (cost=1.31..1.31 rows=1 width=105) (actual time=0.034..0.034 rows=1 loops=3)

  • Buffers: shared hit=3
26. 0.030 0.030 ↑ 1.0 1 3

Seq Scan on dim_client dim_client (cost=0..1.31 rows=1 width=105) (actual time=0.028..0.03 rows=1 loops=3)

  • Filter: (dim_client.client_sk = 11)
  • Buffers: shared hit=3
27. 0.006 0.006 ↑ 1.0 1 569,837

Index Scan using dim_company_pkey on dim_company dim_company_redistributor (cost=0.42..0.47 rows=1 width=1,520) (actual time=0.006..0.006 rows=1 loops=569,837)

  • Index Cond: (fs.redistributor_company_sk = dim_company_redistributor.company_sk)
  • Buffers: shared hit=2279350
28. 0.064 0.064 ↑ 1.0 1 569,837

Index Scan using meta_fiscal_date_range_excl on meta_fiscal meta_fiscal (cost=0.14..0.17 rows=1 width=34) (actual time=0.047..0.064 rows=1 loops=569,837)

  • Index Cond: ((fs.client_sk = meta_fiscal.client_sk) AND (meta_fiscal.client_sk = 11) AND (fs.sale_reporting_date <@ meta_fiscal.date_range))
  • Buffers: shared hit=2281912
29. 44.914 81.505 ↑ 1.8 54,808 3

Hash (cost=3,450.21..3,450.21 rows=96,721 width=60) (actual time=81.505..81.505 rows=54,808 loops=3)

  • Buffers: shared hit=2483
30. 36.591 36.591 ↑ 1.8 54,808 3

Seq Scan on fact_product_uom_conversion fpuc (cost=0..3,450.21 rows=96,721 width=60) (actual time=0.02..36.591 rows=54,808 loops=3)

  • Buffers: shared hit=2483
31. 11.770 41.981 ↑ 1.4 13,093 3

Hash (cost=10,282.1..10,282.1 rows=17,803 width=35) (actual time=41.981..41.981 rows=13,093 loops=3)

  • Buffers: shared hit=5834
32. 25.359 30.211 ↑ 1.4 13,093 3

Bitmap Heap Scan on dim_company dim_company_sell_to (cost=589.56..10,282.1 rows=17,803 width=35) (actual time=8.56..30.211 rows=13,093 loops=3)

  • Buffers: shared hit=5834
33. 4.852 4.852 ↑ 1.1 39,280 1

Bitmap Index Scan on dim_company_client_sk_idx (cost=0..578.88 rows=42,728 width=0) (actual time=4.851..4.852 rows=39,280 loops=1)

  • Index Cond: (dim_company_sell_to.client_sk = 11)
  • Buffers: shared hit=113
34. 0.006 0.006 ↑ 1.0 1 569,837

Index Scan using dim_company_pkey on dim_company dim_company_broker (cost=0.42..0.47 rows=1 width=1,520) (actual time=0.006..0.006 rows=1 loops=569,837)

  • Index Cond: (fs.broker_company_sk = dim_company_broker.company_sk)
  • Buffers: shared hit=2279348
35. 1.558 140.273 ↑ 1.3 1,948 3

Hash (cost=4,260.07..4,260.07 rows=2,463 width=153) (actual time=140.273..140.273 rows=1,948 loops=3)

  • Buffers: shared hit=368 read=3131
36. 138.715 138.715 ↑ 1.3 1,948 3

Seq Scan on dim_date dim_date (cost=0..4,260.07 rows=2,463 width=153) (actual time=5.444..138.715 rows=1,948 loops=3)

  • Filter: (dim_date.client_sk = 11)
  • Buffers: shared hit=368 read=3131
37. 0.054 0.118 ↑ 1.0 108 3

Hash (cost=4.08..4.08 rows=108 width=52) (actual time=0.118..0.118 rows=108 loops=3)

  • Buffers: shared hit=9
38. 0.064 0.064 ↑ 1.0 108 3

Seq Scan on dim_uom dim_uom (cost=0..4.08 rows=108 width=52) (actual time=0.018..0.064 rows=108 loops=3)

  • Buffers: shared hit=9