Re: ORDER BY ... LIMIT and JOIN

On Sunday 09 August 2009 21:26:08 Fizu wrote:
> -> 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)

The statistics looks good now, but almost all the time is still spent on
fetching users with country_id = 5. The actual ordering is only a tiny part of
the full cost. Why it takes time probably depends on your hardware in relation
to database size. I guess the database doesn't fit in ram? What settings have
you changed?

Clustering users on country_ranking_user_idx would probably help for this
specific case, but if it is a good idea depends on what other queries need to
be fast. If the table or indexes are bloated then clustering on any index or
doing reindex might do it.

/Michael