Re: [PERFORM] [GENERAL] how to get accurate values in pg_statistic

2003-09-16 Thread Tom Lane
Mary Edie Meredith [EMAIL PROTECTED] writes: Is there any option to remove the cost numbers from the plan so we can just use diff to automate the plan comparisons? No, but a few moments with sed or perl should get the job done for you. regards, tom lane

Re: [PERFORM] [GENERAL] how to get accurate values in pg_statistic

2003-09-12 Thread scott.marlowe
On Thu, 11 Sep 2003, Christopher Browne wrote: [EMAIL PROTECTED] (scott.marlowe) writes: On Thu, 11 Sep 2003, Tom Lane wrote: Christopher Browne [EMAIL PROTECTED] writes: The right answer for most use seems likely to involve: a) Getting an appropriate number of bins (I suspect 10 is

Re: [PERFORM] [GENERAL] how to get accurate values in pg_statistic (continued)

2003-09-11 Thread Christopher Browne
[EMAIL PROTECTED] (Bruce Momjian) writes: Tom Lane wrote: Mary Edie Meredith [EMAIL PROTECTED] writes: Stephan Szabo kindly responded to our earlier queries suggesting we look at default_statistics_target and ALTER TABLE ALTER COLUMN SET STATISTICS. These determine the number of bins

Re: [PERFORM] [GENERAL] how to get accurate values in pg_statistic

2003-09-11 Thread scott.marlowe
On Thu, 11 Sep 2003, Tom Lane wrote: Christopher Browne [EMAIL PROTECTED] writes: The right answer for most use seems likely to involve: a) Getting an appropriate number of bins (I suspect 10 is a bit small, but I can't justify that mathematically), and I suspect that also, but I

Re: [PERFORM] [GENERAL] how to get accurate values in pg_statistic (continued)

2003-09-10 Thread Bruce Momjian
Tom Lane wrote: Mary Edie Meredith [EMAIL PROTECTED] writes: Stephan Szabo kindly responded to our earlier queries suggesting we look at default_statistics_target and ALTER TABLE ALTER COLUMN SET STATISTICS. These determine the number of bins in the histogram for a given column.

Re: [PERFORM] [GENERAL] how to get accurate values in pg_statistic

2003-09-07 Thread Bruce Momjian
I have learned you can use: SET random = 0; to force identical statistics every time you run ANALYZE. --- Mary Edie Meredith wrote: I certainly don't claim that it is appropriate to force customers into a full

Re: [PERFORM] [GENERAL] how to get accurate values in pg_statistic

2003-09-07 Thread Tom Lane
Greg Stark [EMAIL PROTECTED] writes: Perhaps the default of 10 is simply way too small and should be raised? I've suspected since the default existed that it might be too small ;-). No one's yet done any experiments to try to establish a better default, though. I suppose the first hurdle is

Re: [PERFORM] [GENERAL] how to get accurate values in pg_statistic (continued)

2003-09-05 Thread Tom Lane
Mary Edie Meredith [EMAIL PROTECTED] writes: For our testing purposes, however, consistent results are extremely important. We have observed that small difference in one plan for one of 22 queries can cause a difference in the DBT-3 results. If this happens, a small change in performance runs

Re: [PERFORM] [GENERAL] how to get accurate values in pg_statistic

2003-09-05 Thread Mary Edie Meredith
I certainly don't claim that it is appropriate to force customers into a full analysis, particularly if random sampling versus a full scan of the data reveals little to no performance differences in the plans. Being able to sample accurately is _very nice for large tables. For our testing

Re: [PERFORM] [GENERAL] how to get accurate values in pg_statistic

2003-09-05 Thread Bruce Momjian
Mary Edie Meredith wrote: I certainly don't claim that it is appropriate to force customers into a full analysis, particularly if random sampling versus a full scan of the data reveals little to no performance differences in the plans. Being able to sample accurately is _very nice for large

Re: [PERFORM] [GENERAL] how to get accurate values in pg_statistic

2003-09-05 Thread Bruce Momjian
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: We do have: #geqo_random_seed = -1 # -1 = use variable seed that lets you force a specific random seed for testing purposes. I wonder if that could be extended to control VACUUM radomization too. Right now, it just

Re: [PERFORM] [GENERAL] how to get accurate values in pg_statistic

2003-09-05 Thread Tom Lane
Bruce Momjian [EMAIL PROTECTED] writes: We do have: #geqo_random_seed = -1 # -1 = use variable seed that lets you force a specific random seed for testing purposes. I wonder if that could be extended to control VACUUM radomization too. Right now, it just controls GEQO and in

Re: [PERFORM] [GENERAL] how to get accurate values in pg_statistic

2003-09-04 Thread Rod Taylor
On Thu, 2003-09-04 at 13:41, Mary Edie Meredith wrote: Our port of OSDL DBT3 test suite to PostgreSQL (see Background information below) is nearing completion. We would also like to confirm our understanding of an outstanding consistency issue. We have not been able to do meaningful kernel

Re: [PERFORM] [GENERAL] how to get accurate values in pg_statistic

2003-09-04 Thread Neil Conway
On Thu, 2003-09-04 at 13:46, Rod Taylor wrote: Run a VACUUM FULL ANALYZE between runs. This will force a full scan of all data for stats It will? Are you sure about that? -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive

Re: [PERFORM] [GENERAL] how to get accurate values in pg_statistic

2003-09-04 Thread Mary Edie Meredith
The documentation lead us to believe that it would not, but we are testing just the same (at least checking that the pg_statistics are the same after each load and VACUUM FULL ANALYZE). Will report back. On Thu, 2003-09-04 at 16:50, Neil Conway wrote: On Thu, 2003-09-04 at 13:46, Rod Taylor

Re: [PERFORM] [GENERAL] how to get accurate values in pg_statistic

2003-09-04 Thread Rod Taylor
On Thu, 2003-09-04 at 19:50, Neil Conway wrote: On Thu, 2003-09-04 at 13:46, Rod Taylor wrote: Run a VACUUM FULL ANALYZE between runs. This will force a full scan of all data for stats It will? Are you sure about that? You're right. According to the docs it won't. I had a poor stats