Index Scan Backward
using transactions_pkey on transactions t0
(cost=0.45..21,105.82
rows=3,150
width=147)
(actual
time=7.014..263.234
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: 292,034
Buffers: shared hit=166,239
Planning time
:
0.143 ms
Execution time
:
263.295ms
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=0.45..134.45 rows=20 width=147) (actual time=7.049..263.273 rows=20 loops=1)
Buffers: shared hit=166239
-> Index Scan Backward using transactions_pkey on transactions t0 (cost=0.45..21105.82 rows=3150 width=147) (actual time=7.014..263.234 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: 292034
Buffers: shared hit=166239
Planning Time: 0.143 ms
Execution Time: 263.295 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;
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
CREATE INDEX IF NOT EXISTS transaction_site_id_references_user_offset_index_new ON transactions (("references"[2]), site_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_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)