HTML
SOURCE
QUERY
REFORMATTED QUERY
COMMENTS
STATS

#	exclusive	inclusive	rows x	rows	loops	read	node
1.	0.003	0.151	↑ 1.0	20	1	0	→ Limit (cost=0.45..125.48 rows=20 width=147) (actual time=0.127..0.151 rows=20 loops=1) Buffers: shared hit=41 read=2
2.	0.148	0.148	↑ 168.8	20 - 92	1	16 KB	→ Index Scan using transactions_pkey on transactions t0 (cost=0.45..21,105.88 rows=3,376 width=147) (actual time=0.126..0.148 rows=20 loops=1) Filter: (("references"[2] = '(USER,53)'::transaction_reference) AND (site_id = ANY ('{1,2,3,4,5,6,7,8,9,10,12}'::integer[]))) Rows Removed by Filter: 92 Buffers: shared hit=41 read=2

Planning time	:	0.134 ms
Execution time	:	0.167 ms

EXPLAIN (ANALYZE, BUFFERS)  SELECT t0."amount", t0."currency_code", t0."end_balance", t0."id", t0."inserted_at", t0."main_ref_type", t0."references", t0."updated_at" FROM "transactions" AS t0 WHERE (t0."site_id" = ANY(ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 12])) AND ("references"[2] = '(USER,53)'::transaction_reference) ORDER BY t0."id"  LIMIT 20 OFFSET 0;
                                                                    QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.45..125.48 rows=20 width=147) (actual time=0.127..0.151 rows=20 loops=1)
   Buffers: shared hit=41 read=2
   ->  Index Scan using transactions_pkey on transactions t0  (cost=0.45..21105.88 rows=3376 width=147) (actual time=0.126..0.148 rows=20 loops=1)
         Filter: (("references"[2] = '(USER,53)'::transaction_reference) AND (site_id = ANY ('{1,2,3,4,5,6,7,8,9,10,12}'::integer[])))
         Rows Removed by Filter: 92
         Buffers: shared hit=41 read=2
 Planning Time: 0.134 ms
 Execution Time: 0.167 ms
(8 rows)


EXPLAIN (ANALYZE, BUFFERS)  SELECT t0."amount", t0."currency_code", t0."end_balance", t0."id", t0."inserted_at", t0."main_ref_type", t0."references", t0."updated_at" FROM "transactions" AS t0 WHERE (t0."site_id" = ANY(ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 12])) AND ("references"[2] = '(USER,53)'::transaction_reference) ORDER BY t0."id"  LIMIT 20 OFFSET 0;

EXPLAIN (
    analyze,
    buffers
)
SELECT
    t0.amount,
    t0.currency_code,
    t0.end_balance,
    t0.id,
    t0.inserted_at,
    t0.main_ref_type,
    t0.references,
    t0.updated_at
FROM
    transactions AS t0
WHERE
    t0.site_id = ANY( ARRAY[ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 12 ] ) AND
    references[2] = '(USER,53)'::transaction_reference
ORDER BY
    t0.id
LIMIT 20
OFFSET 0;
-- Formatted by Pg::SQL::PrettyPrinter

 CREATE INDEX IF NOT EXISTS transaction_references_site_id_user_offset_index_new_2 ON transactions (("references"[2]), site_id, id);


ledger=# \d transactions;
                                          Table "public.transactions"
    Column     |            Type             | Collation | Nullable |                 Default
---------------+-----------------------------+-----------+----------+------------------------------------------
 id            | bigint                      |           | not null | nextval('transactions_id_seq'::regclass)
 site_id       | integer                     |           | not null |
 account_id    | bigint                      |           | not null |
 currency_code | global_currency_code        |           | not null |
 type          | transaction_type            |           | not null |
 references    | transaction_reference[]     |           | not null |
 amount        | numeric(28,12)              |           | not null |
 inserted_at   | timestamp without time zone |           | not null |
 updated_at    | timestamp without time zone |           | not null |
 main_ref_type | transaction_reference_type  |           |          |
 end_balance   | numeric(28,12)              |           |          |
Indexes:
    "transactions_pkey" PRIMARY KEY, btree (id)
    "transaction_references_site_id_user_offset_index_new" btree (("references"[2]), site_id)
    "transaction_references_site_id_user_offset_index_new_2" btree (("references"[2]), site_id, id)
    "transaction_site_id_references_user_offset_index" btree (site_id, ("references"[2]))
    "transactions_reference_gin" gin ("references")
Foreign-key constraints:
    "transactions_account_id_fkey" FOREIGN KEY (account_id) REFERENCES accounts(id)

I/O stats

Query read 16 KB bytes from disk (or system disk cache)

Per node type stats

node type	count	sum of times	% of query
Index Scan	1	0.148 ms	98.0 %
Limit	1	0.003 ms	2.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
transactions	1	0.148 ms	98.0 %
Index Scan	1	0.148 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JLZy

Color mode:

Visible columns:

I/O stats

Per node type stats

Per table stats