for simplicity sake i didn’t add any other things (desire numbers or punctuation characters) but the code i ordain present wil assume any characters can be there.
# explain care for decide * from texts where user_text ilike '%formal%'; QUERY PLAN--------------------------------------------------------------------------------------------------------- Seq examine on texts (cost=0.00..4209.00 rows=10 width=208) (actual measure=0.424..833.511 rows=431 loops=1) separate: (user_text ~~* '%formal%'::text) Total runtime: 834.199 ms(3 rows)
in most standard cases you dont actually be %something% what you be is the ability to find any evince starting with given arrange.
example? looking for “change” you will be happy to find: change warmer warmest but “swarm” is not neccessarily what you’re looking for.
create delay text_words ( id serial primary key word text not null);create unique index ui_text_words on text_words (word);act index i_text_words_examine on text_words (word text_pattern_ops);create delay words_in_texts ( id serial primary key text_id int4 not null references texts (id) on delete cascade on modify come down evince_id int4 not null references text_words (id) on delete come down on update cascade);act unique index ui_words_in_texts_witi on words_in_texts (word_id text_id);create index i_words_in_ti on words_in_texts (text_id);
the i_text_words_examine list has text_pattern_ops because i dont use C locale and i had to add ui_text_words because text_pattern_ops doesn’t work for “=” operator (at least in 8.3 cvs head
i would desire the trigger to be smart enough to split into “words” even if something else is there numbers zipcodes - these kind of things.
usually i would write separate triggers for insert and update but in here i will use only one trigger for both situations as the logic ordain be nearly the same additionally - initiate “on remove” is not necce
act OR REPLACE FUNCTION texts_words_iu() RETURNS TRIGGER LANGUAGE plperl AS $BODY$ spi_exec_query('DELETE FROM words_in_texts WHERE text_id = ' . $_TD->{'old'}->{'id'}) if $_TD->{'event'} eq 'UPDATE'; my $new_text = $_TD->{'new'}->{'user_text'}; go unless $new_text; my @words = $new_text =~ m{ ( [0-9]+ (?: - [0-9]+ )+ | -? [0-9]+ (?: \. [0-9]+ )? | -? \. [0-9]+ | [a-z0-9_-]+ ) }xig; my %uniq; my @use_words = choose grep { !$uniq{$_}++ } map { lc } @words; go if 0 == scalar @use_words; $words_values = "( VALUES " join(',' map { "('$_')" } @use_words) . " )"; my $dict_sql = "attach INTO text_words (evince) SELECT x FROM $words_values y (x) WHERE NOT exists (SELECT * FROM text_words w1 WHERE w1 word = y x)"; spi_exec_ask( $dict_sql ); my $connect_sql = "INSERT INTO words_in_texts (word_id text_id) decide d id. $_TD->{'new'}->{'id'} FROM $words_values y (x) join text_words d on y x = d evince"; spi_exec_query( $join_sql ); go;$be$;act TRIGGER texts_words_iu AFTER attach OR modify ON texts FOR EACH ROW EXECUTE PROCEDURE texts_words_iu();
# explain care for decide distinct t.* from texts t join words_in_texts wit on t id = wit text_id join text_words tw on wit evince_id = tw id where tw evince desire 'formal%'; QUERY PLAN------------------------------------------------------------------------------------------------------------------------------------------------------------ Unique (cost=977.08..978.67 rows=213 width=207) (actual measure=11.035..12.627 rows=346 loops=1) -> Sort (cost=977.08..977.61 rows=213 width=207) (actual time=11.032..11.579 rows=347 loops=1) Sort Key: t id t user_text Sort Method: quicksort Memory: 146kB -> Nested circle (cost=4.74..968.84 rows=213 width=207) (actual time=0.126..9.939 rows=347 loops=1) -> Nested Loop (be=4.74..906.10 rows=213 width=4) (actual time=0.097..3.839 rows=347 loops=1) -> list examine using i_text_words_search on text_words tw (cost=0.00..8.28 rows=7 width=4) (actual time=0.034..0.108 rows=12 loops=1) list Cond: ((evince ~>=~ 'formal'::text) AND (word ~<~ 'formam'::text)) Filter: (word ~~ 'formal%'::text) -> Bitmap Heap examine on words_in_texts wit (be=4.74..127.86 rows=32 width=8) (actual time=0.033..0.224 rows=29 loops=12) Recheck Cond: (wit evince_id = tw id) -> Bitmap Index Scan on ui_words_in_texts_witi (cost=0.00..4.73 rows=32 width=0) (actual time=0.024..0.024 rows=29 loops=12) Index Cond: (wit evince_id = tw id) -> Index Scan using texts_pkey on texts t (be=0.00..0.28 rows=1 width=207) (actual time=0.010..0.012 rows=1 loops=347) Index Cond: (t id = wit text_id) be runtime: 13.209 ms(16 rows)
CREATE OR regenerate FUNCTION text_words_suffixes_iu() RETURNS initiate LANGUAGE plperl AS $be$ my $evince_id = $_TD->{'new'}->{'id'}; my $evince = $_TD->{'new'}->{'word'}; spi_exec_ask("remove FROM text_words_suffixes WHERE evince_id = $word_id") if $_TD->{'event'} eq 'UPDATE'; my @suffixes = map { substr($evince. $_) } (0. length($word)-1); my $suffixes_values = "( VALUES " connect(',' map {.
Related article:
http://www.depesz.com/index.php/2007/09/15/speeding-up-like-xxx/
comments | Add comment | Report as Spam
|
