explain.depesz.com

PostgreSQL's explain analyze made readable

Result: o41x

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 198,007.071 ↑ 2.0 60 1

Limit (cost=22,778,642.64..22,778,642.94 rows=120 width=2,135) (actual time=198,007.053..198,007.071 rows=60 loops=1)

2. 0.299 198,007.059 ↑ 2.0 60 1

Sort (cost=22,778,642.64..22,778,642.94 rows=120 width=2,135) (actual time=198,007.051..198,007.059 rows=60 loops=1)

  • Sort Key: offredepri0_2_.datecreationsysteme DESC
  • Sort Method: quicksort Memory: 85kB
3. 15.186 198,006.760 ↑ 2.0 60 1

Nested Loop (cost=1.12..22,778,638.50 rows=120 width=2,135) (actual time=14.119..198,006.760 rows=60 loops=1)

  • Join Filter: ((offredepri0_1_.id)::text = (offredepri0_.id)::text)
4. 27.383 2,401.888 ↓ 28.4 56,874 1

Nested Loop (cost=0.83..33,411.65 rows=2,001 width=1,793) (actual time=0.029..2,401.888 rows=56,874 loops=1)

5. 37.887 349.055 ↓ 28.6 57,870 1

Nested Loop (cost=0.41..31,471.68 rows=2,024 width=1,230) (actual time=0.023..349.055 rows=57,870 loops=1)

6. 79.656 79.656 ↓ 1.0 57,878 1

Seq Scan on dm2_documentcommercialvente offredepri0_1_ (cost=0.00..2,281.31 rows=57,831 width=1,230) (actual time=0.002..79.656 rows=57,878 loops=1)

7. 231.512 231.512 ↑ 1.0 1 57,878

Index Scan using dm1_adresse_pkey on dm1_adresse adresseent1_ (cost=0.41..0.49 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=57,878)

  • Index Cond: ((id)::text = (offredepri0_1_.id_adresseemetteur)::text)
  • Filter: ((id_etablissement)::text = ANY ('{1,4028817648856aac0148897c4a9a4c86,4028817648856aac0148897c4a9b4c89,4028817648856aac0148897c4a9c4c8f,4028817648856aac0148897c4a9c4c8d,4028817648856aac0148897c4a9c4c8b}'::text[]))
8. 2,025.450 2,025.450 ↑ 1.0 1 57,870

Index Scan using dm1_documentcommercial_pkey on dm1_documentcommercial offredepri0_2_ (cost=0.42..0.95 rows=1 width=563) (actual time=0.034..0.035 rows=1 loops=57,870)

  • Index Cond: ((id)::text = (offredepri0_1_.id)::text)
  • Filter: ((datefinvalidite IS NULL) AND (NOT categoriesav))
  • Rows Removed by Filter: 0
9. 192.810 195,589.686 ↓ 0.0 0 56,874

Index Scan using dm2_offredeprixvente_pkey on dm2_offredeprixvente offredepri0_ (cost=0.29..11,366.92 rows=1 width=408) (actual time=3.439..3.439 rows=0 loops=56,874)

  • Index Cond: ((id)::text = (offredepri0_2_.id)::text)
  • Filter: (statutrevue AND ((id)::text !~~ 'DefaultRecord_%'::text) AND ((etat)::text <> '0'::text) AND ((natureoffre)::text = '0'::text) AND ((SubPlan 1) <= 0))
  • Rows Removed by Filter: 0
10.          

SubPlan (forIndex Scan)

11. 297.018 195,396.876 ↑ 1.0 1 10,242

Aggregate (cost=11,366.58..11,366.59 rows=1 width=33) (actual time=19.077..19.078 rows=1 loops=10,242)

12. 664.645 195,099.858 ↑ 4.4 145 10,242

Nested Loop (cost=54.60..11,364.98 rows=642 width=33) (actual time=2.352..19.049 rows=145 loops=10,242)

  • Join Filter: ((ligneoffre2_.id)::text = (ligneoffre2_2_.id)::text)
13. 2,468.042 69,338.340 ↑ 3.6 394 10,242

Nested Loop (cost=54.04..10,229.16 rows=1,410 width=65) (actual time=0.249..6.770 rows=394 loops=10,242)

14. 33,767.874 34,587.234 ↑ 3.6 394 10,242

Bitmap Heap Scan on dm2_ligneoffredeprixvente ligneoffre2_ (cost=53.48..4,546.31 rows=1,410 width=33) (actual time=0.127..3.377 rows=394 loops=10,242)

  • Recheck Cond: ((id_offredeprixvente)::text = (offredepri0_.id)::text)
  • Heap Blocks: exact=442507
15. 819.360 819.360 ↑ 3.6 394 10,242

Bitmap Index Scan on x_dm2_ligneoffredeprixvente_00 (cost=0.00..53.13 rows=1,410 width=0) (actual time=0.080..0.080 rows=394 loops=10,242)

  • Index Cond: ((id_offredeprixvente)::text = (offredepri0_.id)::text)
16. 32,283.064 32,283.064 ↑ 1.0 1 4,035,383

Index Only Scan using dm2_lignedocumentcommercialvente_pkey on dm2_lignedocumentcommercialvente ligneoffre2_1_ (cost=0.56..4.02 rows=1 width=32) (actual time=0.007..0.008 rows=1 loops=4,035,383)

  • Index Cond: (id = (ligneoffre2_.id)::text)
  • Heap Fetches: 30682
17. 125,096.873 125,096.873 ↓ 0.0 0 4,035,383

Index Scan using dm1_lignedocumentcommercial_pkey on dm1_lignedocumentcommercial ligneoffre2_2_ (cost=0.56..0.79 rows=1 width=33) (actual time=0.030..0.031 rows=0 loops=4,035,383)

  • Index Cond: ((id)::text = (ligneoffre2_1_.id)::text)
  • Filter: (datefinvalidite IS NULL)
  • Rows Removed by Filter: 1
Planning time : 2.016 ms
Execution time : 198,007.230 ms