Index Scan
using transaction_site_id_references_user_offset_index on transactions t0
(cost=0.42..5,169.27
rows=3,198
width=147)
(actual
time=0.047..3.050
rows=3,322
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))
CTE Scan
on x
(cost=0.00..63.96
rows=3,198
width=112)
(actual
time=0.050..4.759
rows=3,322
loops=1)
Buffers: shared hit=318
Planning time
:
0.147 ms
Execution time
:
5.494ms
ledger=# EXPlAIN (ANALYZE, BUFFERS) WITH x AS materialized ( 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) ) SELECT * FROM x ORDER BY "id" ASC LIMIT 20 OFFSET 0;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=5318.33..5318.38 rows=20 width=112) (actual time=5.343..5.349 rows=20 loops=1)
Buffers: shared hit=318
CTE x
-> Index Scan using transaction_site_id_references_user_offset_index on transactions t0 (cost=0.42..5169.27 rows=3198 width=147) (actual time=0.047..3.050 rows=3322 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=318
-> Sort (cost=149.06..157.05 rows=3198 width=112) (actual time=5.342..5.345 rows=20 loops=1)
Sort Key: x.id
Sort Method: top-N heapsort Memory: 34kB
Buffers: shared hit=318
-> CTE Scan on x (cost=0.00..63.96 rows=3198 width=112) (actual time=0.050..4.759 rows=3322 loops=1)
Buffers: shared hit=318
Planning Time: 0.147 ms
Execution Time: 5.494 ms
(14 rows)
ledger=# EXPlAIN (ANALYZE, BUFFERS) WITH x AS materialized ( 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) ) SELECT * FROM x ORDER BY "id" ASC LIMIT 20 OFFSET 0;
EXPLAIN (
analyze,
buffers
)
WITH x AS MATERIALIZED (
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
)
SELECT
*
FROM
x
ORDER BY
id ASC
LIMIT 20
OFFSET 0;
-- Formatted by Pg::SQL::PrettyPrinter
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)