limit.sql

EXPLAIN ANALYZE
    SELECT
        ht.id, ht.transaction_hash, ht.ledger_sequence, ht.application_order, ht.account, ht.account_sequence, ht.max_fee,
        COALESCE(ht.fee_charged, ht.max_fee) as fee_charged, ht.operation_count, ht.tx_envelope, ht.tx_result,
        ht.tx_meta, ht.tx_fee_meta, ht.created_at, ht.updated_at, COALESCE(ht.successful, true) as successful,
        array_to_string(ht.signatures, ',') AS signatures, ht.memo_type, ht.memo, lower(ht.time_bounds) AS valid_after,
        upper(ht.time_bounds) AS valid_before, hl.closed_at AS ledger_close_time, ht.inner_transaction_hash, ht.fee_account,
        ht.new_max_fee, array_to_string(ht.inner_signatures, ',') AS inner_signatures
    FROM history_transactions ht
    LEFT JOIN history_ledgers hl
    ON ht.ledger_sequence = hl.sequence
    WHERE ht.transaction_hash = '099f9a1217b1446ddd9865844450497751151c4efa8306a001a53e5f29b8eeca'
    OR ht.inner_transaction_hash = '099f9a1217b1446ddd9865844450497751151c4efa8306a001a53e5f29b8eeca'
    ORDER BY ht.transaction_hash, ht.inner_transaction_hash
    LIMIT 1;
    
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=5893291.09..5893291.09 rows=1 width=1991) (actual time=0.055..0.055 rows=1 loops=1)
   ->  Sort  (cost=5893291.09..5897745.78 rows=1781876 width=1991) (actual time=0.054..0.054 rows=1 loops=1)
         Sort Key: ht.transaction_hash, ht.inner_transaction_hash
         Sort Method: quicksort  Memory: 27kB
         ->  Nested Loop Left Join  (cost=894.21..5884381.71 rows=1781876 width=1991) (actual time=0.040..0.042 rows=1 loops=1)
               ->  Bitmap Heap Scan on history_transactions ht  (cost=893.78..1790194.92 rows=1781876 width=1983) (actual time=0.023..0.024 rows=1 loops=1)
                     Recheck Cond: (((transaction_hash)::text = '099f9a1217b1446ddd9865844450497751151c4efa8306a001a53e5f29b8eeca'::text) OR ((inner_transaction_hash)::text = '099f9a1217b1446ddd9865844450497751151c4efa8306a001a53e5f29b8eeca'::text))
                     Heap Blocks: exact=1
                     ->  BitmapOr  (cost=893.78..893.78 rows=1781876 width=0) (actual time=0.021..0.021 rows=0 loops=1)
                           ->  Bitmap Index Scan on by_hash  (cost=0.00..1.71 rows=1 width=0) (actual time=0.018..0.018 rows=1 loops=1)
                                 Index Cond: ((transaction_hash)::text = '099f9a1217b1446ddd9865844450497751151c4efa8306a001a53e5f29b8eeca'::text)
                           ->  Bitmap Index Scan on by_inner_hash  (cost=0.00..1.13 rows=1781875 width=0) (actual time=0.001..0.001 rows=0 loops=1)
                                 Index Cond: ((inner_transaction_hash)::text = '099f9a1217b1446ddd9865844450497751151c4efa8306a001a53e5f29b8eeca'::text)
               ->  Index Scan using index_history_ledgers_on_sequence on history_ledgers hl  (cost=0.44..2.28 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=1)
                     Index Cond: (ht.ledger_sequence = sequence)
 Planning time: 0.259 ms
 Execution time: 0.115 ms
(17 rows)