Table "public.friends_info"
Column | Type | Collation | Nullable | Default
-----------------+--------+-----------+----------+---------
user_id | bigint | | |
friend_id | bigint | | |
unix_time_check | bigint | | |
Indexes:
"friends_info_user_id_friend_id_key" UNIQUE CONSTRAINT, btree (user_id, friend_id)
"ix_friends_friend_id" btree (friend_id)
"ix_friends_user_id" btree (user_id)EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM friends_info LEFT JOIN i ON friends_info.friend_id = i.user_id WHERE friends_info.user_id = 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1063.49..41700.28 rows=5465 width=644) (actual time=3.039..18.789 rows=88 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=447 read=1
-> Nested Loop Left Join (cost=63.49..40153.78 rows=2277 width=644) (actual time=0.657..2.055 rows=29 loops=3)
Buffers: shared hit=447 read=1
-> Parallel Bitmap Heap Scan on friends_info (cost=62.92..20715.73 rows=2277 width=24) (actual time=0.621..0.629 rows=29 loops=3)
Recheck Cond: (user_id = 1)
Heap Blocks: exact=1
Buffers: shared hit=7 read=1
-> Bitmap Index Scan on ix_friends_user_id (cost=0.00..61.56 rows=5465 width=0) (actual time=0.742..0.743 rows=88 loops=1)
Index Cond: (user_id = 1)
Buffers: shared hit=6 read=1
-> Index Scan using total on i (cost=0.57..8.54 rows=1 width=620) (actual time=0.046..0.046 rows=1 loops=88)
Index Cond: (user_id = friends_info.friend_id)
Buffers: shared hit=440
Planning:
Buffers: shared hit=242
Planning Time: 2.748 ms
Execution Time: 18.919 ms
(20 rows)EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM friends_info LEFT JOIN i ON friends_info.friend_id = i.user_id WHERE friends_info.friend_id= 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=1.14..85.73 rows=18 width=644) (actual time=0.267..0.892 rows=28 loops=1)
Join Filter: (friends_info.friend_id = i.user_id)
Buffers: shared hit=37
-> Index Scan using ix_friends_friend_id on friends_info (cost=0.57..76.88 rows=18 width=24) (actual time=0.196..0.761 rows=28 loops=1)
Index Cond: (friend_id = 1)
Buffers: shared hit=32
-> Materialize (cost=0.57..8.59 rows=1 width=620) (actual time=0.002..0.002 rows=1 loops=28)
Buffers: shared hit=5
-> Index Scan using total on i (cost=0.57..8.59 rows=1 width=620) (actual time=0.047..0.048 rows=1 loops=1)
Index Cond: (user_id = 1)
Buffers: shared hit=5
Planning:
Buffers: shared hit=3
Planning Time: 0.644 ms
Execution Time: 1.010 ms
(15 rows)set enable_bitmapscan = off;CLUSTER index ON my_table;
REINDEX my_index;
DROP INDEX/CREATE INDEX
ANALYZE/VACUUM ANALYZE
Я выполняю 2 одинаковых запроса
Bitmap Index Scan on ix_friends_user_id (cost=0.00..61.56 rows=5465 width=0) (actual time=0.742..0.743 rows=88 loops=1)
Adding a unique constraint will automatically create a unique B-tree index on the column or group of columns listed in the constraint. A uniqueness restriction covering only some rows cannot be written as a unique constraint, but it is possible to enforce such a restriction by creating a unique partial index.
-- DROP TABLE IF EXISTS public.friends_info;
CREATE TABLE IF NOT EXISTS public.friends_info (
user_id bigint
, friend_id bigint
, unix_time_check bigint
);
ALTER TABLE IF EXISTS public.friends_info
ADD CONSTRAINT friends_info_user_id_friend_id_key UNIQUE (user_id, friend_id);
CREATE INDEX IF NOT EXISTS ix_friends_friend_id ON public.friends_info (friend_id);
CREATE INDEX IF NOT EXISTS ix_friends_user_id ON public.friends_info (user_id);
WITH EncodedData AS (
SELECT gs.rownum
, gen_random_bytes(4) AS RandBytes
FROM generate_series(1,1000000) gs (rownum)
WHERE NOT EXISTS (
SELECT 1
FROM public.friends_info
LIMIT 1
)
), DecodedData AS (
SELECT rownum
,(get_byte(RandBytes, 0)::bigint + (get_byte(RandBytes, 1)::bigint << 8) + (get_byte(RandBytes, 2)::bigint << 16) + (get_byte(RandBytes, 3)::bigint << 24))::bigint
AS RandBigInt
FROM EncodedData
), PreparedData AS (
SELECT rownum AS user_id
, (RandBigInt % 1000000) + 1 AS friend_id
, RandBigInt AS unix_time_check
FROM DecodedData
)
INSERT INTO public.friends_info (
user_id
, friend_id
, unix_time_check
) SELECT
user_id
, friend_id
, unix_time_check
FROM PreparedData;
-- EXPLAIN (ANALYZE,BUFFERS)
SELECT *
FROM friends_info
LEFT JOIN friends_info i ON friends_info.friend_id = i.user_id
WHERE friends_info.user_id = 1;
CREATE UNIQUE INDEX IF NOT EXISTS friends_info_user_id_friend_id_key2
ON public.friends_info (user_id, friend_id) INCLUDE (unix_time_check);
-- EXPLAIN (ANALYZE,BUFFERS)
SELECT *
FROM friends_info
LEFT JOIN friends_info i ON friends_info.friend_id = i.user_id
WHERE friends_info.user_id = 1;
-- EXPLAIN (ANALYZE,BUFFERS)
SELECT *
FROM friends_info
LEFT JOIN friends_info i
ON friends_info.friend_id = i.user_id
WHERE friends_info.friend_id=592737;