Re: How to get the total number of rows returned by query

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