From: "b wragg" <bwragg@tpg.com.au>
To: <pgsql-performance@postgresql.org>
Subject: Improve Full text rank in a query 
Date: Fri, 7 Mar 2008 14:58:47 +1100
Message-ID: <000b01c88007$8c07b1a0$0502a8c0@windoze>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="windows-1250"
Content-Transfer-Encoding: 7bit
thread-index: AciAB4t73pWH/affQ8CgzRSLn2/wUw==

Hi all,

I'm running the following query to match a supplied text string to an actual
place name which is recorded in a table with extra info like coordinates,
etc.

SELECT ts_rank_cd(textsearchable_index_col , query, 32 /* rank/(rank+1) */)
AS rank,*
FROM gazetteer, to_tsquery('Gunbower|Island|Vic') query
WHERE query @@ textsearchable_index_col	order by rank desc, concise_ga desc,
auda_alloc desc LIMIT 10

When I run this I get the following top two results:

Pos	Rank		Name
State
1	0.23769	Gunbower Island Primary School	Vic	
2	0.23769	Gunbower Island				Vic

The textsearchable_index_col for each of these looks like this:

'vic':6 '9999':5 'gunbow':1 'island':2 'school':4 'primari':3 'victoria':7
'vic':4 '9999':3 'gunbow':1 'island':2 'victoria':5

I'm new to this, but I can't figure out why the "Gunbower Island Primary
School" is getting top place. How do I get the query to improve the ranking
so that an exact match (like "Gunbower|Island|Vic") gets a higher position?

Thanks,

bw


No virus found in this outgoing message.
Checked by AVG Free Edition. 
Version: 7.5.516 / Virus Database: 269.21.4/1309 - Release Date: 3/03/2008
6:50 PM