Re: [HACKERS] "ExecInitIndexScan: both left and right..." meaning? - Mailing list pgsql-hackers

Tom Lane wrote:

> Ed Loehr <ELOEHR@austin.rr.com> writes:
> > ... query succeeds numerous times before going into a continuous
> > failure mode due to the error above.  Vacuuming the DB fixes the
> > problem "for a while".
>
> Oh my, *that's* interesting.  I have no idea what could be causing that.
> The error message you're getting suggests that the planner is generating
> an incorrect plan tree for the query, which I'd believe soon enough,
> but I don't understand why the behavior would change over time.
> A VACUUM could change the planner's results by altering the stored
> statistics for the tables --- but if you're not vacuuming, the plan
> should be the same every time.

No intermediate vacuuming is occurring, AFAIK (though I'm trying to figure
out how to trigger vacuuming on this error).  Speculating, does the genetic
algorithm twiddle any of the planner's stats?   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 am running with defaults.

> Does the EXPLAIN output showing the query plan change from when it's
> working to when it's not?  What would really be helpful is to see the
> EXPLAIN VERBOSE output in both states (preferably, the pretty-printed
> version that gets put in the postmaster log file, not the compressed
> version that gets sent to the client).

I will attempt to capture EXPLAIN output for the problem situation.

> Also, what indexes do you have on these tables?

I have single-column indices on most every foreign key field (ie,
contract_id), some unique and some not, and on every primary key field
(i.e., 'id' in the 'contract' table).  I have a few multi-column indices.
The only types I use in the entire database are INTEGER, SERIAL, FLOAT8,
DATETIME, and VARCHAR, and I have indices involving on all of these types
at one point or another.  I also have a few of what I'd call "overlapping"
indices, i.e.,
   create table mytable (       id serial,       dog_id integer,       cat_id integer,       ...   );   create index
mytable_dog_idxon mytable(dog_id);   create index mytable_cat_idx on mytable(cat_id);   create index mytable_dogcat_idx
onmytable(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...

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?).  What happens if I inadvertently create
another unique index on the same field (other than being worthless,
redundant, and a needless performance hit)?  I believe I have this
situation in some cases as a result of adding the 'primary key' designation
later, and hadn't gotten around to cleaning it up.  Does that smell like a
rat?  Any other ideas?

Cheers,
Ed Loehr