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

On Thu, 1 Jan 2004, Tom Lane wrote:

> "Marc G. Fournier" <scrappy(at)postgresql(dot)org> writes:
> > 'k, and for todays question ... how does one 'knock up the stats target'?
>
> ALTER TABLE [ ONLY ] name [ * ]
> ALTER [ COLUMN ] column SET STATISTICS integer
>
> The default is 10; try 100, or even 1000 (don't think it will let you
> go higher than 1000).

k, so:

186_archives=# alter table ndict8 alter column word_id set statistics 1000;
ALTER TABLE

followed by an 'vacuum verbose analyze ndict8', which showed an analyze
of:

INFO: analyzing "public.ndict8"
INFO: "ndict8": 34718 pages, 300000 rows sampled, 6354814 estimated total rows

vs when set at 10:

INFO: analyzing "public.ndict8"
INFO: "ndict8": 34718 pages, 3000 rows sampled, 6229711 estimated total rows

The query @ 1000:

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=13918.23..76761.02 rows=81 width=8) (actual time=5199.443..5835.444 rows=13415 loops=1)
Hash Cond: ("outer".url_id = "inner".rec_id)
-> Index Scan using n8_word on ndict8 (cost=0.00..62761.60 rows=16075 width=8) (actual time=0.230..344.485 rows=15533 loops=1)
Index Cond: (word_id = 417851441)
-> Hash (cost=13913.31..13913.31 rows=1968 width=4) (actual time=5198.289..5198.289 rows=0 loops=1)
-> Seq Scan on url (cost=0.00..13913.31 rows=1968 width=4) (actual time=3.933..3414.657 rows=304811 loops=1)
Filter: ((url || ''::text) ~~ 'http://archives.postgresql.org/%%'::text)
Total runtime: 5908.778 ms
(8 rows)

Same query @ 10:

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=13918.23..26502.18 rows=17 width=8) (actual time=3657.984..4293.529 rows=13415 loops=1)
Hash Cond: ("outer".url_id = "inner".rec_id)
-> Index Scan using n8_word on ndict8 (cost=0.00..12567.73 rows=3210 width=8) (actual time=0.239..362.375 rows=15533 loops=1)
Index Cond: (word_id = 417851441)
-> Hash (cost=13913.31..13913.31 rows=1968 width=4) (actual time=3657.480..3657.480 rows=0 loops=1)
-> Seq Scan on url (cost=0.00..13913.31 rows=1968 width=4) (actual time=2.646..2166.632 rows=304811 loops=1)
Filter: ((url || ''::text) ~~ 'http://archives.postgresql.org/%%'::text)
Total runtime: 4362.375 ms
(8 rows)

I don't see a difference between the two, other then time changes, but
that could just be that runA had a server a bit more idle then runB ...
something I'm not seeing here?

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