searching archives should be a weeeee bit faster ...

[Marc G. Fournier]

I spent this aft enabling debugging on the postmaster side, and determined
that there was a critical index missing on one of the tables ... there was
no index on the url.rec_id field, so a query that looks like:

SELECT rec_id, site_id, pop_rank FROM url WHERE rec_id IN
('31356','31364','32786','32787','32857','32858',' 32871','32872','32873','32874','32877','32878','32 891','32892','32894','32922','32923','32928','3292 9','33003','33004','33030','33090','33091','35
146','37026','37028','39730','44317','44339','4434 8','44359','44413','44420','44438','45314','45322' ,'45861','47914','48756','49587','50564','50567',' 51222','52330','53029','53550','53581','53898'
,'53901','53932','54235','54944','54951','54955',' 54965','54973','55041','55295','55370','55374','55 609','55885','55889','55898','55903','55905','5590 6','55907','56442','57144','57228','57230','57
233','58497','58499','58512','58519','58540','5858 1','58585','59281','59865','59874','60476','60478' ,'60482','60486','60590','60827','61465','61531',' 61778','62272','62585','62602','62609','62731'
,'63933','66733','66740','66743','66747','66758',' 66760','66763','66765','66768','66774','66777','68 100','68191','68195','68212','68213','68257','6826 6','68288','68295','68300','68315','68332','68
335','68349','68354','69422','69435','69446','6944 7','69987','69991','69995','70003','70007','70010' ,'70084','70089','70095','70468','70692','70699',' 70739','71022','74531','74810','77576','77736'
,'78346','78608','79208','79291','79312','79349',' 80034','80038','82203','82852','84155','84456','85 945','86709','87055','87061','87065','87078','8740 6','87413','87518','89229','89740','91262','94
205','94209','94215','94217','94940','96242','9629 5','96303','97442','97827','97833','97854','98262' ,'98845','98846','98847','98848','98849','98850',' 98851','98852','98854','98855','98856','98857'
,'98858','98859','98860','99239','100360','100407' ,'100459','100731','100840','102020','102026','103 782','106017','108523','109645','109654','109667', '109670','111213','111232','111233','111349','
111351','111356','111620','116673','116677','11673 4','117709','117733','118075','118103','122444','1 26754','127945','127949','128132','131062','131066 ','131067','131068','131079','131084','131085'
,'131094','131221','131222','131223','131225','131 226','131227','134426','135465','135466','135468', '135470','137034','137035','137536','142057') ORDER BY rec_id;

was doing a sequence scan over 230k+ records in the URL file each time ...
created the index, and now I can actually get results ~32sec for the
single string (mvcc) that I've been using for testing, instead of it never
seeming to come back ...

hopefully that was the major part of it ...
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Sean Chittenden]

Is "index" a stop word in the archives? It doesn't seem to return any
results when typing in "index" with anything (ex: index correlation).
FWIW, just a data point. -sc

--
Sean Chittenden

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Marc G. Fournier]

yes it is/was ...I've just removed it from the list of stop words, but am
not going to force a re-index of 250k URLs ;( So, any new index'ng will
pull in index as a valid search word, and when the current URLs
expire/re-index,they will get pulled in then also ...

On Thu, 4 Sep 2003, Sean Chittenden wrote:

Is "index" a stop word in the archives? It doesn't seem to return any
results when typing in "index" with anything (ex: index correlation).
FWIW, just a data point. -sc

--
Sean Chittenden

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Sean Chittenden]

> yes it is/was ...I've just removed it from the list of stop words,

but am not going to force a re-index of 250k URLs ;( So, any new
index'ng will pull in index as a valid search word, and when the
current URLs expire/re-index,they will get pulled in then also ...

Hrm... if I search for "correlation," I get some results. If I search
for "index," I don't get anything (expected), but if I search for
"index correlation," I get nothing. Why isn't it returning the same
results as when I searched for "correlation?"

Just a scratch that I finally had to scratch and ask about...

-sc

--
Sean Chittenden

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Marc G. Fournier]

On Thu, 4 Sep 2003, Sean Chittenden wrote:

yes it is/was ...I've just removed it from the list of stop words,
but am not going to force a re-index of 250k URLs ;( So, any new
index'ng will pull in index as a valid search word, and when the
current URLs expire/re-index,they will get pulled in then also ...

Hrm... if I search for "correlation," I get some results. If I search
for "index," I don't get anything (expected), but if I search for
"index correlation," I get nothing. Why isn't it returning the same
results as when I searched for "correlation?"

Just a scratch that I finally had to scratch and ask about...

my understanding of how it searches is it defaults to an 'and' ... so it
would have to find all docs that have both index (0) *and* correlation (n)
.... which would equal zero results ...
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org