explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pQ18

Settings
# exclusive inclusive rows x rows loops node
1. 5.409 4,287.512 ↑ 9.7 10,000 1

Limit (cost=2,379,717.35..2,379,959.22 rows=96,750 width=1,656) (actual time=4,274.549..4,287.512 rows=10,000 loops=1)

2.          

CTE t1

3. 4.137 4.137 ↑ 1.7 10,000 1

Index Scan using raw_file_records_batch_id_mapping_batch_id_idx on raw_file_records_batch_id_mapping (cost=0.57..532.69 rows=17,158 width=8) (actual time=0.017..4.137 rows=10,000 loops=1)

  • Index Cond: (batch_id = 156959)
4. 28.758 4,282.103 ↑ 9.7 10,000 1

Sort (cost=2,379,184.65..2,379,426.53 rows=96,750 width=1,656) (actual time=4,274.547..4,282.103 rows=10,000 loops=1)

  • Sort Key: r.global_line_no
  • Sort Method: external sort Disk: 3824kB
5. 247.068 4,253.345 ↑ 9.7 10,000 1

Merge Join (cost=2,252,687.15..2,271,962.80 rows=96,750 width=1,656) (actual time=4,214.765..4,253.345 rows=10,000 loops=1)

  • Merge Cond: (t.raw_record_id = r.raw_record_id)
6. 2,442.614 3,901.511 ↑ 2.6 750,565 1

Sort (cost=343,466.77..348,330.70 rows=1,945,573 width=21) (actual time=3,581.564..3,901.511 rows=750,565 loops=1)

  • Sort Key: t.raw_record_id
  • Sort Method: external merge Disk: 86928kB
7. 1,458.897 1,458.897 ↓ 1.3 2,616,226 1

Index Scan using raw_file_record_type_fc_instance_id_idx on raw_file_record_type t (cost=0.56..83,234.51 rows=1,945,573 width=21) (actual time=0.014..1,458.897 rows=2,616,226 loops=1)

  • Index Cond: (fc_instance_id = 99189)
  • Filter: ((record_type)::text = 'DATA'::text)
8. 6.044 104.766 ↑ 115.4 10,000 1

Materialize (cost=1,909,151.42..1,914,920.71 rows=1,153,858 width=1,667) (actual time=93.018..104.766 rows=10,000 loops=1)

9. 27.671 98.722 ↑ 115.4 10,000 1

Sort (cost=1,909,151.42..1,912,036.07 rows=1,153,858 width=1,667) (actual time=93.013..98.722 rows=10,000 loops=1)

  • Sort Key: r.raw_record_id
  • Sort Method: external merge Disk: 3920kB
10. 11.295 71.051 ↑ 115.4 10,000 1

Nested Loop (cost=386.50..1,331.76 rows=1,153,858 width=1,667) (actual time=14.995..71.051 rows=10,000 loops=1)

11. 9.531 19.756 ↓ 50.0 10,000 1

HashAggregate (cost=386.06..388.06 rows=200 width=8) (actual time=14.975..19.756 rows=10,000 loops=1)

  • Group Key: t1.raw_record_id
12. 10.225 10.225 ↑ 1.7 10,000 1

CTE Scan on t1 (cost=0.00..343.16 rows=17,158 width=8) (actual time=0.019..10.225 rows=10,000 loops=1)

13. 40.000 40.000 ↑ 1.0 1 10,000

Index Scan using raw_file_records_raw_record_id_idx on raw_file_records r (cost=0.44..4.71 rows=1 width=1,659) (actual time=0.003..0.004 rows=1 loops=10,000)

  • Index Cond: (raw_record_id = t1.raw_record_id)
  • Filter: (fc_instance_id = 99189)
  • Rows Removed by Filter: 1