pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedsingle index on more than two coulumns a bad thing?
9+ messages / 8 participants
[nested] [flat]
* single index on more than two coulumns a bad thing?
@ 2004-04-01 23:00 Palle Girgensohn <girgen@pingpong.net>
0 siblings, 3 replies; 9+ messages in thread
From: Palle Girgensohn @ 2004-04-01 23:00 UTC (permalink / raw)
To: pgsql-performance
Hi,
I remember reading a post ages ago, maybe from Vadim, about the fact that
people creating indices on more than two columns will be the first to be
put againts the wall when the revolution comes... sort of...
Is it always bad to create index xx on yy (field1, field2, field3);
I guess the problem is that the index might often grow bigger than the
table, or at least big enough not to speed up the queries?
/Palle
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: single index on more than two coulumns a bad thing?
@ 2004-04-02 00:35 Josh Berkus <josh@agliodbs.com>
parent: Palle Girgensohn <girgen@pingpong.net>
2 siblings, 2 replies; 9+ messages in thread
From: Josh Berkus @ 2004-04-02 00:35 UTC (permalink / raw)
To: Palle Girgensohn <girgen@pingpong.net>; pgsql-performance
Palle,
> Is it always bad to create index xx on yy (field1, field2, field3);
No, it seldom bad, in fact. I have some indexes that run up to seven
columns, becuase they are required for unique keys.
Indexes of 3-4 columns are often *required* for many-to-many join tables.
I'm afraid that you've been given some misleading advice.
> I guess the problem is that the index might often grow bigger than the
> table, or at least big enough not to speed up the queries?
Well, yes ... a 4-column index on a 5-column table could be bigger than the
table if allowed to bloat and not re-indexed. But that's just a reason for
better maintainence.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: single index on more than two coulumns a bad thing?
@ 2004-04-02 04:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 9+ messages in thread
From: Tom Lane @ 2004-04-02 04:36 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Palle Girgensohn <girgen@pingpong.net>; pgsql-performance
Josh Berkus <josh@agliodbs.com> writes:
>> Is it always bad to create index xx on yy (field1, field2, field3);
> I'm afraid that you've been given some misleading advice.
I'd say it's a matter of getting your optimizations straight.
If you have a query that can make use of that index, and the query is
executed often enough to make it worth maintaining the index during
table updates, then by all means make the index.
The standard advice is meant to warn you against creating a zillion
indexes without any thought to what you'll be paying in update costs.
Indexes with more than a couple of columns are usually of only narrow
applicability, and so you have to be sure that they'll really pay for
themselves...
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: single index on more than two coulumns a bad thing?
@ 2004-04-02 04:59 Greg Stark <gsstark@mit.edu>
parent: Palle Girgensohn <girgen@pingpong.net>
2 siblings, 0 replies; 9+ messages in thread
From: Greg Stark @ 2004-04-02 04:59 UTC (permalink / raw)
To: Palle Girgensohn <girgen@pingpong.net>; +Cc: pgsql-performance
Palle Girgensohn <girgen@pingpong.net> writes:
> Is it always bad to create index xx on yy (field1, field2, field3);
All generalisations are false...
Seriously, it's true that as the length of your index key gets longer the
harder and harder it is to justify it. That doesn't mean they're always wrong,
but you should consider whether a shorter key would perform just as well.
The other problem with long index keys is that they often show up in the same
place as having dozens of indexes on the same table. Usually in shops where
the indexes were created after the fact looking at specific queries.
--
greg
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: single index on more than two coulumns a bad thing?
@ 2004-04-02 11:56 Aaron Werman <awerman2@hotmail.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 9+ messages in thread
From: Aaron Werman @ 2004-04-02 11:56 UTC (permalink / raw)
To: josh@agliodbs.com; Palle Girgensohn <girgen@pingpong.net>; pgsql-performance
another thing that I have all over the place is a hierarchy:
index on grandfather_table(grandfather)
index on father_table(grandfather, father)
index on son_table(grandfather, father, son)
almost all of my indices are composite. Are you thinking about composite
indices with low cardinality leading columns?
/Aaron
----- Original Message -----
From: "Josh Berkus" <josh@agliodbs.com>
To: "Palle Girgensohn" <girgen@pingpong.net>;
<pgsql-performance@postgresql.org>
Sent: Thursday, April 01, 2004 7:35 PM
Subject: Re: [PERFORM] single index on more than two coulumns a bad thing?
> Palle,
>
> > Is it always bad to create index xx on yy (field1, field2, field3);
>
> No, it seldom bad, in fact. I have some indexes that run up to seven
> columns, becuase they are required for unique keys.
>
> Indexes of 3-4 columns are often *required* for many-to-many join tables.
>
> I'm afraid that you've been given some misleading advice.
>
> > I guess the problem is that the index might often grow bigger than the
> > table, or at least big enough not to speed up the queries?
>
> Well, yes ... a 4-column index on a 5-column table could be bigger than
the
> table if allowed to bloat and not re-indexed. But that's just a reason
for
> better maintainence.
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: single index on more than two coulumns a bad thing?
@ 2004-04-02 15:56 Bruno Wolff III <bruno@wolff.to>
parent: Palle Girgensohn <girgen@pingpong.net>
2 siblings, 1 reply; 9+ messages in thread
From: Bruno Wolff III @ 2004-04-02 15:56 UTC (permalink / raw)
To: Palle Girgensohn <girgen@pingpong.net>; +Cc: pgsql-performance
On Fri, Apr 02, 2004 at 01:00:45 +0200,
Palle Girgensohn <girgen@pingpong.net> wrote:
>
> Is it always bad to create index xx on yy (field1, field2, field3);
>
> I guess the problem is that the index might often grow bigger than the
> table, or at least big enough not to speed up the queries?
One place where you need them in postgres is enforcing unique multicolumn
keys. These will get created implicitly from the unique (or primary key)
constraint. It isn't all that unusual to have a table that describes
a many to many (to many ...) relationship where the primary key is all
of the columns.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: single index on more than two coulumns a bad thing?
@ 2004-04-03 13:24 Palle Girgensohn <girgen@pingpong.net>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 9+ messages in thread
From: Palle Girgensohn @ 2004-04-03 13:24 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-performance
--On fredag, april 02, 2004 09.56.04 -0600 Bruno Wolff III <bruno@wolff.to>
wrote:
> On Fri, Apr 02, 2004 at 01:00:45 +0200,
> Palle Girgensohn <girgen@pingpong.net> wrote:
>>
>> Is it always bad to create index xx on yy (field1, field2, field3);
>>
>> I guess the problem is that the index might often grow bigger than the
>> table, or at least big enough not to speed up the queries?
>
> One place where you need them in postgres is enforcing unique multicolumn
> keys. These will get created implicitly from the unique (or primary key)
> constraint. It isn't all that unusual to have a table that describes
> a many to many (to many ...) relationship where the primary key is all
> of the columns.
True, of course!
/Palle
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: single index on more than two coulumns a bad thing?
@ 2004-04-04 21:06 Leeuw van der, Tim <tim.leeuwvander@nl.unisys.com>
0 siblings, 1 reply; 9+ messages in thread
From: Leeuw van der, Tim @ 2004-04-04 21:06 UTC (permalink / raw)
To: pgsql-performance
Hi Aaron,
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of
> Aaron Werman
> Sent: vrijdag 2 april 2004 13:57
>
>
> another thing that I have all over the place is a hierarchy:
> index on grandfather_table(grandfather)
> index on father_table(grandfather, father)
> index on son_table(grandfather, father, son)
>
It depends on your data-distribution, but I find that in almost all cases it's beneficial to have your indexes the other way round in such cases:
index on grandfather_table(grandfather)
index on father_table(father, grandfather)
index on son_table(son, father, grandfather)
That usually gives a less common, more selective value at the start of the index, making the initial selection in the index smaller.
And AFAIK I don't have to rewrite my queries for that; the planner doesn't care about the order of expressions in the query that are on the same level.
That said, I tend to use 'surrogate keys'; keys generated from sequences or auto-number columns for my tables. It makes the tables less readable, but the indexes remain smaller.
Greetings,
--Tim
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: single index on more than two coulumns a bad thing?
@ 2004-04-05 02:09 Aaron Werman <awerman@hotmail.com>
parent: Leeuw van der, Tim <tim.leeuwvander@nl.unisys.com>
0 siblings, 0 replies; 9+ messages in thread
From: Aaron Werman @ 2004-04-05 02:09 UTC (permalink / raw)
To: Leeuw van der, Tim <tim.leeuwvander@nl.unisys.com>; pgsql-performance
You're absolutely correct that the general rule is to lead a composite index
with the highest cardinality index columns for fastest selectivity. Indices
and all physical design are based on usage. In this case of unique indices
supporting primary keys in a hierarchy, it depends. For selection of small
sets of arbitrary rows, your arrangement is best. For hierarchy based
queries, such as "for grandparent of foo, and parent of bar, give average
age of sons" - the hierarchy based index is often more efficient.
Surrogate keys have a role, and can improve performance, but also carry an
enormous penalty of intentionally obfuscating logical keys and data
semantics, and almost always lead to data errors not being caught because
they obscure irrational relationships. I hate them, but use them frequently
in high transaction rate operational systems where there is much functional
validation outside the dbms (and the apps behave therefore like object
databases and surrogate keys are network database pointers) and in data
warehousing (where downstream data cannot be corrected anyway).
/Aaron
----- Original Message -----
From: "Leeuw van der, Tim" <tim.leeuwvander@nl.unisys.com>
To: <pgsql-performance@postgresql.org>
Sent: Sunday, April 04, 2004 5:06 PM
Subject: Re: [PERFORM] single index on more than two coulumns a bad thing?
Hi Aaron,
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of
> Aaron Werman
> Sent: vrijdag 2 april 2004 13:57
>
>
> another thing that I have all over the place is a hierarchy:
> index on grandfather_table(grandfather)
> index on father_table(grandfather, father)
> index on son_table(grandfather, father, son)
>
It depends on your data-distribution, but I find that in almost all cases
it's beneficial to have your indexes the other way round in such cases:
index on grandfather_table(grandfather)
index on father_table(father, grandfather)
index on son_table(son, father, grandfather)
That usually gives a less common, more selective value at the start of the
index, making the initial selection in the index smaller.
And AFAIK I don't have to rewrite my queries for that; the planner doesn't
care about the order of expressions in the query that are on the same level.
That said, I tend to use 'surrogate keys'; keys generated from sequences or
auto-number columns for my tables. It makes the tables less readable, but
the indexes remain smaller.
Greetings,
--Tim
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2004-04-05 02:09 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-01 23:00 single index on more than two coulumns a bad thing? Palle Girgensohn <girgen@pingpong.net>
2004-04-02 00:35 ` Josh Berkus <josh@agliodbs.com>
2004-04-02 04:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-02 11:56 ` Aaron Werman <awerman2@hotmail.com>
2004-04-02 04:59 ` Greg Stark <gsstark@mit.edu>
2004-04-02 15:56 ` Bruno Wolff III <bruno@wolff.to>
2004-04-03 13:24 ` Palle Girgensohn <girgen@pingpong.net>
2004-04-04 21:06 Re: single index on more than two coulumns a bad thing? Leeuw van der, Tim <tim.leeuwvander@nl.unisys.com>
2004-04-05 02:09 ` Aaron Werman <awerman@hotmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox