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> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bwragg@tpg.com.au
Subject: Re: Improve Full text rank in a query
In-Reply-To: <000b01c88007$8c07b1a0$0502a8c0@windoze>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox