On 11/25/09 4:18 AM, "Matthew Wakeling" wrote:
>
> The problem is that vacuum full does a full compact of the table, but it
> has to update all the indexes as it goes. This makes it slow, and causes
> bloat to the indexes. There has been some discussion of removing the
> command or at least putt
Sergey Aleynikov wrote:
Hello,
2009/11/25 Richard Neill :
Also, if you find odd statistics of freshly analyzed table - try
increasing statistics target, using
ALTER TABLE .. ALTER COLUMN .. SET STATISTICS ...
If you're using defaults - it's again low for large tables. Start with
200, for exa
Hello,
2009/11/25 Richard Neill :
Also, if you find odd statistics of freshly analyzed table - try
increasing statistics target, using
ALTER TABLE .. ALTER COLUMN .. SET STATISTICS ...
If you're using defaults - it's again low for large tables. Start with
200, for example.
Best regards,
Sergey
Hello,
2009/11/25 Richard Neill :
>It's a simple query, but using a complex view. So I can't really re-order it.
View is inserted directly into your query by PG, and then reordered
according to from_collapse_limit. Probably, problems lies in the view?
How good is it performing? Or from_collapse_l
On Wed, 25 Nov 2009, Grzegorz Jaśkiewicz wrote:
the out of order data layout is primary reason for index bloat. And that
happens , and
gets worse over time once data is more and more distributed. ("random" deletes,
etc).
That's not index bloat. Sure, having the table not in the same order as
On Wed, Nov 25, 2009 at 7:27 AM, Richard Neill wrote:
> Sergey Aleynikov wrote:
>>
>> Hello,
>>
>>> * Is there any way I can nail the query planner to a particular query
>>> plan,
>>> rather than have it keep changing its mind?
>>
>> All these setting leads to choosing different plans. If you have
On Wed, Nov 25, 2009 at 4:58 PM, Kevin Grittner wrote:
> Grzegorz Jaœkiewicz wrote:
>
> > Other way, is to perform regular cluster && reindex
>
> If you CLUSTER there is no reason to REINDEX; indexes are rebuilt by
> the CLUSTER command.
>
> Also, if you do a good job with regular VACUUMs, there
Grzegorz Jaœkiewicz wrote:
> Other way, is to perform regular cluster && reindex
If you CLUSTER there is no reason to REINDEX; indexes are rebuilt by
the CLUSTER command.
Also, if you do a good job with regular VACUUMs, there isn't any bloat
to fix. In that case a regular CLUSTER would only
On Wed, Nov 25, 2009 at 4:26 PM, Kevin Grittner wrote:
> Richard Neill wrote:
>
> > In terms of just index bloat, does a regular vacuum help?
>
> You might want to use the REINDEX command to correct serious index
> bloat. A regular vacuum will make dead space available for re-use,
> but won't e
Richard Neill wrote:
> In terms of just index bloat, does a regular vacuum help?
You might want to use the REINDEX command to correct serious index
bloat. A regular vacuum will make dead space available for re-use,
but won't eliminate bloat directly. (If run regularly, it will
prevent bloat.
Sergey Aleynikov wrote:
Hello,
* Is there any way I can nail the query planner to a particular query plan,
rather than have it keep changing its mind?
All these setting leads to choosing different plans. If you have small
number of complex sensitive queires, you can run explain on them with
c
Matthew Wakeling wrote:
On Wed, 25 Nov 2009, Richard Neill wrote:
On Sun, 22 Nov 2009, Richard Neill wrote:
Worse still, doing a cluster of most of the tables and vacuum full
analyze
In fact, cluster is exactly the command you are looking for. It will
drop the indexes, do a complete table
On Wed, 25 Nov 2009, Richard Neill wrote:
On Sun, 22 Nov 2009, Richard Neill wrote:
Worse still, doing a cluster of most of the tables and vacuum full analyze
Why are you doing a vacuum full? That command is not meant to be used
except in the most unusual of circumstances, as it causes bloat
Matthew Wakeling wrote:
On Sun, 22 Nov 2009, Richard Neill wrote:
Worse still, doing a cluster of most of the tables and vacuum full
analyze
Why are you doing a vacuum full? That command is not meant to be used
except in the most unusual of circumstances, as it causes bloat to indexes.
We
Matthew Wakeling wrote:
On Sun, 22 Nov 2009, Richard Neill wrote:
Worse still, doing a cluster of most of the tables and vacuum full
analyze
Why are you doing a vacuum full? That command is not meant to be used
except in the most unusual of circumstances, as it causes bloat to indexes.
We
On Sun, Nov 22, 2009 at 10:31 AM, Richard Neill wrote:
> Dear All,
>
> Thanks for your help earlier with the previous question. I wonder if I might
> ask another.
>
>
> We have various queries that need to run, of which I'm going to focus on 2,
> "vox" and "du_report".
>
> Both of them are extreme
On Sun, 22 Nov 2009, Richard Neill wrote:
Worse still, doing a cluster of most of the tables and vacuum full analyze
Why are you doing a vacuum full? That command is not meant to be used
except in the most unusual of circumstances, as it causes bloat to
indexes.
If you have run a cluster co
Hello,
> * Is there any way I can nail the query planner to a particular query plan,
> rather than have it keep changing its mind?
All these setting leads to choosing different plans. If you have small
number of complex sensitive queires, you can run explain on them with
correct settings, then re
18 matches
Mail list logo
