Thread: partial index on varchar-coloumn in 7.4.1
Hi, I am trying to use an partial index on a varchar-coloumn as described in the documentation (section 11.7.) The commands I ran were: CREATE INDEX word_idx on token (word) where not (word = 'the'); ANALYZE; The problem is: PostgreSQL doesn't seem to use this index at all. It does use it if I create the index for the whole table. Do partial indexes not work for varchar? Or do I have a syntax error in my command? Thanks, Martin.
Hi, I tried it again on an integer-coloumn. With 'create INDEX word_idx ON token (word) where word = 15;' it works, with 'create INDEX word_idx ON token (word) where not (word = 15);' it doesn't. Why? (And yes, I ran analyze each time.) Thanks, Martin. Am 07.01.2004 um 13:08 schrieb Martin Hampl: > Hi, > > I am trying to use an partial index on a varchar-coloumn as described > in the documentation (section 11.7.) > > The commands I ran were: > > CREATE INDEX word_idx on token (word) where not (word = 'the'); > ANALYZE; > > The problem is: PostgreSQL doesn't seem to use this index at all. It > does use it if I create the index for the whole table. > > Do partial indexes not work for varchar? Or do I have a syntax error > in my command? > > Thanks, > Martin. > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
Martin Hampl <Martin.Hampl@gmx.de> writes:
> Do partial indexes not work for varchar?
Works for me:
regression=# create table token(word varchar(30));
CREATE TABLE
regression=# CREATE INDEX word_idx on token (word) where not (word = 'the');
CREATE INDEX
regression=# explain select * from token where word = 'abc' and not (word = 'the');
QUERY PLAN
------------------------------------------------------------------------
Index Scan using word_idx on token (cost=0.00..17.02 rows=5 width=33)
Index Cond: ((word)::text = 'abc'::text)
Filter: ((word)::text <> 'the'::text)
(3 rows)
You may have unrealistic expectations about the planner's ability to
prove that the index predicate condition is implied by the query
WHERE clause. This will not use the index:
regression=# explain select * from token where word = 'abc';
QUERY PLAN
-------------------------------------------------------
Seq Scan on token (cost=0.00..22.50 rows=5 width=33)
Filter: ((word)::text = 'abc'::text)
(2 rows)
You know and I know that "word = 'abc'" implies "not (word = 'the')",
but the planner cannot make that deduction. The pred_test() routine
doesn't really have any intelligence about conditions involving NOT.
regards, tom lane
On Wed, 7 Jan 2004, Martin Hampl wrote: > Hi, > > I tried it again on an integer-coloumn. With 'create INDEX word_idx ON > token (word) where word = 15;' it works, with 'create INDEX word_idx ON > token (word) where not (word = 15);' it doesn't. It works for me when the query is written with the partial index condition. It's not going to deduce that word=2 implies not(word=15).
Hi, Ah. Thanks. I wouldn't have thought of that. That's a solution I can work with (if not a very elegant one). Regard, Martin. Am 07.01.2004 um 16:29 schrieb Tom Lane: > Martin Hampl <Martin.Hampl@gmx.de> writes: >> Do partial indexes not work for varchar? > > Works for me: > > regression=# create table token(word varchar(30)); > CREATE TABLE > regression=# CREATE INDEX word_idx on token (word) where not (word = > 'the'); > CREATE INDEX > regression=# explain select * from token where word = 'abc' and not > (word = 'the'); > QUERY PLAN > ----------------------------------------------------------------------- > - > Index Scan using word_idx on token (cost=0.00..17.02 rows=5 width=33) > Index Cond: ((word)::text = 'abc'::text) > Filter: ((word)::text <> 'the'::text) > (3 rows) > > You may have unrealistic expectations about the planner's ability to > prove that the index predicate condition is implied by the query > WHERE clause. This will not use the index: > > regression=# explain select * from token where word = 'abc'; > QUERY PLAN > ------------------------------------------------------- > Seq Scan on token (cost=0.00..22.50 rows=5 width=33) > Filter: ((word)::text = 'abc'::text) > (2 rows) > > You know and I know that "word = 'abc'" implies "not (word = 'the')", > but the planner cannot make that deduction. The pred_test() routine > doesn't really have any intelligence about conditions involving NOT. > > regards, tom lane >
I wrote:
> Martin Hampl <Martin.Hampl@gmx.de> writes:
>> Do partial indexes not work for varchar?
> You know and I know that "word = 'abc'" implies "not (word = 'the')",
> but the planner cannot make that deduction. The pred_test() routine
> doesn't really have any intelligence about conditions involving NOT.
Actually, this was easier to fix than I thought. As of CVS tip:
regression=# create table token(word varchar(30));
CREATE TABLE
regression=# CREATE INDEX word_idx on token (word) where not (word = 'the');
CREATE INDEX
regression=# explain select * from token where word = 'abc' ;
QUERY PLAN
------------------------------------------------------------------------
Index Scan using word_idx on token (cost=0.00..17.07 rows=5 width=33)
Index Cond: ((word)::text = 'abc'::text)
(2 rows)
regression=#
There's still no intelligence about NOT in the theorem prover, but it
turns out that it's not seeing NOT. By the time the expressions get to
the point of being compared, NOT (a=b) has been folded to a<>b, and it
turned out to be fairly straightforward to extend the existing logic to
reason about such cases. The above example requires a process like
"a = x implies a <> y if x <> y" (where x and y are constants, so the
"if" part can be checked). This fits right in with what the code could
do already, which was cases like "a > x implies a > y if x > y".
So it'll work more naturally in 7.5.
regards, tom lane
Cool :) Martin. Am 07.01.2004 um 23:35 schrieb Tom Lane: > I wrote: >> Martin Hampl <Martin.Hampl@gmx.de> writes: >>> Do partial indexes not work for varchar? > >> You know and I know that "word = 'abc'" implies "not (word = 'the')", >> but the planner cannot make that deduction. The pred_test() routine >> doesn't really have any intelligence about conditions involving NOT. > > Actually, this was easier to fix than I thought. As of CVS tip: > > regression=# create table token(word varchar(30)); > CREATE TABLE > regression=# CREATE INDEX word_idx on token (word) where not (word = > 'the'); > CREATE INDEX > regression=# explain select * from token where word = 'abc' ; > QUERY PLAN > ----------------------------------------------------------------------- > - > Index Scan using word_idx on token (cost=0.00..17.07 rows=5 width=33) > Index Cond: ((word)::text = 'abc'::text) > (2 rows) > > regression=# > > There's still no intelligence about NOT in the theorem prover, but it > turns out that it's not seeing NOT. By the time the expressions get to > the point of being compared, NOT (a=b) has been folded to a<>b, and it > turned out to be fairly straightforward to extend the existing logic to > reason about such cases. The above example requires a process like > "a = x implies a <> y if x <> y" (where x and y are constants, so the > "if" part can be checked). This fits right in with what the code could > do already, which was cases like "a > x implies a > y if x > y". > So it'll work more naturally in 7.5. > > regards, tom lane >
On Wed, Jan 07, 2004 at 17:35:52 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > There's still no intelligence about NOT in the theorem prover, but it > turns out that it's not seeing NOT. By the time the expressions get to > the point of being compared, NOT (a=b) has been folded to a<>b, and it > turned out to be fairly straightforward to extend the existing logic to > reason about such cases. The above example requires a process like > "a = x implies a <> y if x <> y" (where x and y are constants, so the > "if" part can be checked). This fits right in with what the code could > do already, which was cases like "a > x implies a > y if x > y". > So it'll work more naturally in 7.5. This implication relies on = being transitive. I was curious about how you test for that since there doesn't seem to be a direct way to know that. My guess would be that operators used in an opclass are assumed to be transitive, since I don't think indexing would work if they weren't. This same test would also work for other transitive operators with a negator. For example: a < x implies y >= a if y >= x You can do a bit better if you know that exactly one of the relations =, <, > is true for any ordered pair of operands. But there may be cases where you don't have that much structure.
Bruno Wolff III <bruno@wolff.to> writes:
> This implication relies on = being transitive. I was curious about how you
> test for that since there doesn't seem to be a direct way to know that.
> My guess would be that operators used in an opclass are assumed to be
> transitive, since I don't think indexing would work if they weren't.
Exactly. The predicate tester uses operators that are in btree
opclasses. The assumptions required for a working btree index are
AFAICS more than sufficient for the purposes here; in particular a
btree requires a total ordering on the data type, so this holds:
> You can do a bit better if you know that exactly one of the relations
> =, <, > is true for any ordered pair of operands.
> This same test would also work for other transitive operators with a negator.
> For example:
> a < x implies y >= a if y >= x
Right, but since all those things are already in opclasses, I don't
think it matters. The problem with <> is that it is *not* part of
btree opclasses (an ancient Berkeley decision, presumably based on the
assumption that <> would never be a useful index scan constraint).
What I added to the code yesterday is the ability to look to see if the
given operator has a negator that is an "=" member of a btree opclass.
If so (and if the negator marking is semantically correct) then the
operator must behave as <>, and the other members of the opclass can be
used with it in these sorts of syllogisms.
regards, tom lane
Hi, I tried it again on an integer-coloumn. With 'create INDEX word_idx ON token (word) where word = 15;' it works, with 'create INDEX word_idx ON token (word) where not (word = 15);' it doesn't. Why? (And yes, I ran analyze each time.) Thanks, Martin. Am 07.01.2004 um 13:08 schrieb Martin Hampl: > Hi, > > I am trying to use an partial index on a varchar-coloumn as described > in the documentation (section 11.7.) > > The commands I ran were: > > CREATE INDEX word_idx on token (word) where not (word = 'the'); > ANALYZE; > > The problem is: PostgreSQL doesn't seem to use this index at all. It > does use it if I create the index for the whole table. > > Do partial indexes not work for varchar? Or do I have a syntax error > in my command? > > Thanks, > Martin. > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >