Thread: Vacuum Full - Questions

Vacuum Full - Questions

From
Patrick B
Date:
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)...


Cheers
Patrick

Re: Vacuum Full - Questions

From
Adam Brusselback
Date:
Yes that very well could happen because the size of the table changed, as well as stats being more accurate now. Just because you have a seq scan doesn't mean the planer is making a bad choice.

Re: Vacuum Full - Questions

From
Venkata B Nagothi
Date:

On Thu, Sep 1, 2016 at 8:41 AM, Patrick B <patrickbakerbr@gmail.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

Re: Vacuum Full - Questions

From
Patrick B
Date:


2016-09-01 11:53 GMT+12:00 Venkata B Nagothi <nag1010@gmail.com>:

On Thu, Sep 1, 2016 at 8:41 AM, Patrick B <patrickbakerbr@gmail.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...


Re: Vacuum Full - Questions

From
Venkata B Nagothi
Date:

On Thu, Sep 1, 2016 at 10:32 AM, Patrick B <patrickbakerbr@gmail.com> wrote:


2016-09-01 11:53 GMT+12:00 Venkata B Nagothi <nag1010@gmail.com>:

On Thu, Sep 1, 2016 at 8:41 AM, Patrick B <patrickbakerbr@gmail.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 

Re: Vacuum Full - Questions

From
Gary Evans
Date:
Hi Patrick,

I believe Vacuum full rebuilds the indexes automatically by default, as a new copy of the table is created.

Because the indexes are new, no stats are available to the optimiser to make an informed decision about whether to utilise it or not, so it doesn't.

Once the analyze is performed as Venkata said, the stats are available for it to make that decision.  So, you didn't really need to build the indexes again.

Hope that makes it a little clearer.

Cheers 

On Thu, Sep 1, 2016 at 1:57 PM, Venkata B Nagothi <nag1010@gmail.com> wrote:

On Thu, Sep 1, 2016 at 10:32 AM, Patrick B <patrickbakerbr@gmail.com> wrote:


2016-09-01 11:53 GMT+12:00 Venkata B Nagothi <nag1010@gmail.com>:

On Thu, Sep 1, 2016 at 8:41 AM, Patrick B <patrickbakerbr@gmail.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