Re: ORDER BY ... LIMIT and JOIN

On Sat, Aug 8, 2009 at 2:09 PM, Michael Andreen<harv(at)ruin(dot)nu> wrote:
> The planner is expecting one user with country_id = 1, but instead there are
> 57309. Have you analyzed recently? Maybe increasing the statistics target will
> help.
>
> /Michael

Just after analyze user and ranking it still taking so long to order
by an indexed field.

# explain analyze SELECT * FROM "ranking" INNER JOIN "user" ON
("ranking"."username" = "user"."username") WHERE "user"."country_id" =
5 ORDER BY "ranking"."ranking" ASC LIMIT 100;

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=15340.13..15340.38 rows=100 width=178) (actual
time=4955.795..4955.865 rows=100 loops=1)
-> Sort (cost=15340.13..15343.69 rows=1425 width=178) (actual
time=4955.794..4955.820 rows=100 loops=1)
Sort Key: ranking.ranking
Sort Method: top-N heapsort Memory: 56kB
-> Nested Loop (cost=0.00..15285.67 rows=1425 width=178)
(actual time=20.951..4952.337 rows=1972 loops=1)
-> Index Scan using country_ranking_user_idx on "user"
(cost=0.00..4807.25 rows=1710 width=143) (actual
time=20.923..4898.931 rows=1972 loops=1)
Index Cond: (country_id = 5)
-> Index Scan using ranking_tmp_pkey on ranking
(cost=0.00..6.12 rows=1 width=35) (actual time=0.024..0.025 rows=1
loops=1972)
Index Cond: ((ranking.username)::text =
("user".username)::text)
Total runtime: 4955.974 ms
(10 rows)

# explain analyze SELECT * FROM "ranking" INNER JOIN "user" ON
("ranking"."username" = "user"."username") ORDER BY
"ranking"."ranking" ASC LIMIT 100;

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..136.78 rows=100 width=178) (actual
time=0.058..1.870 rows=100 loops=1)
-> Nested Loop (cost=0.00..3116910.51 rows=2278849 width=178)
(actual time=0.056..1.818 rows=100 loops=1)
-> Index Scan using idxrank_6224 on ranking
(cost=0.00..71682.17 rows=2278849 width=35) (actual time=0.022..0.065
rows=100 loops=1)
-> Index Scan using user_pkey on "user" (cost=0.00..1.32
rows=1 width=143) (actual time=0.015..0.016 rows=1 loops=100)
Index Cond: (("user".username)::text = (ranking.username)::text)
Total runtime: 1.946 ms
(6 rows)

Thank you!
M