Re: Increasing pattern index query speed

> 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