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