explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rLo6 : Optimization for: plan #L6Cl

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.432 22,797.975 ↓ 16.0 16 1

Nested Loop Left Join (cost=3.90..10,193.99 rows=1 width=246) (actual time=3,038.121..22,797.975 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.
  • Buffers: shared hit=5900090 read=583650
  • I/O Timings: read=9415.994
2. 0.258 0.647 ↓ 16.0 16 1

Hash Right Join (cost=3.63..14.65 rows=1 width=270) (actual time=0.429..0.647 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.028
3. 0.130 0.130 ↑ 1.0 134 1

Seq Scan on public.company c1 (cost=0.00..10.34 rows=134 width=186) (actual time=0.021..0.130 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.025 0.259 ↓ 16.0 16 1

Hash (cost=3.61..3.61 rows=1 width=121) (actual time=0.259..0.259 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.028
5. 0.159 0.234 ↓ 16.0 16 1

Bitmap Heap Scan on public.shop s1 (cost=2.40..3.61 rows=1 width=121) (actual time=0.099..0.234 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.028
6. 0.075 0.075 ↓ 16.0 16 1

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

  • Index Cond: (s1.integration_settings ? 'iga'::text)
  • Buffers: shared hit=1 read=2
  • I/O Timings: read=0.028
7. 0.320 0.320 ↑ 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.018..0.020 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=41 read=6
  • I/O Timings: read=0.048
8.          

SubPlan (forNested Loop Left Join)

9. 0.064 0.592 ↑ 1.0 1 16

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

  • Output: $1
  • Buffers: shared hit=17 read=16
  • I/O Timings: read=0.136
10.          

Initplan (forResult)

11. 0.080 0.528 ↑ 1.0 1 16

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

  • Output: zr.report_date
  • Buffers: shared hit=17 read=16
  • I/O Timings: read=0.136
12. 0.448 0.448 ↑ 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.027..0.028 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=17 read=16
  • I/O Timings: read=0.136
13. 522.624 22,795.984 ↑ 1.0 1 16

Aggregate (cost=10,176.28..10,176.29 rows=1 width=8) (actual time=1,424.748..1,424.749 rows=1 loops=16)

  • Output: max(o1.created_at)
  • Buffers: shared hit=5900005 read=583626
  • I/O Timings: read=9415.782
14. 1,989.768 22,273.360 ↓ 20.9 71,106 16

Nested Loop (cost=1.11..10,167.79 rows=3,398 width=8) (actual time=0.063..1,392.085 rows=71,106 loops=16)

  • Output: o1.created_at
  • Inner Unique: true
  • Buffers: shared hit=5900005 read=583626
  • I/O Timings: read=9415.782
15. 942.624 942.624 ↓ 20.9 71,106 16

Index Only Scan using erp_log_shop_id_order_id_idx on public.erp_log e1 (cost=0.55..117.79 rows=3,408 width=37) (actual time=0.033..58.914 rows=71,106 loops=16)

  • Output: e1.shop_id, e1.order_id
  • Index Cond: (e1.shop_id = s1.id)
  • Heap Fetches: 0
  • Buffers: shared hit=770573 read=14431
  • I/O Timings: read=86.111
16. 19,340.968 19,340.968 ↑ 1.0 1 1,137,704

Index Scan using order_pkey on public."order" o1 (cost=0.56..2.95 rows=1 width=45) (actual time=0.017..0.017 rows=1 loops=1,137,704)

  • Output: o1.payment_type, o1.amount_type_id, o1.total_amount, o1.cashier_id, o1.note, o1.cancelled, o1.id, o1.owner_id, o1.active, o1.created_at, o1.updated_at, o1.shop_id, o1.shift_id, o1.raw_amount, o1.entered_discount, o1.actual_discount, o1.discount_type, o1.shop_revenue_id, o1.check_id, o1.customer_id, o1.register_id, o1.table_id, o1.seq_id, o1.closed, o1.total_price, o1.payment_device_id, o1.created_by, o1.updated_by, o1.state, o1.paid_amount, o1.conversion_done, o1.customer_address_id, o1.courier_id, o1.moved_to_id, o1.z_no, o1.f_no, o1.discount_date_id, o1.moved_from_id, o1.order_items, o1.order_customers, o1.discounts, o1.tax_free, o1.vat_ratio
  • Index Cond: (o1.id = e1.order_id)
  • Buffers: shared hit=5129432 read=569195
  • I/O Timings: read=9329.671
Planning time : 4.863 ms
Execution time : 22,798.252 ms