Re: GiST opclass and varlena

Le Tuesday 25 March 2008 17:57:11 Dragan Zubac, vous avez écrit :
> and we're using a procedure to match prefices (longest prefix
> match),with simething like:
>
> while tmp_length <= char_length(d_number) loop
>
> -- take the number and try to find it in prefix table
> -- if not found,decrease it by removing last number
> -- and try again
[...]
> Some poorly measurement showed some 60-80 matching/sec with this
> algorithm of matching prefices and a couple of concurrent database
> connections.

With the GiST index support we're hoping to get millisecond response time
order (that mean something like 1000 matching per sec, best case), and with a
query that simple :
SELECT operator
FROM prefix
WHERE prefix @> '16511xxxxxx'
ORDER BY len(prefix) DESC
LIMIT 1;

For this you have to create a dedicated index, first version of the code has
this version:
http://prefix.projects.postgresql.org/README.html
CREATE INDEX idx_prefix ON prefix USING GIST(prefix gist_prefix_ops);

New version I'm trying to make work looks like this:
CREATE TABLE prefix(id serial, prefix prefix_range, operator integer);
CREATE INDEX idx_prefix ON prefix USING GIST(prefix gist_prefix_range_ops);

Former usage shows performance of 2 to 5 ms answer time on lower setups
(meaning sth like 200 reqs/s already), latter one is aiming at 1000 req/s as
said before, but does not work at all at the moment... The goal is to be able
to use the search from an AFTER INSERT TRIGGER to materialize some calling
stats and prices, etc. So it has to be that fast.

Please consider trying the code if you're interrested, it's been tested with
PostgreSQL versions 8.2 and 8.3, and former version is working fine with text
type prefixes, and should offer you some speedups already.

Hope this helps clarifying the goals and context,
--
dim