HTML
SOURCE
QUERY
REFORMATTED QUERY
COMMENTS
STATS

#	exclusive	inclusive	rows x	rows	loops	node
1.	0.002	0.138	↓ 1.7	5	1	→ Limit (cost=44.80..44.81 rows=3 width=285) (actual time=0.136..0.138 rows=5 loops=1) Buffers: shared hit=27
2.	0.021	0.136	↓ 1.7	5	1	→ Sort (cost=44.80..44.81 rows=3 width=285) (actual time=0.135..0.136 rows=5 loops=1) Sort Key: id DESC Sort Method: quicksort Memory: 26kB Buffers: shared hit=27
3.	0.018	0.115	↓ 1.7	5	1	→ Bitmap Heap Scan on transactions t0 (cost=35.14..44.78 rows=3 width=285) (actual time=0.107..0.115 rows=5 loops=1) Recheck Cond: ((site_id = ANY ('{1,2,3,4,5,6,7,8,9,10,12}'::integer[])) AND ("references"[2] = '(USER,53)'::transaction_reference)) Heap Blocks: exact=5 Buffers: shared hit=27
4.	0.097	0.097	↓ 1.7	5	1	→ Bitmap Index Scan on transaction_site_id_references_user_offset_index (cost=0.00..35.13 rows=3 width=0) (actual time=0.097..0.097 rows=5 loops=1) Index Cond: ((site_id = ANY ('{1,2,3,4,5,6,7,8,9,10,12}'::integer[])) AND ("references"[2] = '(USER,53)'::transaction_reference)) Buffers: shared hit=22

Planning time	:	0.322 ms
Execution time	:	0.180 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" DESC  LIMIT 20 OFFSET 0;
                                                                              QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=44.80..44.81 rows=3 width=285) (actual time=0.136..0.138 rows=5 loops=1)
   Buffers: shared hit=27
   ->  Sort  (cost=44.80..44.81 rows=3 width=285) (actual time=0.135..0.136 rows=5 loops=1)
         Sort Key: id DESC
         Sort Method: quicksort  Memory: 26kB
         Buffers: shared hit=27
         ->  Bitmap Heap Scan on transactions t0  (cost=35.14..44.78 rows=3 width=285) (actual time=0.107..0.115 rows=5 loops=1)
               Recheck Cond: ((site_id = ANY ('{1,2,3,4,5,6,7,8,9,10,12}'::integer[])) AND ("references"[2] = '(USER,53)'::transaction_reference))
               Heap Blocks: exact=5
               Buffers: shared hit=27
               ->  Bitmap Index Scan on transaction_site_id_references_user_offset_index  (cost=0.00..35.13 rows=3 width=0) (actual time=0.097..0.097 rows=5 loops=1)
                     Index Cond: ((site_id = ANY ('{1,2,3,4,5,6,7,8,9,10,12}'::integer[])) AND ("references"[2] = '(USER,53)'::transaction_reference))
                     Buffers: shared hit=22
 Planning Time: 0.322 ms
 Execution Time: 0.180 ms
(15 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" DESC  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 DESC
LIMIT 20
OFFSET 0;
-- Formatted by Pg::SQL::PrettyPrinter

\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  |           | not null |
 end_balance   | numeric(28,12)              |           |          |
Indexes:
    "transactions_pkey" PRIMARY KEY, btree (id)
    "transaction_site_id_references_user_offset_idx" btree (site_id, ("references"[2]))
    "transaction_site_id_references_user_offset_index" btree (site_id, ("references"[2]))
    "transactions_reference_gin" gin ("references")
    "transactions_unique_index" UNIQUE, btree (site_id, account_id, type, "references", main_ref_type)
Foreign-key constraints:
    "transactions_account_id_fkey" FOREIGN KEY (account_id) REFERENCES accounts(id)

I/O stats

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	0.018 ms	13.0 %
Bitmap Index Scan	1	0.097 ms	70.3 %
Limit	1	0.002 ms	1.4 %
Sort	1	0.021 ms	15.2 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
transactions	1	0.018 ms	13.0 %
Bitmap Heap Scan	1	0.018 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QqP1

Color mode:

Visible columns:

I/O stats

Per node type stats

Per table stats