explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nzfo : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #mXUn; plan #tlEd; plan #WdcKX; plan #5mnb; plan #ZbNF; plan #IAfHJ; plan #HSow; plan #5Tvl; plan #1ygo; plan #Qea8; plan #0meo; plan #tjhH; plan #PwQh; plan #gu6F; plan #FFvk

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 3.250 639.864 ↓ 2,105.8 10,529 1

Sort (cost=3,921.10..3,921.11 rows=5 width=12) (actual time=639.559..639.864 rows=10,529 loops=1)

  • Sort Method: quicksort Memory: 878kB
  • Sort Key: ar.created_at DESC
2. 14.287 636.614 ↓ 2,105.8 10,529 1

Group (cost=3,921.02..3,921.04 rows=5 width=12) (actual time=606.077..636.614 rows=10,529 loops=1)

  • Group Key: ar.id
3. 78.758 622.327 ↓ 33,966.6 169,833 1

Sort (cost=3,921.02..3,921.03 rows=5 width=12) (actual time=606.073..622.327 rows=169,833 loops=1)

  • Sort Method: external merge Disk: 4320kB
  • Sort Key: ar.id
4. 112.214 543.569 ↓ 33,966.6 169,833 1

Nested Loop (cost=2,850.32..3,920.96 rows=5 width=12) (actual time=20.992..543.569 rows=169,833 loops=1)

5. 261.522 261.522 ↓ 42,458.2 169,833 1

Nested Loop (cost=2,850.04..3,919.76 rows=4 width=16) (actual time=20.975..261.522 rows=169,833 loops=1)

6. 0.000 169.833 ↑ 1.0 1 169,833

Index Only Scan using pk_functional_structures_users on functional_structures_users fsu (cost=0.28..0.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=169,833)

  • Index Cond: ((functional_structure_id = sp.functional_structure_id) AND (user_id = 1))
  • Heap Fetches: 169833
7. 83.407 83.407 ↓ 34,504.0 34,504 1

Nested Loop (cost=2,849.62..3,918.71 rows=1 width=16) (actual time=20.965..83.407 rows=34,504 loops=1)

8. 32.866 103.512 ↑ 1.0 5 34,504

Index Scan using index_sample_parameters_on_sample_id_and_parameter_id on sample_parameters sp (cost=0.42..1.00 rows=5 width=8) (actual time=0.002..0.003 rows=5 loops=34,504)

  • Join Filter: (ar.work_order_id = sf.work_order_id)
  • Index Cond: (sample_id = sf.id)
9. 31.596 31.596 ↓ 1.5 3 10,532

Index Scan using index_samples_on_work_order_id_and_position on samples sf (cost=0.29..0.64 rows=2 width=8) (actual time=0.002..0.003 rows=3 loops=10,532)

10. 7.637 39.050 ↓ 10,532.0 10,532 1

Hash Join (cost=2,849.34..3,918.05 rows=1 width=20) (actual time=20.952..39.050 rows=10,532 loops=1)

  • Rows Removed by Join Filter: 1947
  • Join Filter: (((ar.sample_id IS NULL) AND (ar.serial = wo.report_serial) AND (ar.edition = wo.report_edition)) OR ((ar.sample_id IS NOT NULL) AND (ar.serial = s.report_serial) AND (ar.edition = s.report_edition)))
  • Index Cond: (work_order_id = wo.id)
  • Hash Cond: (ar.work_order_id = wo.id)
11. 22.204 22.204 ↓ 1.3 12,479 1

Hash Left Join (cost=1,787.28..2,830.55 rows=9,691 width=36) (actual time=11.668..22.204 rows=12,479 loops=1)

12. 0.000 9.209 ↑ 1.0 15,360 1

Hash (cost=865.36..865.36 rows=15,736 width=12) (actual time=9.209..9.209 rows=15,360 loops=1)

  • Hash Cond: (ar.sample_id = s.id)
  • Buckets: 16384 Batches: 1 Memory Usage: 730kB
13. 6.585 6.585 ↑ 1.0 15,360 1

Seq Scan on work_orders wo (cost=0.00..865.36 rows=15,736 width=12) (actual time=0.011..6.585 rows=15,360 loops=1)

14. 10.741 10.741 ↓ 1.0 21,643 1

Hash (cost=1,303.37..1,303.37 rows=21,637 width=12) (actual time=10.740..10.741 rows=21,643 loops=1)

15. 0.000 7.660 ↓ 1.3 12,479 1

Bitmap Heap Scan on analytical_reports ar (cost=213.44..1,231.28 rows=9,691 width=28) (actual time=0.756..7.660 rows=12,479 loops=1)

  • Recheck Cond: (NOT preview)
  • Heap Blocks: exact=815
  • Filter: (((sample_id IS NULL) OR (sample_id IS NOT NULL)) AND (tenant_id = 1))
  • Buckets: 32768 Batches: 1 Memory Usage: 1038kB
16. 7.169 7.169 ↓ 1.0 21,643 1

Seq Scan on samples s (cost=0.00..1,303.37 rows=21,637 width=12) (actual time=0.013..7.169 rows=21,643 loops=1)

17. 0.659 0.659 ↓ 1.0 12,554 1

Bitmap Index Scan on index_analytical_reports_on_preview (cost=0.00..211.02 rows=12,547 width=0) (actual time=0.659..0.659 rows=12,554 loops=1)

Planning time : 3.665 ms
Execution time : 643.282 ms