Skip site navigation (1) Skip section navigation (2)

Re: GiST opclass and varlena

From: Dimitri Fontaine <dfontaine(at)hi-media(dot)com>
To: pgsql-hackers(at)postgresql(dot)org, zubac(at)vlayko(dot)tv
Subject: Re: GiST opclass and varlena
Date: 2008-03-25 20:25:40
Message-ID: 200803252125.43533.dfontaine@hi-media.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2008-03-25 20:26:45
Subject: advancing snapshot's xmin
Previous:From: Sam MasonDate: 2008-03-25 20:22:02
Subject: Re: writing a MIN(RECORD) aggregate

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group