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=15533loops=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