Egor Shipovalov wrote:
> Limit (cost=0.00..24.79 rows=10 width=660) (actual time=8380.84..23224.93
> rows=5 loops=1)
>[...]
> -> Index Scan using nick__friend_nick on friends f0
> (cost=0.00..2509966.71 rows=3880 width=19) (actual time=5.92..15360.73
> rows=391 loops=1)
> Filter: (friend_nick = 'asta'::character varying)
AFAICS this estimated rows=3880 vs. actual rows=391 misestimation is the
source of your problem. If you can convince the planner that
friend_nick='asta' gives only 400 rows, it probably will switch to an
index scan using friend_nick__nick with an estimated cost of ~ 1600 and
an actual time of ~ 100.
> -> Nested Loop (cost=0.00..799561.12 rows=3880 width=622)
> (actual time=10.42..7640.60 rows=360 loops=1)
> -> Index Scan using journals_0_pkey on users
> (cost=0.00..5102.15 rows=9923 width=603) (actual time=1.03..2548.42
> rows=9923 loops=1)
> -> Index Scan using nick__friend_nick on friends f1
> (cost=0.00..79.82 rows=19 width=19) (actual time=0.47..0.47rows=0
> loops=9923)
> Index Cond: ((f1.nick = "outer".nick) AND
> (f1.friend_nick = 'furrr'::character varying))
With better statistics this might change to a much cheaper
-> Nested Loop
-> Index Scan using friend_nick__nick on f1 (cost=..1600 rows=400)
-> Index Scan using u_pkey on users ( ... loops=448)
... unless the planner finds an ever faster plan.
So try
ALTER TABLE friends
ALTER COLUMN friend_nick SET STATISTICS 100;
ANALYSE friends;
and let us know how this affects your query.
> Table "public.friends"
> Column | Type | Modifiers
> -------------+-----------------------+-----------
> nick | character varying(15) | not null
> friend_nick | character varying(15) | not null
> Indexes: friends2_pkey primary key btree (nick, friend_nick),
> friend_nick__nick unique btree (friend_nick, nick),
> nick__friend_nick unique btree (nick, friend_nick)
BTW, this last index is useless because it duplicates the primary key.
Servus
Manfred
