Ed Loehr <ELOEHR@austin.rr.com> writes:
> Tom Lane wrote:
>> Oh my, *that's* interesting. I have no idea what could be causing that.
> Speculating, does the genetic algorithm twiddle any of the planner's
> stats?
No, or at least no more than regular planning does. Let's say it's not
*supposed* to. When dealing with a hard-to-characterize bug, it's wise
not to rule anything out...
> I ask because I know some of my other queries involve 6 or
> more tables, and I seem to recall that was a trigger point for genetic
> algorithms to kick in with default settings.
I think the default is 11 tables in 6.5.*. At least I get
play=> show geqo;
NOTICE: GEQO is ON beginning with 11 relations
SHOW VARIABLE
> create index mytable_dog_idx on mytable(dog_id);
> create index mytable_cat_idx on mytable(cat_id);
> create index mytable_dogcat_idx on mytable(dog_id,cat_id);
> ...thinking these indices would allow the fastest lookups from 3 different
> angles (at the cost of slower inserts, of course). Not sure my intuition
> here corresponds directly with the technical reality...
I doubt the 2-column index earns its keep given that you have another
index on the front column. A multicolumn index is a pretty specialized
beast, so I don't recommend creating one unless you have a very specific
heavily-used query in mind. (Of course, if you're making a multicol
UNIQUE index to enforce uniqueness of a multicol primary key, that's
a different matter entirely. But if you're just fishing for performance
improvements, you're probably fishing in the wrong place.)
> Your question also reminds me of a scenario I'd wondered about:
> create table mytable (
> id serial,
> ...
> primary key (id)
> );
> create unique index mytable_id on mytable(id);
> The primary key designation implicitly creates a unique index
> ('mytable_id_pkey', is it?).
Yes, I think so.
> What happens if I inadvertently create
> another unique index on the same field (other than being worthless,
> redundant, and a needless performance hit)?
AFAIK it should work, but as you say it's a useless performance hit.
It's barely conceivable that there's a bug lurking in there, since
it's a very-seldom-exercised case. But having lots of (nonidentical)
indexes on one table is very well exercised, and it's tough to see
why it would matter if two of them happened to have identical
parameters.
regards, tom lane