id summary reporter owner description type status priority milestone component version resolution keywords cc 1991 geocode really slow on PostgreSQL 9.2 robe robe "I'm not sure if this is just something I'm missing in settings with the new PostgreSQL or a fundamental issue with the new PostgreSQL planner. I have 3 parallel windows PostgreSQL instances -- 8.4 32-bit running PostGIS 1.5 (on older clunky 32-bit windows 2003 box) on new Windows 2008 R2 same box: 9.1 64-bit running PostGIS 2.1.0 SVN, 9.2 64-bit running PostGIS 2.1.0 SVN. On the 9.1 and 8.4, my MA data standard geocodes even with supplementary augmented parcel check completes in about 30-200ms. On 9.2 instance with same dataset. It takes 8-30 seconds. Yap. I compared the plans and it seems 9.2 is not using a bitmap index scan for this particular query I have. I have narrowed it to this construct. {{{ -- 10 seconds WITH a AS ( SELECT * FROM (SELECT f.*, ad.side, ad.zip, ad.fromhn, ad.tohn, RANK() OVER(ORDER BY diff_zip(ad.zip,'02124') + CASE WHEN lower(f.name) = lower('Evans') THEN 0 ELSE levenshtein_ignore_case(f.name, lower('Evans') ) END + levenshtein_ignore_case(f.fullname, lower('Evans' || ' ' || COALESCE('St','')) ) + CASE WHEN (greatest_hn(ad.fromhn,ad.tohn) % 2)::integer = ('22' % 2)::integer THEN 0 ELSE 1 END + CASE WHEN '22'::integer BETWEEN least_hn(ad.fromhn,ad.tohn) AND greatest_hn(ad.fromhn, ad.tohn) THEN 0 ELSE 4 END + CASE WHEN lower('St') = lower(f.suftypabrv) OR lower('St') = lower(f.pretypabrv) THEN 0 ELSE 1 END + rate_attributes(NULL, f.predirabrv, 'Evans', f.name , 'St', suftypabrv , NULL, sufdirabrv, prequalabr) ) As rank FROM featnames As f INNER JOIN addr As ad ON (f.tlid = ad.tlid) WHERE '25' = f.statefp AND '25' = ad.statefp AND lower(f.name) = lower('Evans') AND ( ad.zip = ANY('{02123,02124,02125}'::varchar[]) ) ) AS foo ORDER BY rank LIMIT 3 ) SELECT a.tlid, a.fullname, a.name, a.predirabrv, a.suftypabrv, a.sufdirabrv, a.prequalabr, a.pretypabrv, b.the_geom, tfidr, tfidl, a.side , a.fromhn, a.tohn, a.zip, p.name as place FROM a INNER JOIN edges As b ON (a.statefp = b.statefp AND a.tlid = b.tlid ) INNER JOIN faces AS f ON ('25' = f.statefp AND ( (b.tfidl = f.tfid AND a.side = 'L') OR (b.tfidr = f.tfid AND a.side = 'R' ) )) INNER JOIN place p ON ('25' = p.statefp AND f.placefp = p.placefp ) WHERE a.statefp = '25' AND b.statefp = '25' ; }}} Which can be worked around by switching to this: {{{ -- 92 ms -- WITH a AS ( SELECT * FROM (SELECT f.*, ad.side, ad.zip, ad.fromhn, ad.tohn, RANK() OVER(ORDER BY diff_zip(ad.zip,'02124') + CASE WHEN lower(f.name) = lower('Evans') THEN 0 ELSE levenshtein_ignore_case(f.name, lower('Evans') ) END + levenshtein_ignore_case(f.fullname, lower('Evans' || ' ' || COALESCE('St','')) ) + CASE WHEN (greatest_hn(ad.fromhn,ad.tohn) % 2)::integer = ('22' % 2)::integer THEN 0 ELSE 1 END + CASE WHEN '22'::integer BETWEEN least_hn(ad.fromhn,ad.tohn) AND greatest_hn(ad.fromhn, ad.tohn) THEN 0 ELSE 4 END + CASE WHEN lower('St') = lower(f.suftypabrv) OR lower('St') = lower(f.pretypabrv) THEN 0 ELSE 1 END + rate_attributes(NULL, f.predirabrv, 'Evans', f.name , 'St', suftypabrv , NULL, sufdirabrv, prequalabr) ) As rank FROM featnames As f INNER JOIN addr As ad ON (f.tlid = ad.tlid) WHERE '25' = f.statefp AND '25' = ad.statefp AND lower(f.name) = lower('Evans') AND ( ad.zip = ANY('{02123,02124,02125}'::varchar[]) ) ) AS foo ORDER BY rank LIMIT 3 ) SELECT a.tlid, a.fullname, a.name, a.predirabrv, a.suftypabrv, a.sufdirabrv, a.prequalabr, a.pretypabrv, b.the_geom, tfidr, tfidl, a.side , a.fromhn, a.tohn, a.zip, p.name as place FROM a INNER JOIN edges As b ON (a.statefp = b.statefp AND a.tlid = b.tlid ) INNER JOIN faces AS f ON ('25' = f.statefp AND ( (b.tfidl = f.tfid ) OR (b.tfidr = f.tfid ) )) INNER JOIN place p ON ('25' = p.statefp AND f.placefp = p.placefp ) WHERE a.statefp = '25' AND b.statefp = '25' AND ( (b.tfidl = f.tfid AND a.side = 'L') OR (b.tfidr = f.tfid AND a.side = 'R' ) ); }}} I'll encode this revision if I conclude it's not just something screwy with my setup and also report it up the chain if I conclude it is a PostgreSQL 9.2 issue, which I suspect it is. Note: there is nothing spatial in the above query, so I think PostGIS is off the hook. " defect closed medium PostGIS 2.0.2 tiger geocoder master fixed history