Index Scan
using transactions_pkey on transactions t0
(cost=0.45..21,105.91
rows=3,198
width=147)
(actual
time=0.099..0.121
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
Planning time
:
0.147 ms
Execution time
:
0.141ms
ledger=# EXPLAIN ANALYZE WITH x AS ( 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=0.45..132.44 rows=20 width=147) (actual time=0.100..0.124 rows=20 loops=1)
-> Index Scan using transactions_pkey on transactions t0 (cost=0.45..21105.91 rows=3198 width=147) (actual time=0.099..0.121 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
Planning Time: 0.147 ms
Execution Time: 0.141 ms
(6 rows)
ledger=# EXPLAIN ANALYZE WITH x AS ( 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
WITH x AS (
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)