Re: Unique indexes not unique? - Mailing list pgsql-sql

From Jimmy Mäkelä
Subject Re: Unique indexes not unique?
Date
Msg-id D1045567F50DD311AB1B00508B3188E9026546D9@RINGHALS
Whole thread Raw
In response to Unique indexes not unique?  (Jimmy Mäkelä <jimmy.makela@agent25.se>)
List pgsql-sql
From: Tomasz Myrta [mailto:jasiek@klaster.net]
> I'm not sure unique index works properly for null values. I can't 
> explain, why. Maybe it comes from SQL standard - null i a 
> special value 

Yeah, I thought about that too, but I think that behaviour is really bad and
would consider it a bug. There are good reasons for having a special SQL null,
but
none of these apply to unique indexes (not that I can think of anyway).

> Try to rewrite your query to show postgres how to use index on AB:
> SELECT * FROM "table"
> WHERE
> (a = 1 AND b > 1232132 AND b < 123123123213123) or
> (a = 2 AND b > 1232132 AND b < 123123123213123) or
> (a = 3 AND b > 1232132 AND b < 123123123213123);

Sure, this works, and is an improvement to the UNION-version, but I think
postgres should be able do these substitutions by itself in the
planner/optimizer...

Or is there any method for specifying optimizer hints?

Regards,
Jimmy


pgsql-sql by date:

Previous
From: Tomasz Myrta
Date:
Subject: Re: Unique indexes not unique?
Next
From: Stephan Szabo
Date:
Subject: Re: Unique indexes not unique?