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

From: "Nigel J(dot) Andrews" <nandrews(at)investsystems(dot)co(dot)uk>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Doug Fields <dfields-pg-general(at)pexicom(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Odd new symptom - database locking up on a query
Date: 2002-07-08 22:53:40
Message-ID: Pine.LNX.4.21.0207082341450.2576-100000@ponder.fairway2k.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Hunter Hillegas 2002-07-08 23:21:51 Query Casting Help
Previous Message David Busby 2002-07-08 21:40:47 Returning bytea* from a C function