Re: Vacuum Full - Questions

On Thu, Sep 1, 2016 at 10:32 AM, Patrick B <patrickbakerbr(at)gmail(dot)com> wrote:

>
>
> 2016-09-01 11:53 GMT+12:00 Venkata B Nagothi <nag1010(at)gmail(dot)com>:
>
>>
>> On Thu, Sep 1, 2016 at 8:41 AM, Patrick B <patrickbakerbr(at)gmail(dot)com>
>> wrote:
>>
>>> Hi guys,
>>>
>>> A dev has ran a VACUUM FULL command into our test database running
>>> PostgreSQL 9.5 (I know... goddamn!!!!)...
>>>
>>> ... after the Vacuum Full, some queries start using SEQ scans instead of
>>> indexes...
>>>
>>> Does that happen because of the size of the table? The table that I'm
>>> referring to is 150MB big after the vacuum (Before was 1G)...
>>>
>>
>> Yes, it is possible that sequential scans after vacuum full are cheaper
>> than Index scans before vacuum full ? do you see improvement in query
>> response times ?
>> How does the cost differ ?
>>
>> Regards,
>> Venkata B N
>>
>> Fujitsu Australia
>>
>
>
> Well... the response time was worst than when using index.. that's very
> weird... I've re-created the indexes now a ran ANALYZE and the query is
> using again the index.. just wanted understand what happened...
>

There you go.. Running ANALYZE made the difference. Whenever you execute
VACUUM or VACUUM FULL make sure you execute ANALYZE so that latest stats
are updated. Sorry, i should have mentioned this earlier.

Regards,
Venkata B N

Fujitsu Australia