Thread: Is it possible to create row-wise indexable condition for special case...
Hi,
I know that for condition like ((field1>value1) or (field1=value1 and field2>value2)) I could built index on (field1, field2) and use indexable condition like (field1, field2) > (value1, value2).
However, I have very tricky query which requires an indexable condition like (field1>value1) or (field1=value1 and field2<value2).
I guess that I could use index definition like ON(field1, field2 DESC) however, I can not build query condition which would use that index effectively.
Is it possible and my mind just went black on simple task?
PS: field2 is varchar type so I couldn't using the negative values trick
build index ON(field1, (-field2))
and write something like WHERE (field1, -field2) > (value1, -value2).
--
Maxim Boguk
Senior Postgresql DBA
http://www.postgresql-consulting.ru/
Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678
Skype: maxim.boguk
Jabber: maxim.boguk@gmail.com
МойКруг: http://mboguk.moikrug.ru/
"People problems are solved with people.
If people cannot solve the problem, try technology.
People will then wish they'd listened at the first stage."
I know that for condition like ((field1>value1) or (field1=value1 and field2>value2)) I could built index on (field1, field2) and use indexable condition like (field1, field2) > (value1, value2).
However, I have very tricky query which requires an indexable condition like (field1>value1) or (field1=value1 and field2<value2).
I guess that I could use index definition like ON(field1, field2 DESC) however, I can not build query condition which would use that index effectively.
Is it possible and my mind just went black on simple task?
PS: field2 is varchar type so I couldn't using the negative values trick
build index ON(field1, (-field2))
and write something like WHERE (field1, -field2) > (value1, -value2).
--
Maxim Boguk
Senior Postgresql DBA
http://www.postgresql-consulting.ru/
Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678
Skype: maxim.boguk
Jabber: maxim.boguk@gmail.com
МойКруг: http://mboguk.moikrug.ru/
"People problems are solved with people.
If people cannot solve the problem, try technology.
People will then wish they'd listened at the first stage."
Re: Is it possible to create row-wise indexable condition for special case...
From
François Beausoleil
Date:
Le 2012-08-06 à 01:39, Maxim Boguk a écrit :
I know that for condition like ((field1>value1) or (field1=value1 and field2>value2)) I could built index on (field1, field2) and use indexable condition like (field1, field2) > (value1, value2).
However, I have very tricky query which requires an indexable condition like (field1>value1) or (field1=value1 and field2<value2).
I guess that I could use index definition like ON(field1, field2 DESC) however, I can not build query condition which would use that index effectively.
Is it possible and my mind just went black on simple task?
PS: field2 is varchar type so I couldn't using the negative values trick
build index ON(field1, (-field2))
and write something like WHERE (field1, -field2) > (value1, -value2).
--
Maxim Boguk
Senior Postgresql DBA
http://www.postgresql-consulting.ru/
Could you perhaps use bitmapped indexes? Such that you'd have two indexes, and the optimizer would AND the two indexes together?
Hope that helps!
François