Hello everyone, I just joined the list. Adam Lang convinced me when he got
some answers for me from your list and sent it to the PHP PG list.
Thanks for all of your input. Let me change my question somewhat to clear up
my concern. How much of a write performance hit will I take if I create say
3 or 4 different indicies on one large table (max of 100K rows)? I read
Bruce Momjian's book (not a bad book) and he alluded to these problems but
was not specific. I just want to know if any of you had this issue using a
certain number of tables and indicies so that I might be able to avoid it
myself. Thanks.
Brent
Depending on what his queries actually are, perhaps it's
sufficient
to create one index on (col3,col2,col1), rather than on
(col1,col2,col3)
as I presume his first index currently is. As Mike Ansley
points out,
Postgres can use the first N columns of an index if all N
are
constrained by a query's WHERE clause; but there is no
point in looking
at index columns beyond an unconstrained column, because if
you did
you'd be fighting the index order instead of being helped
by it. I think that the planner used to have some bugs that might
interfere
with recognition of these partial-index-match cases, but
it's been okay
with them since 7.0 for sure. To say more, we'd need to
know exactly
which PG version he's running and exactly what his queries
look like. regards, tom lane
"First, they ignore you. Then they laugh at you. Then they fight you. Then
you win."
-Mahatma Ghandi