Re: Mnogosearch (Was: Re: website doc search is ... )

On Thu, 1 Jan 2004, Tom Lane wrote:

> Mark Kirkwood <markir@paradise.net.nz> writes:
> > Might be worth trying a larger statistics target (say 100), in the hope
> > that the planner then has better information to work with.
>
> I concur with that suggestion.  Looking at Marc's problem:
>
>                                                               QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------------------
>  Hash Join  (cost=13918.23..26550.58 rows=17 width=8) (actual time=4053.403..83481.769 rows=13415 loops=1)
>    Hash Cond: ("outer".url_id = "inner".rec_id)
>    ->  Index Scan using n8_word on ndict8  (cost=0.00..12616.09 rows=3219 width=8) (actual time=113.645..79163.431 rows=15533 loops=1)
>          Index Cond: (word_id = 417851441)
>    ->  Hash  (cost=13913.31..13913.31 rows=1968 width=4) (actual time=3920.597..3920.597 rows=0 loops=1)
>          ->  Seq Scan on url  (cost=0.00..13913.31 rows=1968 width=4) (actual time=3.837..2377.853 rows=304811 loops=1)
>                Filter: ((url || ''::text) ~~ 'http://archives.postgresql.org/%%'::text)
>  Total runtime: 83578.572 ms
> (8 rows)
>
> the slowness is not really in the LIKE, it's in the indexscan on ndict8
> (79 out of 83 seconds spent there).  The planner probably would not have
> chosen this plan if it hadn't been off by a factor of 5 on the rows
> estimate.  So try knocking up the stats target for ndict8.word_id,
> re-analyze, and see what happens.

'k, and for todays question ... how does one 'knock up the stats target'?
This is stuff I've not played with yet, so a URL to read up on this would
be nice, vs just how to do it?

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664