[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] Increasing pattern index query speed
From:       Scott Carey <scott () richrelevance ! com>
Date:       2008-11-26 22:27:46
Message-ID: BDFBB77C9E07BE4A984DAAE981D19F961ACA1F1AD4 () EXVMBX018-1 ! exch018 ! msoutlookonline ! net
[Download RAW message or body]

> I used 1000 since doc wrote that max value is 1000
> Rid table contains 3.5millions rows, will increase 1 millions of rows per
> year and is updated frequently, mostly by adding.

> Is it OK to leave

> SET STATISTICS 1000;

> setting for this table this column or should  I try to decrease it ?

> Andrus.

If you expect millions of rows, and this is one of your most important use cases, \
leaving that column's statistics target at 1000 is probably fine.  You will incur a \
small cost on most queries that use this column (query planning is more expensive as \
it may have to scan all 1000 items for a match), but the risk of a bad query plan and \
a very slow query is a lot less.

It is probably worth the small constant cost to prevent bad queries in your case, and \
since the table will be growing.  Larger tables need larger statistics common values \
buckets in general.

Leave this at 1000, focus on your other issues first.  After all the other major \
issues are done you can come back and see if a smaller value is worth trying or not.

You may also end up setting higher statistics targets on some other columns to fix \
other issues.  You may want to set the value in the configuration file higher than \
the default 10 -- I'd recommend starting with 40 and re-analyzing the tables.  Going \
from 10 to 40 has a minor cost but can help the planner create significantly better \
queries if you have skewed data distributions.

-Scott

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[prev in list] [next in list] [prev in thread] [next in thread]