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

On Thu, 1 Jan 2004, Tom Lane wrote:

> "Marc G. Fournier" <scrappy@postgresql.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=15533loops=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=15533loops=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@hub.org           Yahoo!: yscrappy              ICQ: 7615664