Re: strange query filter problems

On Wed, Apr 19, 2006 at 01:53:46PM +0200, Jonas Henriksen wrote:
> Yes, explain analyze looks like this:

Well, incorrect statistics are definitly the culprit, look:


> "  ->  Index Scan using sskjema_pkey on sskjema s  (cost=0.00..3868.95 rows=9738 width=157) (actual
time=104.465..208.185rows=14417 loops=1)" 

50% off, not bad.

> "        ->  Index Scan using speciesix on tskjema t  (cost=0.00..6.01 rows=1 width=75) (actual time=0.140..24.594
rows=1703loops=1)" 
> "              Index Cond: ((species ~>=~ 'TAGGMAKRELL'::bpchar) AND (species ~<~ 'TAGGMAKRELM'::bpchar))"
> "              Filter: (species ~~ 'TAGGMAKRELL%'::text)"

Youch, 170200% percent off, which explains why it gets wildly bad
timings. Can you increase the statistics on the species column? Check
the docs for how.
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.