Re: ORDER BY ... LIMIT and JOIN

From: Michael Andreen
Subject: Re: ORDER BY ... LIMIT and JOIN
Date: ,
Msg-id: 200908100103.58997.harv@ruin.nu
(view: Whole thread, Raw)
In response to: Re: ORDER BY ... LIMIT and JOIN  (Fizu)
List: pgsql-performance

Tree view

ORDER BY ... LIMIT and JOIN  (Fizu, )
 Re: ORDER BY ... LIMIT and JOIN  (Michael Andreen, )
  Re: ORDER BY ... LIMIT and JOIN  (Fizu, )
   Re: ORDER BY ... LIMIT and JOIN  (Michael Andreen, )
   Re: ORDER BY ... LIMIT and JOIN  (Robert Haas, )

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


pgsql-performance by date:

From: Robert Haas
Date:
Subject: Re: ORDER BY ... LIMIT and JOIN
From: Ip Wing Kin John
Date:
Subject: Re: Bottleneck?