explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2ImR : Optimization for: Req1 # o; plan #HNwC

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.677 510.281 ↑ 87.4 249 1

HashAggregate (cost=74,247.24..74,464.84 rows=21,760 width=1,854) (actual time=510.138..510.281 rows=249 loops=1)

  • Group Key: tt_trouble_ticket.ticketid, tt_trouble_ticket.feuillet, tt_trouble_ticket.serviceid1value, tt_trouble_ticket.initiatorsystem, tt_trouble_ticket.pilotgroupid, tt_trouble_ticket.natureid, tt_trouble_ticket.servicechangeproduct, tt_trouble_ticket.old_feuillet, tt_trouble_ticket.id_diag_res, tt_trouble_ticket.languageid, tt_trouble_ticket.old_id_diag_res, tt_trouble_ticket.servicetypeid, tt_trouble_ticket.serviceid2value, tt_trouble_ticket.serviceid3value, tt_trouble_ticket.calcul_infos, tt_trouble_ticket.old_serviceid1value, tt_trouble_ticket.horodatage_maj_ticket, tt_trouble_ticket.isparent, tt_trouble_ticket.initgroupid
  • Buffers: shared hit=40800
2. 0.039 509.604 ↑ 87.4 249 1

Append (cost=1,150.75..73,213.64 rows=21,760 width=1,854) (actual time=24.588..509.604 rows=249 loops=1)

  • Buffers: shared hit=40800
3. 4.139 197.215 ↑ 335.5 46 1

Hash Join (cost=1,150.75..17,590.07 rows=15,434 width=156) (actual time=24.587..197.215 rows=46 loops=1)

  • Hash Cond: ((tt_trouble_ticket.pilotgroupid)::text = (iai_params_specifiques_eds.eds_params)::text)
  • Join Filter: ((tt_trouble_ticket.parentticketobjid IS NULL) OR ((iai_params_specifiques_eds.ajout_diag_fils)::text = ''::text))
  • Rows Removed by Join Filter: 3
  • Buffers: shared hit=14721
4. 175.304 192.344 ↑ 1.8 18,034 1

Bitmap Heap Scan on tt_trouble_ticket (cost=1,123.55..17,248.15 rows=32,376 width=167) (actual time=22.905..192.344 rows=18,034 loops=1)

  • Recheck Cond: ((natureid)::text = ANY ('{0,1,2,4,5,K}'::text[]))
  • Filter: ((id_diag_res IS NULL) AND ((currentstatus)::text = ANY ('{0,1,2,3,4}'::text[])))
  • Rows Removed by Filter: 50872
  • Heap Blocks: exact=14499
  • Buffers: shared hit=14706
5. 17.040 17.040 ↓ 1.0 68,906 1

Bitmap Index Scan on tt_trouble_ticket_natureid (cost=0.00..1,115.46 rows=67,857 width=0) (actual time=17.040..17.040 rows=68,906 loops=1)

  • Index Cond: ((natureid)::text = ANY ('{0,1,2,4,5,K}'::text[]))
  • Buffers: shared hit=207
6. 0.115 0.732 ↓ 1.0 280 1

Hash (cost=23.73..23.73 rows=278 width=8) (actual time=0.732..0.732 rows=280 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=15
7. 0.617 0.617 ↓ 1.0 280 1

Seq Scan on iai_params_specifiques_eds (cost=0.00..23.73 rows=278 width=8) (actual time=0.023..0.617 rows=280 loops=1)

  • Filter: (((envoi_resu_diag)::text = 'on'::text) AND ((actif)::text = 'Actif'::text))
  • Rows Removed by Filter: 302
  • Buffers: shared hit=15
8. 0.080 293.858 ↑ 29.6 203 1

Unique (cost=33,640.51..33,941.01 rows=6,010 width=156) (actual time=293.751..293.858 rows=203 loops=1)

  • Buffers: shared hit=22817
9. 0.834 293.778 ↑ 29.6 203 1

Sort (cost=33,640.51..33,655.53 rows=6,010 width=156) (actual time=293.748..293.778 rows=203 loops=1)

  • Sort Key: tt_trouble_ticket_1.ticketid, tt_trouble_ticket_1.feuillet, tt_trouble_ticket_1.serviceid1value, tt_trouble_ticket_1.initiatorsystem, tt_activegroups.groupid, tt_trouble_ticket_1.natureid, tt_trouble_ticket_1.servicechangeproduct, tt_trouble_ticket_1.old_feuillet, tt_trouble_ticket_1.id_diag_res, tt_trouble_ticket_1.languageid, tt_trouble_ticket_1.old_id_diag_res, tt_trouble_ticket_1.servicetypeid, tt_trouble_ticket_1.serviceid2value, tt_trouble_ticket_1.serviceid3value, tt_trouble_ticket_1.calcul_infos, tt_trouble_ticket_1.old_serviceid1value, tt_trouble_ticket_1.horodatage_maj_ticket, tt_trouble_ticket_1.isparent, tt_trouble_ticket_1.initgroupid
  • Sort Method: quicksort Memory: 72kB
  • Buffers: shared hit=22817
10. 0.806 292.944 ↑ 29.6 203 1

Hash Join (cost=17,669.82..33,263.29 rows=6,010 width=156) (actual time=131.432..292.944 rows=203 loops=1)

  • Hash Cond: ((tt_activegroups.groupid)::text = (iai_params_specifiques_eds_1.eds_params)::text)
  • Join Filter: ((tt_trouble_ticket_1.parentticketobjid IS NULL) OR ((iai_params_specifiques_eds_1.ajout_diag_fils)::text = ''::text))
  • Rows Removed by Join Filter: 3
  • Buffers: shared hit=22817
11. 24.571 291.758 ↑ 5.3 4,285 1

Hash Join (cost=17,642.62..33,076.36 rows=22,519 width=167) (actual time=130.087..291.758 rows=4,285 loops=1)

  • Hash Cond: ((tt_activegroups.fk_ticketid)::text = (tt_trouble_ticket_1.ticketid)::text)
  • Join Filter: ((tt_activegroups.groupid)::text <> (tt_trouble_ticket_1.pilotgroupid)::text)
  • Rows Removed by Join Filter: 9779
  • Buffers: shared hit=22802
12. 137.282 137.282 ↑ 1.4 124,859 1

Seq Scan on tt_activegroups (cost=0.00..14,464.86 rows=181,008 width=18) (actual time=0.008..137.282 rows=124,859 loops=1)

  • Filter: ((enddate IS NULL) AND ((roleid)::text = 'ACT'::text))
  • Rows Removed by Filter: 385850
  • Buffers: shared hit=8081
13. 11.530 129.905 ↓ 1.1 17,985 1

Hash (cost=17,440.27..17,440.27 rows=16,188 width=167) (actual time=129.905..129.905 rows=17,985 loops=1)

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 2830kB
  • Buffers: shared hit=14721
14. 109.423 118.375 ↓ 1.1 17,985 1

Bitmap Heap Scan on tt_trouble_ticket tt_trouble_ticket_1 (cost=1,146.02..17,440.27 rows=16,188 width=167) (actual time=11.771..118.375 rows=17,985 loops=1)

  • Recheck Cond: ((natureid)::text = ANY ('{0,1,2,4,5,K}'::text[]))
  • Filter: ((id_diag_res IS NULL) AND (NOT (hashed SubPlan 1)) AND ((currentstatus)::text = ANY ('{0,1,2,3,4}'::text[])))
  • Rows Removed by Filter: 50921
  • Heap Blocks: exact=14499
  • Buffers: shared hit=14721
15. 8.617 8.617 ↓ 1.0 68,906 1

Bitmap Index Scan on tt_trouble_ticket_natureid (cost=0.00..1,115.46 rows=67,857 width=0) (actual time=8.617..8.617 rows=68,906 loops=1)

  • Index Cond: ((natureid)::text = ANY ('{0,1,2,4,5,K}'::text[]))
  • Buffers: shared hit=207
16.          

SubPlan (forBitmap Heap Scan)

17. 0.190 0.335 ↑ 1.0 283 1

HashAggregate (cost=22.98..25.81 rows=283 width=7) (actual time=0.296..0.335 rows=283 loops=1)

  • Group Key: iai_params_specifiques_eds_3.eds_params
  • Buffers: shared hit=15
18. 0.145 0.145 ↑ 1.0 283 1

Seq Scan on iai_params_specifiques_eds iai_params_specifiques_eds_3 (cost=0.00..22.27 rows=283 width=7) (actual time=0.006..0.145 rows=283 loops=1)

  • Filter: ((envoi_resu_diag)::text = 'on'::text)
  • Rows Removed by Filter: 299
  • Buffers: shared hit=15
19. 0.063 0.380 ↓ 1.0 280 1

Hash (cost=23.73..23.73 rows=278 width=8) (actual time=0.380..0.380 rows=280 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=15
20. 0.317 0.317 ↓ 1.0 280 1

Seq Scan on iai_params_specifiques_eds iai_params_specifiques_eds_1 (cost=0.00..23.73 rows=278 width=8) (actual time=0.025..0.317 rows=280 loops=1)

  • Filter: (((envoi_resu_diag)::text = 'on'::text) AND ((actif)::text = 'Actif'::text))
  • Rows Removed by Filter: 302
  • Buffers: shared hit=15
21. 0.000 18.492 ↓ 0.0 0 1

Unique (cost=21,449.17..21,464.97 rows=316 width=156) (actual time=18.492..18.492 rows=0 loops=1)

  • Buffers: shared hit=3262
22. 0.055 18.492 ↓ 0.0 0 1

Sort (cost=21,449.17..21,449.96 rows=316 width=156) (actual time=18.492..18.492 rows=0 loops=1)

  • Sort Key: tt_trouble_ticket_2.ticketid, tt_trouble_ticket_2.feuillet, tt_trouble_ticket_2.serviceid1value, tt_trouble_ticket_2.initiatorsystem, tt_activegroups_1.groupid, tt_trouble_ticket_2.natureid, tt_trouble_ticket_2.servicechangeproduct, tt_trouble_ticket_2.old_feuillet, tt_trouble_ticket_2.id_diag_res, tt_trouble_ticket_2.languageid, tt_trouble_ticket_2.old_id_diag_res, tt_trouble_ticket_2.servicetypeid, tt_trouble_ticket_2.serviceid2value, tt_trouble_ticket_2.serviceid3value, tt_trouble_ticket_2.calcul_infos, tt_trouble_ticket_2.old_serviceid1value, tt_trouble_ticket_2.horodatage_maj_ticket, tt_trouble_ticket_2.isparent, tt_trouble_ticket_2.initgroupid
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3262
23. 0.035 18.437 ↓ 0.0 0 1

Hash Join (cost=6,268.78..21,436.05 rows=316 width=156) (actual time=18.437..18.437 rows=0 loops=1)

  • Hash Cond: ((tt_activegroups_1.groupid)::text = (iai_params_specifiques_eds_2.eds_params)::text)
  • Join Filter: ((tt_trouble_ticket_2.parentticketobjid IS NULL) OR ((iai_params_specifiques_eds_2.ajout_diag_fils)::text = ''::text))
  • Buffers: shared hit=3262
24. 0.022 18.028 ↓ 0.0 0 1

Hash Join (cost=6,241.58..21,400.46 rows=1,182 width=167) (actual time=18.028..18.028 rows=0 loops=1)

  • Hash Cond: ((tt_activegroups_1.fk_ticketid)::text = (tt_trouble_ticket_2.ticketid)::text)
  • Join Filter: ((tt_activegroups_1.groupid)::text <> (tt_trouble_ticket_2.pilotgroupid)::text)
  • Buffers: shared hit=3247
25. 0.008 0.008 ↑ 181,008.0 1 1

Seq Scan on tt_activegroups tt_activegroups_1 (cost=0.00..14,464.86 rows=181,008 width=18) (actual time=0.008..0.008 rows=1 loops=1)

  • Filter: ((enddate IS NULL) AND ((roleid)::text = 'ACT'::text))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1
26. 0.001 17.998 ↓ 0.0 0 1

Hash (cost=6,230.95..6,230.95 rows=850 width=167) (actual time=17.998..17.998 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=3246
27. 8.485 17.997 ↓ 0.0 0 1

Bitmap Heap Scan on tt_trouble_ticket tt_trouble_ticket_2 (cost=1,181.42..6,230.95 rows=850 width=167) (actual time=17.997..17.997 rows=0 loops=1)

  • Recheck Cond: (((pilotgroupid)::text = 'ATQUOA'::text) AND ((natureid)::text = ANY ('{0,1,2,4,5,K}'::text[])))
  • Filter: ((id_diag_res IS NULL) AND ((currentstatus)::text = ANY ('{0,1,2,3,4}'::text[])))
  • Rows Removed by Filter: 3332
  • Heap Blocks: exact=3027
  • Buffers: shared hit=3246
28. 0.303 9.512 ↓ 0.0 0 1

BitmapAnd (cost=1,181.42..1,181.42 rows=1,782 width=0) (actual time=9.512..9.512 rows=0 loops=1)

  • Buffers: shared hit=219
29. 0.663 0.663 ↓ 1.0 3,338 1

Bitmap Index Scan on tt_trouble_ticket_pilotgroupid (cost=0.00..65.29 rows=3,316 width=0) (actual time=0.663..0.663 rows=3,338 loops=1)

  • Index Cond: ((pilotgroupid)::text = 'ATQUOA'::text)
  • Buffers: shared hit=12
30. 8.546 8.546 ↓ 1.0 68,906 1

Bitmap Index Scan on tt_trouble_ticket_natureid (cost=0.00..1,115.46 rows=67,857 width=0) (actual time=8.546..8.546 rows=68,906 loops=1)

  • Index Cond: ((natureid)::text = ANY ('{0,1,2,4,5,K}'::text[]))
  • Buffers: shared hit=207
31. 0.056 0.374 ↓ 1.0 280 1

Hash (cost=23.73..23.73 rows=278 width=8) (actual time=0.374..0.374 rows=280 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=15
32. 0.318 0.318 ↓ 1.0 280 1

Seq Scan on iai_params_specifiques_eds iai_params_specifiques_eds_2 (cost=0.00..23.73 rows=278 width=8) (actual time=0.024..0.318 rows=280 loops=1)

  • Filter: (((envoi_resu_diag)::text = 'on'::text) AND ((actif)::text = 'Actif'::text))
  • Rows Removed by Filter: 302
  • Buffers: shared hit=15