Re: ANALYZE sampling is too good

On 10/12/13 17:18, Claudio Freire wrote:
> On Tue, Dec 10, 2013 at 12:13 AM, Mark Kirkwood
> <mark.kirkwood@catalyst.net.nz> wrote:
>> Just one more...
>>
>> The Intel 520 with ext4:
>>
>>
>> Without patch:  ANALYZE pgbench_accounts 5s
>> With patch: ANALYZE pgbench_accounts  1s
>>
>> And double checking -
>> With patch, but effective_io_concurrency = 1: ANALYZE pgbench_accounts  5s
>>
>> These results look more like Heikki's. Which suggests more benefit on SSD
>> than spinning disks. Some more data points (apart from mine) would be good
>> to see tho.
>
> Assuming ANALYZE is sampling less than 5% of the table, I'd say
> fadvising will always be a win.
>
> I'd also suggest higher e_i_c for rotating media. Rotating media has
> longer latencias, and e_i_c has to be computed in terms of latency,
> rather than "spindles" when doing prefetch.
>
> For backward index scans, I found the optimum number for a single
> spindle to be about 20.

Yeah - was just fooling about with this on the velociraptor, looks like 
somewhere in the 20's works well.
                   | pgbench_accounts
eff_io_concurrency | analyze time (s)
-------------------+-----------------
8                  |  43
16                 |  40
24                 |  36
32                 |  35
64                 |  35

Cheers

Mark