Re: Odd new symptom - database locking up on a query

Doug Fields <dfields(at)pexicom(dot)com> writes:
> CREATE INDEX idx ON table (a,b);

> And the query is of the form: (with hundreds in the static IN)

> explain DELETE FROM table WHERE b=44 AND a IN
> (1071164,1071176,1071188,1071200,1071212,1071224,1071236,1071248,1071260,1071272,1071284,1071296);

> However, if the index is manually rebuilt accidentally as:

> CREATE INDEX idx ON table (b,a);

> (Note the a,b is reversed) THEN IT WILL NOT BE USED.

Yup. This has to do with the planner's search algorithm for potentially
useful indexscan qualifications. Given an indexable OR clause it's
relatively cheap to see if we can extend it to additional index columns,
but the other way around would require unconstrained search through all
possible pairs of WHERE clauses, which looks like a bad idea to me.

regards, tom lane