Composite Index question
Composite Index question:
I have composite index on 3 columns on a table, by mistake the composite
index was created twice on the table.
Will there any performance issues on this table because of the 2 same
composite indexes?
Thanks
Deepak
Hm. Run some queries; drop the second version of the index definition;
re-run the same queries; report to the group. The redundant index isn't
helping, that much is certain.
Show quoted text
On 10/20/2010 05:43 PM, DM wrote:
Composite Index question:
I have composite index on 3 columns on a table, by mistake the composite
index was created twice on the table.Will there any performance issues on this table because of the 2 same
composite indexes?Thanks
Deepak
Its a huge table in production, i dont want to take any risk.
I can simulate and test this but i was to checking to see If any one knows
off hand about this.
I can simulate it but
On Wed, Oct 20, 2010 at 4:57 PM, Rob Sargent <robjsargent@gmail.com> wrote:
Show quoted text
Hm. Run some queries; drop the second version of the index definition;
re-run the same queries; report to the group. The redundant index isn't
helping, that much is certain.On 10/20/2010 05:43 PM, DM wrote:
Composite Index question:
I have composite index on 3 columns on a table, by mistake the composite
index was created twice on the table.Will there any performance issues on this table because of the 2 same
composite indexes?Thanks
Deepak--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
If you can think of one benefit from having the redundant index then by
all means keep it. It certainly eludes me. Seems to me, removing an
un-necessary index on a huge table can only be a good thing.
Show quoted text
On 10/20/2010 06:02 PM, DM wrote:
Its a huge table in production, i dont want to take any risk.
I can simulate and test this but i was to checking to see If any one
knows off hand about this.I can simulate it but
On Wed, Oct 20, 2010 at 4:57 PM, Rob Sargent <robjsargent@gmail.com
<mailto:robjsargent@gmail.com>> wrote:Hm. Run some queries; drop the second version of the index definition;
re-run the same queries; report to the group. The redundant index isn't
helping, that much is certain.On 10/20/2010 05:43 PM, DM wrote:
Composite Index question:
I have composite index on 3 columns on a table, by mistake the
composite
index was created twice on the table.
Will there any performance issues on this table because of the 2 same
composite indexes?Thanks
Deepak--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org
<mailto:pgsql-general@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I know there is no benfit of having duplicate indexes.
Inorder for me to make change on production it requires lot of approvals and
stuff.
I wnat to know if there is any major performance drawback for having
duplicate composite index, so that i can push hard for the change. Let me
know.
thanks for your looking into this.
On Wed, Oct 20, 2010 at 5:10 PM, Rob Sargent <robjsargent@gmail.com> wrote:
Show quoted text
If you can think of one benefit from having the redundant index then by
all means keep it. It certainly eludes me. Seems to me, removing an
un-necessary index on a huge table can only be a good thing.On 10/20/2010 06:02 PM, DM wrote:
Its a huge table in production, i dont want to take any risk.
I can simulate and test this but i was to checking to see If any one
knows off hand about this.I can simulate it but
On Wed, Oct 20, 2010 at 4:57 PM, Rob Sargent <robjsargent@gmail.com
<mailto:robjsargent@gmail.com>> wrote:Hm. Run some queries; drop the second version of the index
definition;
re-run the same queries; report to the group. The redundant index
isn't
helping, that much is certain.
On 10/20/2010 05:43 PM, DM wrote:
Composite Index question:
I have composite index on 3 columns on a table, by mistake the
composite
index was created twice on the table.
Will there any performance issues on this table because of the 2
same
composite indexes?
Thanks
Deepak--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org
<mailto:pgsql-general@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Sorry, I cannot prove or disprove any penalty. I don't currently have
access to any postgres db of any serious magnitude.
Show quoted text
On 10/20/2010 06:23 PM, DM wrote:
I know there is no benfit of having duplicate indexes.
Inorder for me to make change on production it requires lot of approvals
and stuff.I wnat to know if there is any major performance drawback for having
duplicate composite index, so that i can push hard for the change. Let
me know.thanks for your looking into this.
On Wed, Oct 20, 2010 at 5:10 PM, Rob Sargent <robjsargent@gmail.com
<mailto:robjsargent@gmail.com>> wrote:If you can think of one benefit from having the redundant index then by
all means keep it. It certainly eludes me. Seems to me, removing an
un-necessary index on a huge table can only be a good thing.On 10/20/2010 06:02 PM, DM wrote:
Its a huge table in production, i dont want to take any risk.
I can simulate and test this but i was to checking to see If any one
knows off hand about this.I can simulate it but
On Wed, Oct 20, 2010 at 4:57 PM, Rob Sargent<robjsargent@gmail.com <mailto:robjsargent@gmail.com>
<mailto:robjsargent@gmail.com <mailto:robjsargent@gmail.com>>> wrote:
Hm. Run some queries; drop the second version of the index
definition;
re-run the same queries; report to the group. The redundant
index isn't
helping, that much is certain.
On 10/20/2010 05:43 PM, DM wrote:
Composite Index question:
I have composite index on 3 columns on a table, by mistake the
composite
index was created twice on the table.
Will there any performance issues on this table because of
the 2 same
composite indexes?
Thanks
Deepak--
Sent via pgsql-general mailing list(pgsql-general@postgresql.org <mailto:pgsql-general@postgresql.org>
<mailto:pgsql-general@postgresql.org
<mailto:pgsql-general@postgresql.org>>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
DM <dm.aeqa@gmail.com> writes:
I know there is no benfit of having duplicate indexes.
Inorder for me to make change on production it requires lot of approvals and
stuff.
I wnat to know if there is any major performance drawback for having
duplicate composite index,
Of course there is: it doubles the index-update overhead every time you
update the table, in return for no benefit whatsoever. Get rid of the
duplicate index. (Now, if the table is seldom updated, it might not be
urgent to do so. But you ought to plan on doing it.)
regards, tom lane
On 10/20/2010 05:43 PM, DM wrote:
Composite Index question:
I have composite index on 3 columns on a table, by mistake the composite
index was created twice on the table.Will there any performance issues on this table because of the 2 same
composite indexes?Thanks
Deepak
Are the indices of the same type (e.g. both BTree) but with different index
names?
Is the second composite index the columns in same order as the first ? if
not in the same column order you might be seeing some benefit for some
queries but this is dependent on the queries filter clauses. If so you
might consider augmenting one or both of the indices to better suit your
queries.
From my experience, it appears to degrade performance because two indices
have to be maintained. (not to mention also appears to be a waste of disk
space) I am hopeful someone will correct me if I am wrong.
Ours were from people explicitly creating indexes on columns that had
indices implicitly created on them when the table was created. Cleanup was
pretty easy and painless on our production systems.
It's pretty easy to check for exact duplicates all over a given database as
well as how often each index is being used.
Check some of the queries here:
http://www.xzilla.net/blog/2008/Jul/Index-pruning-techniques.html
..:Mark
Thank you for all your suggestions and answers.
- Deepak
On Wed, Oct 20, 2010 at 7:41 PM, mark <dvlhntr@gmail.com> wrote:
Show quoted text
On 10/20/2010 05:43 PM, DM wrote:
Composite Index question:
I have composite index on 3 columns on a table, by mistake the composite
index was created twice on the table.Will there any performance issues on this table because of the 2 same
composite indexes?Thanks
DeepakAre the indices of the same type (e.g. both BTree) but with different index
names?Is the second composite index the columns in same order as the first ? if
not in the same column order you might be seeing some benefit for some
queries but this is dependent on the queries filter clauses. If so you
might consider augmenting one or both of the indices to better suit your
queries.From my experience, it appears to degrade performance because two indices
have to be maintained. (not to mention also appears to be a waste of disk
space) I am hopeful someone will correct me if I am wrong.Ours were from people explicitly creating indexes on columns that had
indices implicitly created on them when the table was created. Cleanup was
pretty easy and painless on our production systems.It's pretty easy to check for exact duplicates all over a given database as
well as how often each index is being used.
Check some of the queries here:
http://www.xzilla.net/blog/2008/Jul/Index-pruning-techniques.html..:Mark
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general