X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1E37DD1CC7F for ; Wed, 2 Jun 2004 08:57:52 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 99671-04 for ; Wed, 2 Jun 2004 11:57:54 +0000 (GMT) Received: from mx1.2at.nl (mx1.2at.nl [62.166.148.220]) by svr1.postgresql.org (Postfix) with ESMTP id 5D581D1CA04 for ; Wed, 2 Jun 2004 08:57:48 -0300 (ADT) Content-class: urn:content-classes:message Subject: Postgres query optimization with varchar fields MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Date: Wed, 2 Jun 2004 13:57:44 +0200 X-MimeOLE: Produced By Microsoft Exchange V6.5.6944.0 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Postgres query optimization with varchar fields Thread-Index: AcRImM+bMZQvLTBnTvSlSl1yqb9jFw== From: "W.H. van Atteveldt" To: X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200406/15 X-Sequence-Number: 7073 Dear reader,=20 I am investigating whether it is useful to directly query a database containing a rather large text corpus (order of magnitude 100k - 1m newspaper articles, so around 100 million words), or whether I should use third party text indexing services. I want to know things such as: how often is a certain word (or pattern) mentioned in an article and how often it is mentioned with the condition that another word is nearby (same article or n words distant). I created a table listing the words one word per row, and created an index on the word and wordnr columns. An example query would be: simple: select articleid, count(*) as count from words w where articleid in (select id from articles where batchid in (84,85,100,101,118,121)) and (word like '') group by articleid complex: select articleid, count(*) as count from words w where articleid in (select id from articles where batchid in (84,85,100,101,118,121)) and (word like '') and exists (select * from words w2 where w.articleid =3D w2.articleid and (word like '')) group by articleid According to the diagnostics, the database does use the indices for the query, but it is still rather slow (around 10 minutes for a 'simple query', x seconds for a complex one) It is important that the complex query only counts instances where the PATTERN is found and PATTERN2 only functions as a criterium and does not add to the count. My questions are: (technical details provided below) - Does anyone disagree with the general setup? - Is there a more sensible way to phrase my SQL? - Any other ideas to improve performance? Thanks, Wouter van Atteveldt Free University Amsterdam ------ Technicalities: I am using a Postgresql 7.4.1 database on a linux machine (uname -a: Linux swpc450.cs.vu.nl 2.4.22-1.2115.nptl #1 Wed Oct 29 15:31:21 EST 2003 i686 athlon i386 GNU/Linux). The table of interest is: (lemma, pos, simplepos currently not used) Table "public.words" Column | Type | Modifiers ------------+------------------------+---------------------------------- --------------------- id | integer | not null default nextval('public.words_id_seq'::text) articleid | integer | not null sentencenr | integer | not null word | character varying(255) | not null lemma | character varying(255) | pos | character varying(255) | simplepos | character(1) | wordnr | integer | not null parnr | integer | not null Indexes: "words_pkey" primary key, btree (id) "words_aid" btree (articleid) "words_word" btree (word) "words_word_ptrn" btree (word varchar_pattern_ops) "words_wordnr" btree (wordnr) Query plans: anoko=3D> explain select articleid, count(*) as count from words w where articleid in (select id from articles where batchid in (84,85,100,101,118,121)) and (word like 'integratie%') group by articleid; =20 QUERY PLAN=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20 ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------------------------ HashAggregate (cost=3D937959.21..937959.22 rows=3D2 width=3D4) -> Hash IN Join (cost=3D95863.70..937816.01 rows=3D28640 width=3D4) Hash Cond: ("outer".articleid =3D "inner".id) -> Index Scan using words_word_ptrn on words w (cost=3D0.00..836604.62 rows=3D208886 width=3D4) Index Cond: (((word)::text ~>=3D~ 'integratie'::character varying) AND ((word)::text ~<~ 'integratif'::character varying)) Filter: ((word)::text ~~ 'integratie%'::text) -> Hash (cost=3D94998.60..94998.60 rows=3D146041 width=3D4) -> Index Scan using articles_batchid, articles_batchid, articles_batchid, articles_batchid, articles_batchid, articles_batchid on articles (cost=3D0.00..94998.60 rows=3D146041 width=3D4) Index Cond: ((batchid =3D 84) OR (batchid =3D 85) OR (batchid =3D 100) OR (batchid =3D 101) OR (batchid =3D 118) OR (batchid =3D 121)) explain select articleid, count(*) as count from words w where articleid in (select id from articles where batchid in (84,85,100,101,118,121)) and (word like '') and exists (select * from words w2 where w.articleid =3D w2.articleid and (word like '')) group by articleid anoko-> ; =20 QUERY PLAN=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20 ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------------------------ GroupAggregate (cost=3D168253089.23..168254556.46 rows=3D1 width=3D4) -> Merge IN Join (cost=3D168253089.23..168254484.85 rows=3D14320 width=3D4) Merge Cond: ("outer".articleid =3D "inner".id) -> Sort (cost=3D168144438.23..168144699.33 rows=3D104443 width= =3D4) Sort Key: w.articleid -> Index Scan using words_word_ptrn on words w (cost=3D0.00..168134972.17 rows=3D104443 width=3D4) Index Cond: ((word)::text ~=3D~ ''::character varying) Filter: (((word)::text ~~ ''::text) AND (subplan)) SubPlan -> Index Scan using words_aid on words w2 (cost=3D0.00..836948.84 rows=3D1045 width=3D460) Index Cond: ($0 =3D articleid) Filter: ((word)::text ~~ ''::text) -> Sort (cost=3D108651.01..109016.11 rows=3D146041 width=3D4) Sort Key: articles.id -> Index Scan using articles_batchid, articles_batchid, articles_batchid, articles_batchid, articles_batchid, articles_batchid on articles (cost=3D0.00..94998.60 rows=3D146041 width=3D4) Index Cond: ((batchid =3D 84) OR (batchid =3D 85) OR (batchid =3D 100) OR (batchid =3D 101) OR (batchid =3D 118) OR (batchid =3D 121))