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

Help for performance

From: dLux <dlux(at)kapu(dot)hu>
To: pgsql-sql(at)postgresql(dot)org
Subject: Help for performance
Date: 2000-01-20 11:54:58
Message-ID: 20000120125458.A14384@dlux.hu (view raw or flat)
Thread:
Lists: pgsql-sql
Hello!

  I have two tables:

  ns_article has about 2000 entries, and ns_word has about 2000000.

ns_word  is a  word-index  for  the article.  I  want  to implement  a
word-search in it.
I want  to implement a  search function,  which can search  with "like
'wordstart%'". I need  to do it with a lot of  keywords like altavista
does (with  +: intersect,  without +:  union; with  -: except),  but I
haven't found any fast result.

The following query tree would be good:
- sort by atime
  - index scan on ns_article (by article_id)
    hash join:
      - intersect/union/except
        - index scan on ns_word (by index_word)
        - index scan on ns_word (by index_word)
      ...

My current solution is:
(select  distinct atime(get_ns_article(article_id))  as a,  article_id
from  ns_word   where  index_word   like  'wordstart%')   union  (...)
intersect (...) order by a;

But this  is VERY slow  if I search with  more than one  keyword (when
intersect/union is  on use). It  is quite  fast for one  keyword (like
this:   select   distinct  atime(get_ns_article(article_id))   as   a,
article_id from ns_word where index_word like 'linux%').

the  get_ns_artcile is  a  function which  returns  a  tuple from  the
ns_article table by an article_id.

Please help me  to make it faster!  We have enough memory,  so this is
not a problem!

Thanks again,

dLux

--------------------------------------
Here are the table defs:
create table ns_article (
  article_id  int not null default nextval('ns_article_seq'),
  site   text,
  atime   timestamp,
  ...
);

create unique index ns_article_pkey on ns_article (article_id);
create unique index ns_article_url_title on ns_article (url,title);
create index ns_article_atime on ns_article (atime);

create table ns_word (
  article_id  int, -- cikk száma
  orig_word  text,
  index_word  text,
  word_position  int
);

create index ns_word_index_word on ns_word (index_word);
create  unique index  ns_word_article_wordpos on  ns_word (article_id,
word_position);

dLux
--
                             Tel: (+36)/30-9663314

pgsql-sql by date

Next:From: Peter EisentrautDate: 2000-01-20 11:58:30
Subject: Re: [SQL] crypt in Postgres?
Previous:From: Kovacs ZoltanDate: 2000-01-20 09:27:05
Subject: inserting values into arrays

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