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

On Mon, 8 Jul 2002, Tom Lane wrote:

> 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.

Tom,

Are you sure about this? I read it as Doug is saying normally the index is
built with the order a, b which wouldn't be surprising [to me] if the index
wasn't used in a query using a test like b = 4 and a IN (lots). Whereas if the
index is built with the order reversed to b, a then the index really isn't used
but one would have thought it a good candidate for use.

I'm sure a while ago it was you who told me that the order in a multi column
index was significant and the first item was the 'major' selector...but then
may be I didn't read it properly and my mind just filled in what I would have
expected to be the case. I'll see if I can find the email but in the meantime
could you restate whether an index built with the order b,a would be a good
candidate for use in a query using a where clause of b = x AND a IN (long
list) please?

Thanks,

--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants