Index Scan
using transaction_site_id_references_user_offset_index on transactions t0
(cost=0.42..15.06
rows=1
width=163)
(actual
time=0.017..0.017
rows=0
loops=1)
Index Cond: ((site_id = ANY ('{1,2,3}'::integer[])) AND ("references"[2] = '(USER,53)'::transaction_reference))
Planning time
:
0.106 ms
Execution time
:
0.043ms
# EXPLAIN ANALYZE SELECT t0.* FROM "transactions" AS t0 WHERE ("references"[2] = '(USER,53)'::transaction_reference) AND t0."site_id" = ANY(ARRAY[1,2,3]) ORDER BY t0."id" DESC LIMIT 20 OFFSET 0;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=15.07..15.07 rows=1 width=163) (actual time=0.021..0.022 rows=0 loops=1)
-> Sort (cost=15.07..15.07 rows=1 width=163) (actual time=0.021..0.021 rows=0 loops=1)
Sort Key: id DESC
Sort Method: quicksort Memory: 25kB
-> Index Scan using transaction_site_id_references_user_offset_index on transactions t0 (cost=0.42..15.06 rows=1 width=163) (actual time=0.017..0.017 rows=0 loops=1)
Index Cond: ((site_id = ANY ('{1,2,3}'::integer[])) AND ("references"[2] = '(USER,53)'::transaction_reference))
Planning Time: 0.106 ms
Execution Time: 0.043 ms
(8 rows)
# EXPLAIN ANALYZE SELECT t0.* FROM "transactions" AS t0 WHERE ("references"[2] = '(USER,53)'::transaction_reference) AND t0."site_id" = ANY(ARRAY[1,2,3]) ORDER BY t0."id" DESC LIMIT 20 OFFSET 0;
EXPLAIN ANALYZE
SELECT
t0.*
FROM
transactions AS t0
WHERE
references[2] = '(USER,53)'::transaction_reference AND
t0.site_id = ANY( ARRAY[ 1, 2, 3 ] )
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 | | |
end_balance | numeric(28,12) | | |
Indexes:
"transactions_pkey" PRIMARY KEY, btree (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)