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','3278 7','32857','328 58','32871','32 872','32873','3 2874','32877',' 32878','32891', '32892','32894' ,'32922','32923 ','32928','3292 9','33003','330 04','33030','33 090','33091','3 5
146','37026','3 7028','39730',' 44317','44339', '44348','44359' ,'44413','44420 ','44438','4531 4','45322','458 61','47914','48 756','49587','5 0564','50567',' 51222','52330', '53029','53550' ,'53581','53898 '
,'53901','53932 ','54235','5494 4','54951','549 55','54965','54 973','55041','5 5295','55370',' 55374','55609', '55885','55889' ,'55898','55903 ','55905','5590 6','55907','564 42','57144','57 228','57230','5 7
233','58497','5 8499','58512',' 58519','58540', '58581','58585' ,'59281','59865 ','59874','6047 6','60478','604 82','60486','60 590','60827','6 1465','61531',' 61778','62272', '62585','62602' ,'62609','62731 '
,'63933','66733 ','66740','6674 3','66747','667 58','66760','66 763','66765','6 6768','66774',' 66777','68100', '68191','68195' ,'68212','68213 ','68257','6826 6','68288','682 95','68300','68 315','68332','6 8
335','68349','6 8354','69422',' 69435','69446', '69447','69987' ,'69991','69995 ','70003','7000 7','70010','700 84','70089','70 095','70468','7 0692','70699',' 70739','71022', '74531','74810' ,'77576','77736 '
,'78346','78608 ','79208','7929 1','79312','793 49','80034','80 038','82203','8 2852','84155',' 84456','85945', '86709','87055' ,'87061','87065 ','87078','8740 6','87413','875 18','89229','89 740','91262','9 4
205','94209','9 4215','94217',' 94940','96242', '96295','96303' ,'97442','97827 ','97833','9785 4','98262','988 45','98846','98 847','98848','9 8849','98850',' 98851','98852', '98854','98855' ,'98856','98857 '
,'98858','98859 ','98860','9923 9','100360','10 0407','100459', '100731','10084 0','102020','10 2026','103782', '106017','10852 3','109645','10 9654','109667', '109670','11121 3','111232','11 1233','111349', '
111351','111356 ','111620','116 673','116677',' 116734','117709 ','117733','118 075','118103',' 122444','126754 ','127945','127 949','128132',' 131062','131066 ','131067','131 068','131079',' 131084','131085 '
,'131094','1312 21','131222','1 31223','131225' ,'131226','1312 27','134426','1 35465','135466' ,'135468','1354 70','137034','1 37035','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