explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jrmv

Settings
# exclusive inclusive rows x rows loops node
1. 0.103 5,658.734 ↑ 1.0 50 1

Nested Loop (cost=16,059.03..17,091.46 rows=50 width=1,147) (actual time=5,641.85..5,658.734 rows=50 loops=1)

  • Buffers: shared hit=2319165 read=11644 dirtied=60
2. 0.078 5,657.881 ↑ 1.0 50 1

Nested Loop (cost=16,058.75..17,075.84 rows=50 width=1,063) (actual time=5,641.825..5,657.881 rows=50 loops=1)

  • Buffers: shared hit=2319008 read=11643 dirtied=60
3. 3.921 5,656.303 ↑ 1.0 50 1

Nested Loop (cost=16,058.33..17,050.46 rows=50 width=943) (actual time=5,641.805..5,656.303 rows=50 loops=1)

  • Buffers: shared hit=2318850 read=11601 dirtied=60
4. 0.093 5,650.732 ↑ 1.0 50 1

Nested Loop (cost=16,055.15..16,889.19 rows=50 width=755) (actual time=5,641.601..5,650.732 rows=50 loops=1)

  • Buffers: shared hit=2318485 read=11470 dirtied=60
5. 2.182 5,649.339 ↑ 1.0 50 1

Nested Loop (cost=16,054.73..16,865.77 rows=50 width=658) (actual time=5,641.558..5,649.339 rows=50 loops=1)

  • Buffers: shared hit=2318321 read=11434 dirtied=60
6. 0.065 5,646.407 ↑ 1.0 50 1

Nested Loop (cost=16,051.85..16,620.52 rows=50 width=560) (actual time=5,641.372..5,646.407 rows=50 loops=1)

  • Buffers: shared hit=2317964 read=11386 dirtied=60
7. 0.865 5,646.092 ↑ 1.0 50 1

Nested Loop (cost=16,051.71..16,611.89 rows=50 width=482) (actual time=5,641.323..5,646.092 rows=50 loops=1)

  • Buffers: shared hit=2317865 read=11385 dirtied=60
8. 2.112 5,644.927 ↑ 1.0 50 1

Nested Loop (cost=16,049.48..16,399.06 rows=50 width=416) (actual time=5,641.281..5,644.927 rows=50 loops=1)

  • Buffers: shared hit=2317475 read=11375 dirtied=60
9. 0.120 5,642.465 ↑ 1.0 50 1

Hash Join (cost=16,047.26..16,186.24 rows=50 width=350) (actual time=5,641.108..5,642.465 rows=50 loops=1)

  • Buffers: shared hit=2317108 read=11342 dirtied=60
10. 0.129 5,642.315 ↑ 1.0 50 1

Nested Loop (cost=16,046.21..16,184.51 rows=50 width=288) (actual time=5,641.02..5,642.315 rows=50 loops=1)

  • Buffers: shared hit=2317107 read=11342 dirtied=60
11. 0.061 5,641.436 ↑ 1.0 50 1

Nested Loop (cost=16,045.93..16,168.88 rows=50 width=204) (actual time=5,640.924..5,641.436 rows=50 loops=1)

  • Buffers: shared hit=2316917 read=11341 dirtied=60
12. 0.013 5,640.775 ↑ 1.0 50 1

Limit (cost=16,045.5..16,045.63 rows=50 width=84) (actual time=5,640.735..5,640.775 rows=50 loops=1)

  • Buffers: shared hit=2316721 read=11337 dirtied=60
13. 410.900 5,640.762 ↑ 288.4 50 1

Sort (cost=16,045.5..16,081.55 rows=14,420 width=84) (actual time=5,640.734..5,640.762 rows=50 loops=1)

  • Sort Key: cu.individual, cu.customernbr, co.supplierref
  • Sort Method: top-N heapsort Memory: 32kB
  • Buffers: shared hit=2316721 read=11337 dirtied=60
14. 500.137 5,229.862 ↓ 33.0 476,108 1

Nested Loop (cost=25.53..15,566.48 rows=14,420 width=84) (actual time=0.912..5,229.862 rows=476,108 loops=1)

  • Buffers: shared hit=2316713 read=11337 dirtied=60
15. 222.884 2,554.261 ↓ 32.8 271,933 1

Nested Loop (cost=25.1..11,324.97 rows=8,294 width=56) (actual time=0.711..2,554.261 rows=271,933 loops=1)

  • Buffers: shared hit=1086129 read=6628
16. 262.675 427.846 ↓ 32.8 271,933 1

Hash Join (cost=24.68..7,400.64 rows=8,294 width=8) (actual time=0.593..427.846 rows=271,933 loops=1)

  • Buffers: shared hit=850 read=2689
17. 164.685 164.685 ↑ 1.0 272,178 1

Seq Scan on crmdata cd (cost=0..6,262.92 rows=274,692 width=8) (actual time=0.056..164.685 rows=272,178 loops=1)

  • Buffers: shared hit=827 read=2689
18. 0.026 0.486 ↑ 1.1 31 1

Hash (cost=24.27..24.27 rows=33 width=4) (actual time=0.486..0.486 rows=31 loops=1)

  • Buffers: shared hit=20
19. 0.460 0.460 ↑ 1.1 31 1

Index Scan using parametervalue_internalparametervaluecode_idx on parametervalue pv (cost=0.28..24.27 rows=33 width=4) (actual time=0.091..0.46 rows=31 loops=1)

  • Index Cond: ((pv.internalparametervaluecode)::text = ANY ('{customer,prospect}'::text[]))
  • Buffers: shared hit=20
20. 1,903.531 1,903.531 ↑ 1.0 1 271,933

Index Scan using customer_pkey on customer cu (cost=0.42..0.46 rows=1 width=52) (actual time=0.006..0.007 rows=1 loops=271,933)

  • Index Cond: (cu.customerid = cd.customerid)
  • Buffers: shared hit=1085279 read=3939
21. 2,175.464 2,175.464 ↑ 1.0 2 271,933

Index Scan using idx_contract_customerid_pk on contract co (cost=0.42..0.49 rows=2 width=28) (actual time=0.006..0.008 rows=2 loops=271,933)

  • Index Cond: (co.customerid = cu.customerid)
  • Buffers: shared hit=1230584 read=4709 dirtied=60
22. 0.600 0.600 ↑ 1.0 1 50

Index Scan using freeaddresses_pkey on freeaddresses fa_cu (cost=0.43..2.45 rows=1 width=120) (actual time=0.011..0.012 rows=1 loops=50)

  • Index Cond: (fa_cu.addressid = cu.postaddrid)
  • Buffers: shared hit=196 read=4
23. 0.750 0.750 ↑ 1.0 1 50

Index Scan using town_pkey on town town_cu (cost=0.28..0.3 rows=1 width=84) (actual time=0.014..0.015 rows=1 loops=50)

  • Index Cond: (((fa_cu.towncountrycode)::text = (town_cu.countrycode)::text) AND ((fa_cu.townpostalcode)::text = (town_cu.postalcode)::text) AND (fa_cu.towntowncode = town_cu.towncode))
  • Buffers: shared hit=190 read=1
24. 0.019 0.030 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=62) (actual time=0.03..0.03 rows=2 loops=1)

  • Buffers: shared hit=1
25. 0.011 0.011 ↑ 1.0 2 1

Seq Scan on supplier s (cost=0..1.02 rows=2 width=62) (actual time=0.009..0.011 rows=2 loops=1)

  • Buffers: shared hit=1
26. 0.000 0.350 ↑ 1.0 1 50

Index Scan using "Key4" on contracthistory firsthistory (cost=2.23..4.25 rows=1 width=66) (actual time=0.006..0.007 rows=1 loops=50)

  • Index Cond: ((firsthistory.contractid = co.contractid) AND (firsthistory.contracthistentry = (SubPlan 2)))
  • Buffers: shared hit=200
27.          

SubPlan (forIndex Scan)

28. 0.100 2.000 ↑ 1.0 1 50

Result (cost=1.79..1.8 rows=1 width=4) (actual time=0.039..0.04 rows=1 loops=50)

  • Buffers: shared hit=167 read=33
29.          

Initplan (forResult)

30. 0.050 1.900 ↑ 1.0 1 50

Limit (cost=0.42..1.79 rows=1 width=4) (actual time=0.038..0.038 rows=1 loops=50)

  • Buffers: shared hit=167 read=33
31. 1.850 1.850 ↑ 2.0 1 50

Index Only Scan using "Key4" on contracthistory ch2 (cost=0.42..3.16 rows=2 width=4) (actual time=0.037..0.037 rows=1 loops=50)

  • Index Cond: ((ch2.contractid = co.contractid) AND (ch2.contracthistentry IS NOT NULL))
  • Buffers: shared hit=167 read=33
32. 0.000 0.300 ↑ 1.0 1 50

Index Scan using "Key4" on contracthistory lasthistory (cost=2.23..4.25 rows=1 width=66) (actual time=0.006..0.006 rows=1 loops=50)

  • Index Cond: ((lasthistory.contractid = co.contractid) AND (lasthistory.contracthistentry = (SubPlan 4)))
  • Buffers: shared hit=200
33.          

SubPlan (forIndex Scan)

34. 0.050 0.700 ↑ 1.0 1 50

Result (cost=1.79..1.8 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=50)

  • Buffers: shared hit=190 read=10
35.          

Initplan (forResult)

36. 0.100 0.650 ↑ 1.0 1 50

Limit (cost=0.42..1.79 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=50)

  • Buffers: shared hit=190 read=10
37. 0.550 0.550 ↑ 2.0 1 50

Index Only Scan using "Key4" on contracthistory ch2_1 (cost=0.42..3.16 rows=2 width=4) (actual time=0.011..0.011 rows=1 loops=50)

  • Index Cond: ((ch2_1.contractid = co.contractid) AND (ch2_1.contracthistentry IS NOT NULL))
  • Buffers: shared hit=190 read=10
38. 0.250 0.250 ↑ 1.0 1 50

Index Scan using product_pkey on product p (cost=0.14..0.16 rows=1 width=78) (actual time=0.004..0.005 rows=1 loops=50)

  • Index Cond: (lasthistory.productcodeparamid = p.productcodeparamid)
  • Buffers: shared hit=99 read=1
39. 0.000 0.750 ↑ 1.0 1 50

Index Scan using contractdpjournal_pkey on contractdpjournal cdp (cost=2.88..4.89 rows=1 width=98) (actual time=0.014..0.015 rows=1 loops=50)

  • Index Cond: (cdp.contractdpid = (SubPlan 5))
  • Buffers: shared hit=191 read=9
40.          

SubPlan (forIndex Scan)

41. 0.100 2.050 ↑ 1.0 1 50

Limit (cost=2.45..2.45 rows=1 width=8) (actual time=0.04..0.041 rows=1 loops=50)

  • Buffers: shared hit=166 read=39
42. 0.250 1.950 ↑ 1.0 1 50

Sort (cost=2.45..2.45 rows=1 width=8) (actual time=0.039..0.039 rows=1 loops=50)

  • Sort Key: (COALESCE(cdp2.todate, '2500-01-01'::date)) DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=166 read=39
43. 1.700 1.700 ↑ 1.0 1 50

Index Scan using idx_contractdpjournal_contractid_pk on contractdpjournal cdp2 (cost=0.42..2.44 rows=1 width=8) (actual time=0.032..0.034 rows=1 loops=50)

  • Index Cond: (cdp2.contractid = co.contractid)
  • Buffers: shared hit=163 read=39
44. 1.300 1.300 ↑ 1.0 1 50

Index Scan using deliverypoint_pkey on deliverypoint dp (cost=0.42..0.46 rows=1 width=97) (actual time=0.026..0.026 rows=1 loops=50)

  • Index Cond: (cdp.pointid = dp.pointid)
  • Buffers: shared hit=164 read=36
45. 0.000 1.650 ↑ 1.0 1 50

Index Scan using dpj_un on dpconfigjournal dpc (cost=3.17..3.22 rows=1 width=192) (actual time=0.032..0.033 rows=1 loops=50)

  • Index Cond: (dpc.dpconfigjid = (SubPlan 6))
  • Buffers: shared hit=165 read=31
46.          

SubPlan (forIndex Scan)

47. 0.100 3.800 ↑ 1.0 1 50

Limit (cost=2.75..2.75 rows=1 width=16) (actual time=0.075..0.076 rows=1 loops=50)

  • Buffers: shared hit=200 read=100
48. 0.350 3.700 ↑ 2.0 1 50

Sort (cost=2.75..2.75 rows=2 width=16) (actual time=0.074..0.074 rows=1 loops=50)

  • Sort Key: dpc2.fromdate DESC, dpc2.creationdate DESC
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=200 read=100
49. 3.350 3.350 ↓ 2.0 4 50

Index Scan using idx_dpconfigjournal_pointid_pk on dpconfigjournal dpc2 (cost=0.42..2.74 rows=2 width=16) (actual time=0.022..0.067 rows=4 loops=50)

  • Index Cond: (dpc2.pointid = dp.pointid)
  • Buffers: shared hit=197 read=100
50. 1.500 1.500 ↑ 1.0 1 50

Index Scan using freeaddresses_pkey on freeaddresses fa_dp (cost=0.43..0.5 rows=1 width=120) (actual time=0.029..0.03 rows=1 loops=50)

  • Index Cond: (fa_dp.addressid = dp.addressid)
  • Buffers: shared hit=158 read=42
51. 0.750 0.750 ↑ 1.0 1 50

Index Scan using town_pkey on town town_dp (cost=0.28..0.3 rows=1 width=84) (actual time=0.014..0.015 rows=1 loops=50)

  • Index Cond: (((fa_dp.towncountrycode)::text = (town_dp.countrycode)::text) AND ((fa_dp.townpostalcode)::text = (town_dp.postalcode)::text) AND (fa_dp.towntowncode = town_dp.towncode))
  • Buffers: shared hit=157 read=1