postgresql - Postgres Full Text Search performance question -

-

postgres full text search performance seems depend on dictionary use, here's example

my table contains 60000 entries

default german dictionary (only stopwords, think)

select title sitesearch s, to_tsquery('german','holz') query query @@ searchtext limit 10

query plan

limit  (cost=247.14..282.99 rows=10 width=21) (actual time=1.286..1.534 rows=10 loops=1)   ->  nested loop  (cost=247.14..1358.57 rows=310 width=21) (actual time=1.278..1.512 rows=10 loops=1)         ->  function scan on query  (cost=0.00..0.01 rows=1 width=32) (actual time=0.019..0.019 rows=1 loops=1)         ->  bitmap heap scan on sitesearch s  (cost=247.14..1354.68 rows=310 width=570) (actual time=1.237..1.452 rows=10 loops=1)               recheck cond: (query.query @@ s.searchtext)               ->  bitmap index scan on sitesearch_searchtext_idx  (cost=0.00..247.06 rows=310 width=0) (actual time=0.871..0.871 rows=1144 loops=1)                     index cond: (query.query @@ s.searchtext) total runtime: 1.815 ms 8 row(s)  total runtime: 13.414 ms

that's pretty fast

my dictionary german ispell 

create text search dictionary pg_german (     template = ispell,     dictfile = german,     afffile = german,     stopwords = german );

support compound words enabled
dictfile: 319018 words
affix file: 1290 lines
stopwords: 264 words 

select title sitesearch s, to_tsquery('public.pg_german','holz') query query @@ searchtext limit 10

query plan

limit  (cost=247.14..282.99 rows=10 width=21) (actual time=1.263..1.578 rows=10 loops=1)   ->  nested loop  (cost=247.14..1358.57 rows=310 width=21) (actual time=1.255..1.556 rows=10 loops=1)         ->  function scan on query  (cost=0.00..0.01 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=1)         ->  bitmap heap scan on sitesearch s  (cost=247.14..1354.68 rows=310 width=570) (actual time=1.229..1.495 rows=10 loops=1)               recheck cond: (query.query @@ s.searchtext)               ->  bitmap index scan on sitesearch_searchtext_idx  (cost=0.00..247.06 rows=310 width=0) (actual time=0.896..0.896 rows=1144 loops=1)                     index cond: (query.query @@ s.searchtext) total runtime: 1.818 ms 8 row(s)  total runtime: 1,520.428 ms

query plan fast ... 1,5s total runtime? take long load / initialize dictionary? how speed process?

thanks!

yes, ispell dictionaries very slow load first time. if you're using those, need use connection pooling - take time first time loaded each connection.

there's work being done on 9.1, current versions you're stuck requirement.


Comments

Post a Comment

Popular posts from this blog

android - Spacing between the stars of a rating bar? -

-
ho can set spacing beween stars? thats ratingbar: ratingbar = (ratingbar) inflater.inflate(r.layout.ratingbar, null); ratingbar.setlayoutparams(new layoutparams(layoutparams.wrap_content, layoutparams.wrap_content)); the layout: <?xml version="1.0" encoding="utf-8"?> <ratingbar xmlns:android="http://schemas.android.com/apk/res/android" style="@style/mystyle" android:layout_alignparentright="true" android:layout_height="19dp" android:layout_width="wrap_content" android:numstars="5"> </ratingbar> the style: <style name="mystyle" parent="@android:style/widget.ratingbar"> <item name="android:progressdrawable">@drawable/android_r2_ratingstar_yellow</item> <item name="android:indeterminatedrawable">@drawable/android_r2_ratingstar_grey</item> </style> thanks, cheers you have ...
Read more

html - Instapaper-like algorithm -

-
does of algorithm extracts contents webpage? instapaper ? there 2 steps instapaper does: find main content block on page (excluding headers, footers, menus etc) from content block extract , format text to find content block (typically html block element, div containing key page text content) instapaper uses algorithm 1 used readability . can @ source of readability.js see what's going on, @ core tries find area on page highest text/link ratio, although has other simple scoring metrics (e.g. off top of head, things ratio of text commas, para elements etc) go heuristics. once have identified root node element, relevant content, you'll need format it, if want can pull node element containing text out of source document , insert yours, in reality you'll want remove existing styles , apply own, standard , feel. if want output nice text-only can use jericho's renderer . update1 : should mention else instapaper - follow 'pagination' links (...
Read more

c# - How to execute a particular part of code asynchronously in a class -

-
i creating speech recognition engine that's going respond user commands.i have created button enable , disable speech recognition per user convenience.i have used dispose() of speech engine disable speech recoginition.here code private void button1_click(object sender, routedeventargs e) { engineon = !engineon; if (engineon) { speechengine = speech.createspeechengine(); //speech class creates , returns new speech engine. speechengine.audiolevelupdated += new eventhandler<audiolevelupdatedeventargs>(speechengine_audiolevelupdated); // use system's default microphone speechengine.setinputtodefaultaudiodevice(); speechengine.loadgrammar(new dictationgrammar()); // start listening speechengine.recognizeasync(recognizemode.multiple); } else { speechclass.myengine.dispose(); } } but disposal of speech obj...
Read more