On 20/04/15 10:29, Tom Lane wrote:
Yves Dorfsman writes:
What about functions that are simpler such as upper()/lower()?
If you think those are simpler, you're much mistaken :-(. For instance,
"lower(first_name) = 'yves'" would have to be translated to something
like "first_name IN ('yves', 'y
Yves Dorfsman writes:
> What about functions that are simpler such as upper()/lower()?
If you think those are simpler, you're much mistaken :-(. For instance,
"lower(first_name) = 'yves'" would have to be translated to something
like "first_name IN ('yves', 'yveS', 'yvEs', 'yvES', ..., 'YVES')"
On 2015-04-19 15:33, Tom Lane wrote:
>
>> It's possible that in the future someone will implement an optimization
>> like this, but I'm not aware of anyone working on that and I wouldn't
>> hold my breath.
>
> Yeah. In principle you could make the planner do this. As Adam Williams
> notes nea
Tomas Vondra writes:
> On 04/19/15 22:10, Jon Dufresne wrote:
>> My point is, why force the user to take these extra steps or add
>> overhead when the the two queries (or two indexes) are functionally
>> equivalent. Shouldn't this is an optimization handled by the
>> database so the user doesn't n
On 04/19/15 22:10, Jon Dufresne wrote:
On Sun, Apr 19, 2015 at 10:42 AM, Tomas Vondra
wrote:
Or you might try creating an expression index ...
CREATE INDEX date_year_idx ON dates((extract(year from d)));
Certainly, but won't this add additional overhead in the form of two
indexes; one fo
On Sun, 2015-04-19 at 13:10 -0700, Jon Dufresne wrote:
> On Sun, Apr 19, 2015 at 10:42 AM, Tomas Vondra
> wrote:
> > On 04/19/15 19:16, Jon Dufresne wrote:
> >> Given the table:
> >> CREATE TABLE dates (id SERIAL, d DATE NOT NULL, t TEXT NOT NULL)
> >> With an *index* on field d. The following two
On Sun, Apr 19, 2015 at 10:42 AM, Tomas Vondra
wrote:
>
>
> On 04/19/15 19:16, Jon Dufresne wrote:
>>
>> Given the table:
>>
>> CREATE TABLE dates (id SERIAL, d DATE NOT NULL, t TEXT NOT NULL)
>>
>> With an *index* on field d. The following two queries are functionally
>> equivalent:
>>
>> 1. SELE
On 04/19/15 19:16, Jon Dufresne wrote:
Given the table:
CREATE TABLE dates (id SERIAL, d DATE NOT NULL, t TEXT NOT NULL)
With an *index* on field d. The following two queries are functionally
equivalent:
1. SELECT * FROM dates WHERE d >= '1900-01-01'
2. SELECT * FROM dates WHERE EXTRACT(year
Given the table:
CREATE TABLE dates (id SERIAL, d DATE NOT NULL, t TEXT NOT NULL)
With an *index* on field d. The following two queries are functionally
equivalent:
1. SELECT * FROM dates WHERE d >= '1900-01-01'
2. SELECT * FROM dates WHERE EXTRACT(year from d) >= 1900'
By functionally equivale
