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

aspxgridview - Devexpress grid - header filter does not work if column is initially hidden -

-
i'm using devexpress grid , i'm trying 'country' column display header filter properly: <dx:gridviewdatacolumn caption="country" fieldname="countryname" showincustomizationform="true" visible="false"> <settings allowheaderfilter="true"/> </dx:gridviewdatacolumn> if 'country' column set visible='true', header filter displayed should(it shows value option list). however, want 'country' column hidden, available in customization window(like in code above). in case, when column dragged outside customization window , grid , header filter clicked, javascript error encountered: element null element.addeventlistener(eventname, func, true); is known bug? there workarounds? set aspxgridview.settings.showheaderfilterbutton property true resolve problem. the following markup works fine me (i using dxperience 10.1.7): <asp:accessdatasource id="accessda
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