explain.depesz.com

PostgreSQL's explain analyze made readable

Result: L6Cl

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.439 21,784.346 ↓ 16.0 16 1

Nested Loop Left Join (cost=3.90..10,192.79 rows=1 width=246) (actual time=2,567.908..21,784.346 rows=16 loops=1)

  • Output: s1.id, s1.company_id, s1.name, (s1.integration_settings #>> '{iga,PRCTR}'::text[]), (s1.integration_settings #>> '{iga,KASA_NO}'::text[]), (s1.integration_settings #>> '{iga,TERMINAL_ID}'::text[]), d1.serial_number, (c1.integration_settings #>> '{iga,ingenicoApi,token}'::text[]), ((s1.integration_settings -> 'iga'::text) @> '{"enabled": true}'::jsonb), ((c1.integration_settings -> 'iga'::text) @> '{"enabled": true}'::jsonb), (SubPlan 2), (SubPlan 3)
  • Buffers: shared hit=6098599 read=385141
  • I/O Timings: read=8595.861
2. 0.284 0.579 ↓ 16.0 16 1

Hash Right Join (cost=3.63..14.65 rows=1 width=270) (actual time=0.354..0.579 rows=16 loops=1)

  • Output: s1.id, s1.company_id, s1.name, s1.integration_settings, c1.integration_settings
  • Hash Cond: ((c1.id)::text = s1.company_id)
  • Buffers: shared hit=27 read=2
  • I/O Timings: read=0.018
3. 0.123 0.123 ↑ 1.0 134 1

Seq Scan on public.company c1 (cost=0.00..10.34 rows=134 width=186) (actual time=0.020..0.123 rows=134 loops=1)

  • Output: c1.name, c1.logo, c1.id, c1.owner_id, c1.active, c1.created_at, c1.updated_at, c1.user_id, c1.imported_id, c1.settings, c1.created_by, c1.updated_by, c1.integration_settings
  • Buffers: shared hit=9
4. 0.018 0.172 ↓ 16.0 16 1

Hash (cost=3.61..3.61 rows=1 width=121) (actual time=0.172..0.172 rows=16 loops=1)

  • Output: s1.id, s1.company_id, s1.name, s1.integration_settings
  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
  • Buffers: shared hit=15 read=2
  • I/O Timings: read=0.018
5. 0.104 0.154 ↓ 16.0 16 1

Bitmap Heap Scan on public.shop s1 (cost=2.40..3.61 rows=1 width=121) (actual time=0.056..0.154 rows=16 loops=1)

  • Output: s1.id, s1.company_id, s1.name, s1.integration_settings
  • Recheck Cond: (s1.integration_settings ? 'iga'::text)
  • Heap Blocks: exact=14
  • Buffers: shared hit=15 read=2
  • I/O Timings: read=0.018
6. 0.050 0.050 ↓ 16.0 16 1

Bitmap Index Scan on shop_integration_settings_idx (cost=0.00..2.40 rows=1 width=0) (actual time=0.038..0.050 rows=16 loops=1)

  • Index Cond: (s1.integration_settings ? 'iga'::text)
  • Buffers: shared hit=1 read=2
  • I/O Timings: read=0.018
7. 0.784 0.784 ↑ 1.0 1 16

Index Scan using device_shop_id_device_model_id_idx on public.device d1 (cost=0.28..2.70 rows=1 width=47) (actual time=0.047..0.049 rows=1 loops=16)

  • Output: d1.is_simulator, d1.os_version, d1.app_version, d1.id, d1.owner_id, d1.active, d1.created_at, d1.updated_at, d1.user_id, d1.os_name, d1.serial_number, d1.shop_id, d1.device_type_id, d1.device_brand_id, d1.device_model_id, d1.name, d1.register_id, d1.ip_address, d1.properties, d1.mac_address, d1.created_by, d1.updated_by, d1.backup_device_id
  • Index Cond: ((d1.shop_id = (s1.id)::text) AND (d1.device_model_id = 4))
  • Buffers: shared hit=43 read=4
  • I/O Timings: read=0.554
8.          

SubPlan (forNested Loop Left Join)

9. 0.064 3.376 ↑ 1.0 1 16

Result (cost=0.31..0.32 rows=1 width=8) (actual time=0.211..0.211 rows=1 loops=16)

  • Output: $1
  • Buffers: shared hit=19 read=14
  • I/O Timings: read=2.901
10.          

Initplan (forResult)

11. 0.080 3.312 ↑ 1.0 1 16

Limit (cost=0.28..0.31 rows=1 width=8) (actual time=0.205..0.207 rows=1 loops=16)

  • Output: zr.report_date
  • Buffers: shared hit=19 read=14
  • I/O Timings: read=2.901
12. 3.232 3.232 ↑ 175.0 1 16

Index Only Scan using zreport_shop_id_report_date_idx on public.zreport zr (cost=0.28..6.18 rows=175 width=8) (actual time=0.201..0.202 rows=1 loops=16)

  • Output: zr.report_date
  • Index Cond: ((zr.shop_id = s1.id) AND (zr.report_date IS NOT NULL))
  • Heap Fetches: 0
  • Buffers: shared hit=19 read=14
  • I/O Timings: read=2.901
13. 21,779.168 21,779.168 ↑ 1.0 1 16

Aggregate (cost=10,175.09..10,175.10 rows=1 width=8) (actual time=1,361.197..1,361.198 rows=1 loops=16)

  • Output: max(o1.created_at)
  • Buffers: shared hit=6098510 read=385121
  • I/O Timings: read=8592.388