Received: from spam.sch.bme.hu (spam.sch.bme.hu [152.66.226.210]) by hub.org (8.9.3/8.9.3) with ESMTP id HAA51594 for ; Thu, 20 Jan 2000 07:56:35 -0500 (EST) (envelope-from dlux@spam.sch.bme.hu) Received: from dlux by spam.sch.bme.hu with local (Exim 3.12 #1) id 12BH8a-0003r1-00 for pgsql-sql@postgreSQL.org; Thu, 20 Jan 2000 13:56:32 +0100 Date: Thu, 20 Jan 2000 13:56:32 +0100 From: dLux To: pgsql-sql@postgresql.org Subject: sql performance help needed Message-ID: <20000120135632.C14384@dlux.hu> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-2 Content-Transfer-Encoding: 8bit User-Agent: Mutt/1.0i Sender: dLux 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 ----- End forwarded message ----- dLux -- god:~# create world Segmentation fault (core dumped)