"Chris Velevitch" <chris.velevitch@gmail.com> writes: > I have a query on a table:- > X between k1 and k2 or X < k1 and Y <> k3 > where k1, k2, k3 are constants. > How would this query work, if I created an index on X and a partial > index on X where Y <> k3? Is it worth the trouble? You didn't mention the statistics involved, but ordinarily I'd think a non-equal condition is too nonselective to justify the cost of maintaining an extra index. The real problem here is that "X < k1" is probably too nonselective as well, which will likely lead the planner to decide that a plain seqscan is the cheapest solution. In principle the above could be done with a BitmapOr of two indexscans on X, but unless the constants are such that only a small fraction of rows are going to be selected, it's likely that a seqscan is the way to go. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend

"Chris Velevitch" <chris.velevitch@gmail.com> writes: > On Dec 7, 2007 2:39 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Is it worth the trouble? You didn't mention the statistics involved, >> but ordinarily I'd think a non-equal condition is too nonselective >> to justify the cost of maintaining an extra index. > Yes, I did neglect to mention the stats. Y is the status of the record > which represents the life cycle of the record starting at zero and > ending up at k3. So basically the query would retrieving and the > partial index will indexing records which aren't at their end of life. > So the number of records where Y <> k3 will be low and the number of > records where Y = k3 will be growing over time. OK, in that case the partial index might be worth trying. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq