explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hqfE : Optimization for: plan #zQa

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.075 10,867.625 ↑ 1.0 191 1

Limit (cost=2,519,208.71..2,519,313.71 rows=200 width=190) (actual time=10,866.466..10,867.625 rows=191 loops=1)

2.          

Initplan (for Limit)

3. 0.023 0.023 ↑ 1.0 1 1

Index Scan using unique_email_address on freelancers (cost=0.28..8.29 rows=1 width=4) (actual time=0.022..0.023 rows=1 loops=1)

  • Index Cond: ((email)::text = (current_setting('session.username'::text))::text)
4. 1.112 10,867.527 ↑ 310.4 191 1

Result (cost=2,519,200.42..2,550,330.29 rows=59,295 width=190) (actual time=10,866.464..10,867.527 rows=191 loops=1)

5. 0.210 10,866.415 ↑ 310.4 191 1

Sort (cost=2,519,200.42..2,519,348.65 rows=59,295 width=162) (actual time=10,866.372..10,866.415 rows=191 loops=1)

  • Sort Key: ((empty_text_to_null((customers_1.extra_info ->> 'typing_priority'::text)))::integer), (((receipts.id * 1000) + $0))
  • Sort Method: quicksort Memory: 57kB
6. 1.883 10,866.205 ↑ 310.4 191 1

Hash Join (cost=2,429,939.22..2,516,637.73 rows=59,295 width=162) (actual time=10,425.955..10,866.205 rows=191 loops=1)

  • Hash Cond: (receipts.customer_id = customers_1.id)
7. 2.734 9,715.432 ↑ 13.7 4,364 1

Merge Left Join (cost=2,425,909.75..2,496,886.45 rows=59,708 width=154) (actual time=8,820.769..9,715.432 rows=4,364 loops=1)

  • Merge Cond: (receipts.id = typed_values_1.receipt_id)
8. 7.883 9,707.516 ↑ 13.7 4,364 1

Merge Anti Join (cost=2,423,725.61..2,494,553.02 rows=59,708 width=101) (actual time=8,815.584..9,707.516 rows=4,364 loops=1)

  • Merge Cond: (receipts.id = typed_values_1_1.receipt_id)
9. 872.012 872.012 ↑ 25.2 4,732 1

Index Scan using receipts_pkey on receipts (cost=0.42..70,412.47 rows=119,417 width=101) (actual time=0.034..872.012 rows=4,732 loops=1)

  • Filter: (is_receipt_typed(amount, amount_original_currency, entry_date, deleted, checked) IS FALSE)
  • Rows Removed by Filter: 174,769
10. 9.929 8,827.621 ↓ 1.1 3,473 1

GroupAggregate (cost=2,423,725.19..2,423,781.14 rows=3,176 width=4) (actual time=8,815.545..8,827.621 rows=3,473 loops=1)

  • Group Key: typed_values_1_1.receipt_id
  • Filter: (count(*) > 1)
  • Rows Removed by Filter: 999
11. 14.021 8,817.692 ↓ 2.5 8,019 1

Sort (cost=2,423,725.19..2,423,733.26 rows=3,225 width=4) (actual time=8,815.538..8,817.692 rows=8,019 loops=1)

  • Sort Key: typed_values_1_1.receipt_id
  • Sort Method: quicksort Memory: 568kB
12. 8,803.671 8,803.671 ↓ 2.5 8,019 1

Index Scan using merged_into_master on typed_values typed_values_1_1 (cost=0.42..2,423,537.26 rows=3,225 width=4) (actual time=0.677..8,803.671 rows=8,019 loops=1)

  • Index Cond: (merged_into_master = false)
  • Filter: ((merged_into_master IS FALSE) AND ((the_user)::text IS DISTINCT FROM (current_setting('session.username'::text))::text) AND (double_entry.is_receipt_typed_in_fork(receipt_id, the_user, amount_original_currency, currency, entry_date, deleted, deleted_comment, typer_unsure, unsure_comment) IS TRUE))
  • Rows Removed by Filter: 2,745
13. 0.006 5.182 ↓ 0.0 0 1

Sort (cost=2,184.14..2,184.15 rows=1 width=57) (actual time=5.182..5.182 rows=0 loops=1)

  • Sort Key: typed_values_1.receipt_id
  • Sort Method: quicksort Memory: 25kB
14. 4.534 5.176 ↓ 0.0 0 1

Bitmap Heap Scan on typed_values typed_values_1 (cost=220.99..2,184.13 rows=1 width=57) (actual time=5.175..5.176 rows=0 loops=1)

  • Filter: ((merged_into_master IS FALSE) AND (((the_user)::text = (current_setting('session.username'::text))::text) OR (the_user IS NULL)))
  • Rows Removed by Filter: 10,764
  • Heap Blocks: exact=496
15. 0.642 0.642 ↓ 1.1 10,764 1

Bitmap Index Scan on merged_into_master (cost=0.00..220.99 rows=9,676 width=0) (actual time=0.642..0.642 rows=10,764 loops=1)

  • Index Cond: (merged_into_master = false)
16. 0.498 1,148.890 ↑ 12.4 359 1

Hash (cost=3,973.90..3,973.90 rows=4,445 width=43) (actual time=1,148.890..1,148.890 rows=359 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 90kB
17. 1,148.392 1,148.392 ↑ 12.4 359 1

Seq Scan on customers customers_1 (cost=0.00..3,973.90 rows=4,445 width=43) (actual time=1.598..1,148.392 rows=359 loops=1)

  • Filter: ((test_account IS FALSE) AND ((empty_text_to_null((extra_info ->> 'typing_priority'::text)))::integer <> '-1'::integer) AND ((((get_service_package_current_year(id, true))::text <> 'No service package paid for current year'::text) OR ((get_service_package_previous_year(id, true))::text <> 'No service package paid for previous year'::text)) IS TRUE))
  • Rows Removed by Filter: 4,118
Planning time : 1.444 ms
Execution time : 10,868.076 ms