explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cFMs

Settings
# exclusive inclusive rows x rows loops node
1. 11.056 9,015.376 ↓ 26.7 50,000 1

Limit (cost=113,934.35..114,217.80 rows=1,870 width=61) (actual time=8,918.180..9,015.376 rows=50,000 loops=1)

  • Buffers: shared hit=427,701 read=19,084
2. 18.758 9,004.320 ↓ 26.7 50,000 1

Finalize GroupAggregate (cost=113,934.35..114,217.80 rows=1,870 width=61) (actual time=8,918.178..9,004.320 rows=50,000 loops=1)

  • Group Key: tickets_ticket_updates.ticket_updates_ticket_id, tfb_1.value, tfb_2.value, __w_ticket_field_changes.field_name, __w_ticket_field_changes.new_value, (timezone('America/Los_Angeles'::text, timezone('UTC'::text, tickets_ticket_updates.ticket_updates_ticket_updated_at))), (timezone('America/Los_Angeles'::text, timezone('UTC'::text, tickets_ticket_updates.tickets_created_at)))
  • Buffers: shared hit=151,883 read=6,326
3. 86.198 8,985.562 ↓ 32.1 50,003 1

Gather Merge (cost=113,934.35..114,149.24 rows=1,558 width=61) (actual time=8,918.170..8,985.562 rows=50,003 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=427,701 read=19,084
4. 10.878 8,899.364 ↓ 22.0 17,155 3 / 3

Partial GroupAggregate (cost=112,934.33..112,969.39 rows=779 width=61) (actual time=8,886.307..8,899.364 rows=17,155 loops=3)

  • Group Key: tickets_ticket_updates.ticket_updates_ticket_id, tfb_1.value, tfb_2.value, __w_ticket_field_changes.field_name, __w_ticket_field_changes.new_value, (timezone('America/Los_Angeles'::text, timezone('UTC'::text, tickets_ticket_updates.ticket_updates_ticket_updated_at))), (timezone('America/Los_Angeles'::text, timezone('UTC'::text, tickets_ticket_updates.tickets_created_at)))
  • Buffers: shared hit=427,701 read=19,084
5. 48.204 8,888.486 ↓ 22.1 17,197 3 / 3

Sort (cost=112,934.33..112,936.28 rows=779 width=70) (actual time=8,886.298..8,888.486 rows=17,197 loops=3)

  • Sort Key: tickets_ticket_updates.ticket_updates_ticket_id, tfb_1.value, tfb_2.value, __w_ticket_field_changes.field_name, __w_ticket_field_changes.new_value, (timezone('America/Los_Angeles'::text, timezone('UTC'::text, tickets_ticket_updates.ticket_updates_ticket_updated_at))), (timezone('America/Los_Angeles'::text, timezone('UTC'::text, tickets_ticket_updates.tickets_created_at)))
  • Sort Method: quicksort Memory: 3,279kB
  • Buffers: shared hit=427,701 read=19,084
6. 47.129 8,840.282 ↓ 25.3 19,737 3 / 3

Nested Loop Left Join (cost=140.27..112,896.92 rows=779 width=70) (actual time=2.068..8,840.282 rows=19,737 loops=3)

  • Buffers: shared hit=427,641 read=19,084
7. 10.791 7,007.985 ↓ 38.6 11,088 3 / 3

Nested Loop Left Join (cost=139.84..110,735.27 rows=287 width=34) (actual time=1.837..7,007.985 rows=11,088 loops=3)

  • Buffers: shared hit=313,889 read=15,033
8. 6.243 6,853.050 ↓ 41.7 11,088 3 / 3

Nested Loop Left Join (cost=139.41..108,007.75 rows=266 width=41) (actual time=1.820..6,853.050 rows=11,088 loops=3)

  • Buffers: shared hit=164,379 read=14,993
9. 27.108 6,092.823 ↓ 42.6 11,088 3 / 3

Hash Left Join (cost=138.98..105,341.75 rows=260 width=40) (actual time=1.807..6,092.823 rows=11,088 loops=3)

  • Hash Cond: (tickets_ticket_updates.tickets_organization_id = organizations.id)
  • Filter: (CASE WHEN (NOT (CASE WHEN (strpos(organizations.name, '_deleted_'::text) > 0) THEN true ELSE false END IS DISTINCT FROM true)) THEN ('~'::text || organizations.name) ELSE organizations.name END = ANY ('{"Advanced Digital Services (ADS) - NPFP","Deluxe AU - NPFP","Deluxe ES - NPFP + NP3","Deluxe LA - NPFP + NP3 AD","Deluxe UK - NPFP","Dicentia Studios - NPFP + NP3","Digital Studio & DVD - NPFP","Encripta - NPFP","Enteractive GmbH - NPFP","FOTOKEM - NPFP + NP3","Hiventy Asia - NPFP + NP3","Hiventy Europe - NPFP + NP3","IMAGION AG - NPFP","International Digital Centre (IDC) - NPFP + NP3","IYUNO Media Group - NPFP + NP3","Juice Worldwide - NPFP","ODMedia - NPFP","Oxobox - NPFP","Plint - NPFP","PONYCANYON ENTERPRISE - NPFP + NP3","Prime Focus Technologies India - NPFP + NP3","Silver Trak Digital - NPFP + NP3","Sony PCL - NPFP + NP3","VDM - NPFP + NP3","Vista India - NPFP","Visual Data EU - NPFP + NP3","Visual Data US - NPFP + NP3","ZOO Digital - NPFP","Pixelogic (fka: Radius 60)","Pixelogic EMEA"}'::text[]))
  • Rows Removed by Filter: 295
  • Buffers: shared hit=16,123 read=13,697
10. 6,063.982 6,063.982 ↓ 6.6 11,383 3 / 3

Parallel Index Scan using tickets_ticket_updates_tickets_brand on tickets_ticket_updates (cost=0.42..105,198.60 rows=1,732 width=48) (actual time=0.023..6,063.982 rows=11,383 loops=3)

  • Index Cond: (tickets_brand = 'NPFP Project Management'::text)
  • Filter: ((tickets_created_at >= '2018-09-01 07:00:00+00'::timestamp with time zone) AND (tickets_created_at < '2019-11-01 07:00:00+00'::timestamp with time zone) AND (tickets_form = 'Onboarding Form [GPC]'::text) AND (tickets_status = ANY ('{New,Open,Pending,Solved,Closed}'::text[])))
  • Rows Removed by Filter: 16,011
  • Buffers: shared hit=15,885 read=13,697
11. 0.923 1.733 ↓ 1.0 2,871 3 / 3

Hash (cost=102.69..102.69 rows=2,869 width=40) (actual time=1.733..1.733 rows=2,871 loops=3)

  • Buckets: 4,096 Batches: 1 Memory Usage: 194kB
  • Buffers: shared hit=222
12. 0.810 0.810 ↓ 1.0 2,871 3 / 3

Seq Scan on organizations (cost=0.00..102.69 rows=2,869 width=40) (actual time=0.067..0.810 rows=2,871 loops=3)

  • Buffers: shared hit=222
13. 753.984 753.984 ↑ 1.0 1 33,264 / 3

Index Scan using ticket_fields_boolean_entity_id on ticket_fields_boolean tfb_2 (cost=0.43..10.24 rows=1 width=9) (actual time=0.065..0.068 rows=1 loops=33,264)

  • Index Cond: (tickets_ticket_updates.tickets_id = entity_id)
  • Filter: (custom_field_id = '360000026846'::bigint)
  • Rows Removed by Filter: 47
  • Buffers: shared hit=148,256 read=1,296
14. 144.144 144.144 ↑ 1.0 1 33,264 / 3

Index Scan using ticket_fields_boolean_entity_id on ticket_fields_boolean tfb_1 (cost=0.43..10.24 rows=1 width=9) (actual time=0.005..0.013 rows=1 loops=33,264)

  • Index Cond: (tickets_ticket_updates.tickets_id = entity_id)
  • Filter: (custom_field_id = '360000027963'::bigint)
  • Rows Removed by Filter: 47
  • Buffers: shared hit=149,510 read=40
15. 1,785.168 1,785.168 ↑ 15.0 1 33,264 / 3

Index Scan using __w_ticket_field_changes_ticket_update_id on __w_ticket_field_changes (cost=0.43..7.35 rows=15 width=44) (actual time=0.151..0.161 rows=1 loops=33,264)

  • Index Cond: (tickets_ticket_updates.ticket_updates_id = ticket_update_id)
  • Buffers: shared hit=113,752 read=4,051