explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iPuk

Settings
# exclusive inclusive rows x rows loops node
1. 698.990 66,553.425 ↑ 15.7 23,372 1

Nested Loop Left Join (cost=38,912.45..124,242,788.67 rows=366,888 width=202) (actual time=484.717..66,553.425 rows=23,372 loops=1)

  • Filter: (GREATEST(GREATEST(rsc.creation_date, rscs.creation_date, rscig.creation_date, rsci.creation_date, rscis.creation_date), rscia.creation_date) < LEAST(LEAST(rec.creation_date, recs.creation_date, recig.creation_date, reci.creation_date, recis.creation_date, 'infinity'::timestamp with time zone), recia.creation_date))
  • Rows Removed by Filter: 33278
  • Buffers: shared hit=2304819 read=9773 dirtied=198 written=62, local hit=2
  • I/O Timings: read=26180.440 write=17.544
2.          

CTE process_state_types

3. 0.116 385.706 ↑ 2.8 8 1

Nested Loop (cost=1.60..38,902.09 rows=22 width=16) (actual time=6.287..385.706 rows=8 loops=1)

  • Buffers: shared hit=369 read=164 written=1, local hit=1
  • I/O Timings: read=306.099 write=0.083
4. 0.309 380.054 ↑ 2.8 8 1

Nested Loop Left Join (cost=1.02..38,816.69 rows=22 width=24) (actual time=6.202..380.054 rows=8 loops=1)

  • Filter: ((reps.* IS NULL) OR (reps.creation_date > transaction_timestamp()))
  • Rows Removed by Filter: 19
  • Buffers: shared hit=333 read=160 written=1, local hit=1
  • I/O Timings: read=306.015 write=0.083
5. 1.377 233.837 ↑ 58.8 27 1

Nested Loop (cost=0.44..27,679.77 rows=1,588 width=32) (actual time=6.184..233.837 rows=27 loops=1)

  • Buffers: shared hit=282 read=115 written=1, local hit=1
  • I/O Timings: read=178.606 write=0.083
6. 0.160 0.160 ↑ 22.6 100 1

Seq Scan on contract_ids cci_1 (cost=0.00..32.60 rows=2,260 width=8) (actual time=0.026..0.160 rows=100 loops=1)

  • Buffers: local hit=1
7. 232.300 232.300 ↓ 0.0 0 100

Index Scan using process_state_contract_id_idx on process_state ps (cost=0.44..12.22 rows=1 width=32) (actual time=1.544..2.323 rows=0 loops=100)

  • Index Cond: (contract_id = cci_1.contract_id)
  • Filter: (process_state_type_id = 10)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=282 read=115 written=1
  • I/O Timings: read=178.606 write=0.083
8. 145.908 145.908 ↑ 1.0 1 27

Index Scan using pk_revision_0 on revision reps (cost=0.58..7.00 rows=1 width=153) (actual time=5.404..5.404 rows=1 loops=27)

  • Index Cond: (id = ps.revision_to_id)
  • Buffers: shared hit=51 read=45
  • I/O Timings: read=127.409
9. 5.536 5.536 ↑ 1.0 1 8

Index Only Scan using revision_id_creation_date_idx1 on revision rsps (cost=0.58..3.88 rows=1 width=8) (actual time=0.692..0.692 rows=1 loops=8)

  • Index Cond: ((id = ps.revision_from_id) AND (creation_date <= transaction_timestamp()))
  • Heap Fetches: 8
  • Buffers: shared hit=36 read=4
  • I/O Timings: read=0.083
10. 375.427 63,645.085 ↑ 19.4 56,650 1

Nested Loop Left Join (cost=9.78..115,258,039.75 rows=1,100,663 width=378) (actual time=484.682..63,645.085 rows=56,650 loops=1)

  • Buffers: shared hit=2172568 read=9640 dirtied=198 written=62, local hit=2
  • I/O Timings: read=25897.630 write=17.544
11. 418.904 59,814.008 ↑ 19.4 56,650 1

Nested Loop Left Join (cost=9.21..106,342,461.16 rows=1,100,663 width=370) (actual time=484.668..59,814.008 rows=56,650 loops=1)

  • Buffers: shared hit=1924165 read=9223 dirtied=198 written=62, local hit=2
  • I/O Timings: read=25244.376 write=17.544
12. 389.660 57,922.204 ↑ 19.4 56,650 1

Nested Loop (cost=8.63..97,427,621.18 rows=1,100,663 width=362) (actual time=484.641..57,922.204 rows=56,650 loops=1)

  • Buffers: shared hit=1821822 read=9108 dirtied=198 written=62, local hit=2
  • I/O Timings: read=24870.717 write=17.544
13. 407.612 51,924.194 ↑ 19.4 56,650 1

Nested Loop Left Join (cost=8.05..88,512,781.19 rows=1,100,663 width=354) (actual time=475.558..51,924.194 rows=56,650 loops=1)

  • Buffers: shared hit=1538924 read=8566 dirtied=197 written=57, local hit=2
  • I/O Timings: read=23451.439 write=17.071
14. 381.812 49,533.832 ↑ 19.4 56,650 1

Nested Loop (cost=7.47..79,597,721.83 rows=1,100,663 width=346) (actual time=475.542..49,533.832 rows=56,650 loops=1)

  • Buffers: shared hit=1417033 read=8389 dirtied=197 written=56, local hit=2
  • I/O Timings: read=23079.729 write=17.035
15. 383.719 43,147.120 ↑ 19.4 56,650 1

Nested Loop Left Join (cost=6.89..70,682,662.47 rows=1,100,663 width=338) (actual time=475.485..43,147.120 rows=56,650 loops=1)

  • Buffers: shared hit=1134443 read=7589 dirtied=197 written=46, local hit=2
  • I/O Timings: read=20535.313 write=7.876
16. 380.692 42,310.201 ↑ 19.4 56,650 1

Nested Loop (cost=6.32..61,764,343.80 rows=1,100,663 width=330) (actual time=475.471..42,310.201 rows=56,650 loops=1)

  • Buffers: shared hit=1103548 read=7589 dirtied=197 written=46, local hit=2
  • I/O Timings: read=20535.313 write=7.876
17. 360.516 38,870.409 ↑ 19.4 56,650 1

Nested Loop Left Join (cost=5.74..52,846,025.13 rows=1,100,663 width=322) (actual time=475.432..38,870.409 rows=56,650 loops=1)

  • Buffers: shared hit=820291 read=7485 dirtied=197 written=46, local hit=2
  • I/O Timings: read=20212.375 write=7.876
18. 447.395 38,283.293 ↑ 19.4 56,650 1

Nested Loop (cost=5.16..43,928,312.66 rows=1,100,663 width=314) (actual time=475.416..38,283.293 rows=56,650 loops=1)

  • Buffers: shared hit=797821 read=7485 dirtied=197 written=46, local hit=2
  • I/O Timings: read=20212.375 write=7.876
19. 345.951 35,229.998 ↑ 19.4 56,650 1

Nested Loop Left Join (cost=4.58..35,010,600.19 rows=1,100,663 width=306) (actual time=475.321..35,229.998 rows=56,650 loops=1)

  • Buffers: shared hit=514566 read=7391 dirtied=194 written=46, local hit=2
  • I/O Timings: read=19932.551 write=7.876
20. 501.370 34,827.397 ↑ 19.4 56,650 1

Nested Loop (cost=4.01..26,091,879.17 rows=1,100,663 width=298) (actual time=475.301..34,827.397 rows=56,650 loops=1)

  • Buffers: shared hit=514466 read=7391 dirtied=194 written=46, local hit=2
  • I/O Timings: read=19932.551 write=7.876
21. 333.186 29,624.077 ↑ 19.4 56,650 1

Nested Loop (cost=3.43..17,173,158.16 rows=1,100,663 width=290) (actual time=457.823..29,624.077 rows=56,650 loops=1)

  • Buffers: shared hit=231188 read=7184 dirtied=194 written=46, local hit=2
  • I/O Timings: read=19191.983 write=7.876
22. 229.775 18,547.960 ↑ 7.6 25,397 1

Nested Loop Left Join (cost=2.85..9,777,870.69 rows=192,301 width=258) (actual time=445.842..18,547.960 rows=25,397 loops=1)

  • Buffers: shared hit=83942 read=5807 dirtied=179 written=38, local hit=2
  • I/O Timings: read=13029.792 write=6.348
23. 200.668 9,908.410 ↑ 10.4 14,575 1

Nested Loop Left Join (cost=2.28..8,549,961.43 rows=151,693 width=204) (actual time=430.142..9,908.410 rows=14,575 loops=1)

  • Join Filter: (pst.contract_id = c.id)
  • Rows Removed by Join Filter: 115797
  • Buffers: shared hit=8375 read=3407 dirtied=168 written=20, local hit=2
  • I/O Timings: read=8054.871 write=1.309
24. 61.921 9,183.042 ↑ 10.4 14,575 1

Nested Loop (cost=2.28..8,474,873.17 rows=151,693 width=204) (actual time=44.350..9,183.042 rows=14,575 loops=1)

  • Buffers: shared hit=8006 read=3243 dirtied=168 written=19, local hit=1
  • I/O Timings: read=7748.773 write=1.226
25. 4.298 1,719.186 ↑ 18.0 205 1

Nested Loop (cost=1.70..162,726.54 rows=3,697 width=144) (actual time=32.872..1,719.186 rows=205 loops=1)

  • Buffers: shared hit=1232 read=544 dirtied=33, local hit=1
  • I/O Timings: read=1490.793
26. 2.602 1,106.260 ↑ 20.1 134 1

Nested Loop (cost=1.14..73,155.70 rows=2,692 width=128) (actual time=25.431..1,106.260 rows=134 loops=1)

  • Buffers: shared hit=677 read=358 dirtied=2, local hit=1
  • I/O Timings: read=973.180
27. 4.457 495.432 ↑ 23.0 102 1

Nested Loop (cost=0.57..19,429.30 rows=2,343 width=80) (actual time=12.689..495.432 rows=102 loops=1)

  • Buffers: shared hit=330 read=171, local hit=1
  • I/O Timings: read=443.309
28. 1.975 1.975 ↑ 22.6 100 1

Seq Scan on contract_ids cci (cost=0.00..32.60 rows=2,260 width=8) (actual time=0.027..1.975 rows=100 loops=1)

  • Buffers: local hit=1
29. 489.000 489.000 ↑ 1.0 1 100

Index Scan using contract_id_idx on contract c (cost=0.57..8.57 rows=1 width=72) (actual time=4.868..4.890 rows=1 loops=100)

  • Index Cond: (id = cci.contract_id)
  • Buffers: shared hit=330 read=171
  • I/O Timings: read=443.309
30. 608.226 608.226 ↑ 5.0 1 102

Index Scan using contract_item_group_contract_id_idx on contract_item_group cig (cost=0.57..22.88 rows=5 width=48) (actual time=5.150..5.963 rows=1 loops=102)

  • Index Cond: (contract_id = c.id)
  • Buffers: shared hit=347 read=187 dirtied=2
  • I/O Timings: read=529.871
31. 608.628 608.628 ↑ 4.0 2 134

Index Scan using contract_state_contract_id_idx on contract_state cs (cost=0.57..33.19 rows=8 width=40) (actual time=3.761..4.542 rows=2 loops=134)

  • Index Cond: (contract_id = c.id)
  • Filter: ((contract_state_type_id >= 1) AND (contract_state_type_id <= 5))
  • Buffers: shared hit=555 read=186 dirtied=31
  • I/O Timings: read=517.613
32. 7,401.935 7,401.935 ↑ 4.4 71 205

Index Scan using contract_item_contract_item_group_id_revision_from_id_idx on contract_item ci (cost=0.58..2,245.25 rows=310 width=68) (actual time=6.003..36.107 rows=71 loops=205)

  • Index Cond: (contract_item_group_id = cig.id)
  • Filter: ((sales_config_id IS NOT NULL) AND (sales_component_id IS NOT NULL) AND (spam_contract_position IS NOT NULL))
  • Rows Removed by Filter: 23
  • Buffers: shared hit=6774 read=2699 dirtied=135 written=19
  • I/O Timings: read=6257.980 write=1.226
33. 524.700 524.700 ↑ 2.8 8 14,575

CTE Scan on process_state_types pst (cost=0.00..0.44 rows=22 width=16) (actual time=0.004..0.036 rows=8 loops=14,575)

  • Buffers: shared hit=369 read=164 written=1, local hit=1
  • I/O Timings: read=306.099 write=0.083
34. 8,409.775 8,409.775 ↑ 1.0 1 14,575

Index Scan using contract_item_attribute_contract_item_id_revision_from_id_idx2 on contract_item_attribute cia (cost=0.57..8.08 rows=1 width=62) (actual time=0.466..0.577 rows=1 loops=14,575)

  • Index Cond: (contract_item_id = ci.id)
  • Buffers: shared hit=75567 read=2400 dirtied=11 written=18
  • I/O Timings: read=4974.921 write=5.039
35. 10,742.931 10,742.931 ↑ 7.0 2 25,397

Index Scan using contract_item_state_contract_item_id_idx1 on contract_item_state cis (cost=0.58..38.32 rows=14 width=40) (actual time=0.377..0.423 rows=2 loops=25,397)

  • Index Cond: (contract_item_id = ci.id)
  • Filter: ((contract_item_state_type_id >= 1) AND (contract_item_state_type_id <= 5))
  • Buffers: shared hit=147246 read=1377 dirtied=15 written=8
  • I/O Timings: read=6162.191 write=1.528
36. 4,701.950 4,701.950 ↑ 1.0 1 56,650

Index Scan using pk_revision_0 on revision rsc (cost=0.58..8.10 rows=1 width=24) (actual time=0.083..0.083 rows=1 loops=56,650)

  • Index Cond: (id = c.revision_from_id)
  • Buffers: shared hit=283278 read=207
  • I/O Timings: read=740.567
37. 56.650 56.650 ↓ 0.0 0 56,650

Index Scan using pk_revision_0 on revision rec (cost=0.58..8.10 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=56,650)

  • Index Cond: (id = c.revision_to_id)
  • Buffers: shared hit=100
38. 2,605.900 2,605.900 ↑ 1.0 1 56,650

Index Scan using pk_revision_0 on revision rscs (cost=0.58..8.10 rows=1 width=24) (actual time=0.046..0.046 rows=1 loops=56,650)

  • Index Cond: (id = cs.revision_from_id)
  • Buffers: shared hit=283255 read=94 dirtied=3
  • I/O Timings: read=279.825
39. 226.600 226.600 ↓ 0.0 0 56,650

Index Scan using pk_revision_0 on revision recs (cost=0.58..8.10 rows=1 width=24) (actual time=0.004..0.004 rows=0 loops=56,650)

  • Index Cond: (id = cs.revision_to_id)
  • Buffers: shared hit=22470
40. 3,059.100 3,059.100 ↑ 1.0 1 56,650

Index Scan using pk_revision_0 on revision rscig (cost=0.58..8.10 rows=1 width=24) (actual time=0.054..0.054 rows=1 loops=56,650)

  • Index Cond: (id = cig.revision_from_id)
  • Buffers: shared hit=283257 read=104
  • I/O Timings: read=322.938
41. 453.200 453.200 ↓ 0.0 0 56,650

Index Scan using pk_revision_0 on revision recig (cost=0.58..8.10 rows=1 width=24) (actual time=0.008..0.008 rows=0 loops=56,650)

  • Index Cond: (id = cig.revision_to_id)
  • Buffers: shared hit=30895
42. 6,004.900 6,004.900 ↑ 1.0 1 56,650

Index Scan using pk_revision_0 on revision rsci (cost=0.58..8.10 rows=1 width=24) (actual time=0.106..0.106 rows=1 loops=56,650)

  • Index Cond: (id = ci.revision_from_id)
  • Buffers: shared hit=282590 read=800 written=10
  • I/O Timings: read=2544.416 write=9.160
43. 1,982.750 1,982.750 ↓ 0.0 0 56,650

Index Scan using pk_revision_0 on revision reci (cost=0.58..8.10 rows=1 width=24) (actual time=0.035..0.035 rows=0 loops=56,650)

  • Index Cond: (id = ci.revision_to_id)
  • Buffers: shared hit=121891 read=177 written=1
  • I/O Timings: read=371.709 write=0.036
44. 5,608.350 5,608.350 ↑ 1.0 1 56,650

Index Scan using pk_revision_0 on revision rscis (cost=0.58..8.10 rows=1 width=24) (actual time=0.099..0.099 rows=1 loops=56,650)

  • Index Cond: (id = cis.revision_from_id)
  • Buffers: shared hit=282898 read=542 dirtied=1 written=5
  • I/O Timings: read=1419.279 write=0.473
45. 1,472.900 1,472.900 ↓ 0.0 0 56,650

Index Scan using pk_revision_0 on revision recis (cost=0.58..8.10 rows=1 width=24) (actual time=0.026..0.026 rows=0 loops=56,650)

  • Index Cond: (id = cis.revision_to_id)
  • Buffers: shared hit=102343 read=115
  • I/O Timings: read=373.658
46. 3,455.650 3,455.650 ↑ 1.0 1 56,650

Index Scan using pk_revision_0 on revision rscia (cost=0.58..8.10 rows=1 width=24) (actual time=0.061..0.061 rows=1 loops=56,650)

  • Index Cond: (id = cia.revision_from_id)
  • Buffers: shared hit=248403 read=417
  • I/O Timings: read=653.254
47. 2,209.350 2,209.350 ↓ 0.0 0 56,650

Index Scan using pk_revision_0 on revision recia (cost=0.58..8.10 rows=1 width=24) (actual time=0.039..0.039 rows=0 loops=56,650)

  • Index Cond: (id = cia.revision_to_id)
  • Buffers: shared hit=132251 read=133
  • I/O Timings: read=282.811