Re: strange query filter problems

Thanks, increasing the statistics did the trick:
ALTER TABLE tskjema ALTER species SET STATISTICS 999

Is there a rule of thumb to tell how to set the statistics?

Jonas:))

On 4/19/06, Martijn van Oosterhout <kleptog(at)svana(dot)org> wrote:
> 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.185 rows=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=1703 loops=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(at)svana(dot)org> http://svana.org/kleptog/
> > From each according to his ability. To each according to his ability to litigate.
>
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.1 (GNU/Linux)
>
> iD8DBQFERigoIB7bNG8LQkwRAhIdAJ9zFw0pkEqctLqHmbPteg7ofrDlEgCfS9ZO
> IscpR2d+wW7fzW3OUM3QYvo=
> =OewT
> -----END PGP SIGNATURE-----
>
>
>