select advert_id from acr_cache where category_id = ? and region_id = ?
order by XXX {asc|desc} limit 20;
where XXX is one of 5 possible fields,
timestamp,
timestamp,
text,
text,
numeric
Create 5 indexes on ( category_id, region_id, a field )
where a field is one of your 5
Without reading too hard, I suggest having a quick look at contrib/ltree
module in the PostgreSQL distribution. It may or may not help you.
Chris
hubert lubaczewski wrote:
hi
first let me draw the outline.
we have a database which stores adverts.
each advert is in one category, and one or
On Sat, Jun 04, 2005 at 07:17:17PM +0800, Christopher Kings-Lynne wrote:
Without reading too hard, I suggest having a quick look at contrib/ltree
module in the PostgreSQL distribution. It may or may not help you.
acr_cache doesn't care about trees. and - since i have acr_cache - i
dont have
On Sat, Jun 04, 2005 at 01:18:04PM +0200, PFC wrote:
Then write your query as :
select advert_id from acr_cache where category_id = ? and region_id = ?
order by category_id, region_id, XXX limit 20;
this is great idea - i'll check it out definitelly.
depesz
signature.asc
Description:
select advert_id from acr_cache where category_id = ? and region_id = ?
order by category_id, region_id, XXX limit 20;
don't forget to mention all the index columns in the order by, or the
planner won't use it.
---(end of broadcast)---
TIP
On Sat, Jun 04, 2005 at 02:07:52PM +0200, PFC wrote:
don't forget to mention all the index columns in the order by, or
the planner won't use it.
of course.
i understand the concept. actually i find kind of ashamed i did not try
it before.
anyway - thanks for great tip.
depesz
