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

SAP Web Service from .NET via WCF -

-
i'm trying consume sap web service .net via wcf. i've generated proxy , have configured app.config file. here test code: webservicesap.ztest_rfcclient mywcfservice = new webservicesap.ztest_rfcclient("myendpoint"); mywcfservice.clientcredentials.username.username = "<username>"; mywcfservice.clientcredentials.username.password = "<password>"; webservicesap.ztestrfc parameter = new webservicesap.ztestrfc(); parameter.testinput = "this simple test"; webservicesap.ztestrfcresponse response = mywcfservice.ztestrfc(parameter); console.writeline(reponse.testoutput); console.readline(); the ztestrfc sap method simple function accepts input string, , outputs: "result: <the input string>" when call ztestrfc method, got null value in variable response. soap messages seem fine. soap request <messagelogtracerecord> <httprequest xmlns="http://schemas.microsoft.com/2004/06/servic...
Read more

Optimized Line drawing in QT -

-
i new qt. working on graphics. i using qwidget drawing graphics(for drawing graphics in qwidget paint event). need draw background , foreground graphics. background fixed graphics. foregrounds drawing lines. each 100 millisecond need draw 20points. drawing time 8 sec. total need draw 1600 points (total points represents contentious line). i using qtimer invoke drawing in each 100ms. first few drawing drawn fast. in middle of drawing it's become slow. the problem need draw foreground , background in each 100ms. please me fix problem. if 1 have sample code please provide. in advance. is there way draw partial area ie. particular modified region of graphics? qpainter-drawing can slow without hardware support. using qgraphicsview won't if lines visible, since internally uses qpainter anyway. if have draw 20 new points (or lines) per update , per update background gets cleared have render again, there few things try: 1) disable background autofill. see...
Read more

datetime - str to time in python -

-
time1 = "2010-04-20 10:07:30" time2 = "2010-04-21 10:07:30" how convert above string time stamp? i need subtract above timestamps time2-time1 . for python 2.5+ from datetime import datetime format = '%y-%m-%d %h:%m:%s' print datetime.strptime(time2, format) - datetime.strptime(time1, format) # 1 day, 0:00:00 edit: python 2.4 import time format = '%y-%m-%d %h:%m:%s' print time.mktime(time.strptime(time2, format)) - time.mktime(time.strptime(time1, format)) # 86400.0
Read more