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

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:


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Martin Hampl <Martin.Hampl (AT) gmx (DOT) de> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Wed, 7 Jan 2004, Martin Hampl wrote:

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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

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:


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

I wrote:
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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Cool

Martin.

Am 07.01.2004 um 23:35 schrieb Tom Lane:


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Wed, Jan 07, 2004 at 17:35:52 -0500,
Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> wrote:
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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

Bruno Wolff III <bruno (AT) wolff (DOT) to> writes:
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:

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org