explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EVEa

Settings
# exclusive inclusive rows x rows loops node
1. 48.588 40,781.360 ↓ 0.0 0 1

Insert on transactions (cost=252.94..293.41 rows=191 width=476) (actual time=40,781.360..40,781.360 rows=0 loops=1)

2.          

CTE fx_table

3. 11.125 328.899 ↓ 10,540.7 31,622 1

Hash Join (cost=150.03..152.85 rows=3 width=48) (actual time=317.786..328.899 rows=31,622 loops=1)

  • Hash Cond: ((currencies.currency)::text = (trading_multi_forex.from_currency)::text)
4. 0.019 0.035 ↑ 11.8 17 1

HashAggregate (cost=25.62..27.62 rows=200 width=32) (actual time=0.029..0.035 rows=17 loops=1)

  • Group Key: (currencies.currency)::text
5. 0.016 0.016 ↑ 73.5 17 1

Seq Scan on currencies (cost=0.00..22.50 rows=1,250 width=32) (actual time=0.011..0.016 rows=17 loops=1)

6. 11.249 317.739 ↓ 5,270.3 31,622 1

Hash (cost=124.33..124.33 rows=6 width=48) (actual time=317.739..317.739 rows=31,622 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1986kB
7. 306.490 306.490 ↓ 5,270.3 31,622 1

Foreign Scan on trading_multi_forex (cost=100.00..124.33 rows=6 width=48) (actual time=2.990..306.490 rows=31,622 loops=1)

8. 57.502 40,732.772 ↓ 52.4 10,000 1

Nested Loop Left Join (cost=100.08..140.56 rows=191 width=476) (actual time=389.879..40,732.772 rows=10,000 loops=1)

9. 5.144 195.270 ↓ 52.4 10,000 1

Limit (cost=100.00..115.73 rows=191 width=564) (actual time=45.199..195.270 rows=10,000 loops=1)

10. 190.126 190.126 ↓ 52.4 10,000 1

Foreign Scan on proddb_transactions (cost=100.00..115.73 rows=191 width=564) (actual time=45.197..190.126 rows=10,000 loops=1)

11. 10.000 40,480.000 ↓ 0.0 0 10,000

Limit (cost=0.09..0.09 rows=1 width=16) (actual time=4.048..4.048 rows=0 loops=10,000)

12. 2,020.000 40,470.000 ↓ 0.0 0 10,000

Sort (cost=0.09..0.09 rows=1 width=16) (actual time=4.047..4.047 rows=0 loops=10,000)

  • Sort Key: fx_table.datetime DESC
  • Sort Method: top-N heapsort Memory: 25kB
13. 38,450.000 38,450.000 ↓ 740.0 740 10,000

CTE Scan on fx_table (cost=0.00..0.08 rows=1 width=16) (actual time=3.128..3.845 rows=740 loops=10,000)

  • Filter: ((datetime <= proddb_transactions.created_at) AND ((currency)::text = (proddb_transactions.native_currency)::text))
  • Rows Removed by Filter: 30882