Re: SourceForge & Postgres

Tim,

I've found your message in postgres hackers list and wondering if
sourceforge db part could be improved using our recent (7.1) GiST improvements.

In short, using RD-Tree + GiST we've added index support for arrays of
integers. For example, in our rather busy web site we have pool
of online news. Most complex query to construct main page is
select messages from given list of categories, because it requires
join from message_section_map (message could belong to several
categories).
messages message_section_map
-------- -------------------
msg_id msg_id
title sect_id
.....

WHERE clause (simplificated) looks like
......
message_section_map.sect_id in (1,13,103,10488,105,17,9,4,2,260000373,12,7,8,14,5,6,11,15,
10339,10338,10336,10335,260000404,260000405,260000403,206) and
message_section_map.msg_id = messages.msg_id order by publication_date
desc .....

This is really difficult query and takes a long time to execute.

now, we exclude message_section_map, just add array <sections> to
table messages which contains all sect_id given message belong to.
Using our index support for arrays of int4 our complex query
executes very fast !

I think sourceforge uses some kind of such queries.

Some info about GiST extension and our contribution could be find
at http://www.sai.msu.su/~megera/postgres/gist/

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83