ST_EstimatedExtent return a incorrect extent value

[rockyc]

I have a table, and do the following testing.

1. Execute sql: vacuum analyze st_estimatedextent_test

2. Execute the sql: select ST_EstimatedExtent('public','st_estimatedextent_test','geom');

Get the result:

BOX(96.6659851074219 -32.3140754699707,129.162185668945 -10.3033857345581)

3. reinsert a existing row by executing the sql: insert into st_estimatedextent_test select gid,geom from st_estimatedextent_test where gid = 1;

4. Execute sql vacuum analyze st_estimatedextent_test;

5. Execute the sql select ST_EstimatedExtent('public','st_estimatedextent_test','geom');

Get the result:

BOX(105.510528564453 -32.3140754699707,129.118179321289 -10.3033857345581)

I think the correct result should be:

BOX(96.6659851074219 -32.3140754699707,129.162185668945 -10.3033857345581), returned in the step 2

[rockyc]

the table is backuped by pgadmin.

[pramsey]

As the name indicates, the function is an estimate, generated by taking a sample. It's not necessarily going to be deterministic as you add and change records. How much does the estimate differ from the actual ST_Extent?

[rockyc]

Replying to pramsey:

As the name indicates, the function is an estimate, generated by taking a sample. It's not necessarily going to be deterministic as you add and change records. How much does the estimate differ from the actual ST_Extent?

Absolutely, we should expect this function to return a estimated value;

But after adding a existing row, I expect the function returns the same value as before because no new geometry is added; at least, I expect the function returns a box which is bigger than the before one, because the box can not be smaller after more rows are added to the table,right? but unfortunately, the returned box is much smaller than the previous one.

[pramsey]

The estimate is based on a sample. Changing the rows changes which subset gets picked for the sample, getting a smaller box is not unreasonable.

[rockyc]

Replying to pramsey:

The estimate is based on a sample. Changing the rows changes which subset gets picked for the sample, getting a smaller box is not unreasonable.

You means, its results is only based on a sample, and can be changed dramatically dependent on the picked sample.

If I understand correctly, does it mean, sometimes, the return box is smaller than the value return from ST_Extent, and sometimes is larger, and also, the difference between ST_EstimatedExtent and ST_Extent can be a big value, right?

If it is true, do you have some guidance on when,where and how to use ST_EstimatedExtent.

Thanks

[pramsey]

The estimated bounds can be smaller (will almost always be smaller) than the real bounds (particularly if there are only a few records at the extrema) but can never be larger (since in order to be larger it would have to sample a geometry that wasn't in the full extent calculation, not possible).

[rockyc]

Replying to pramsey:

The estimated bounds can be smaller (will almost always be smaller) than the real bounds (particularly if there are only a few records at the extrema) but can never be larger (since in order to be larger it would have to sample a geometry that wasn't in the full extent calculation, not possible).

Thanks for your reply.

I totally understand and agree with what you said.