adate::Date is equiv. to adate if adate is type of Date ?

Started by Oleg Bartunovalmost 27 years ago5 messageshackers

oleg@sai.msu.su

almost 27 years ago

Here is a problem I got while experiencing with date type:
when I use adate = 'today'::Date explain shows me postgres will use
index, but adate::Date = 'today'::Date (which is the same expression )doesn't
use index. This happens for 6.4.2 and 6.5 cvs.

Regards,

Oleg

PS.
btw, how I can find 'something' older than a month, i.e.
select * from titles where adate::date > 'today'::Date - '1 month'::timespan;

apod=> vacuum analyze;
VACUUM
apod=> explain select count(*) from titles where adate::Date = 'today'::Date;
NOTICE: QUERY PLAN:

Aggregate (cost=64.10 size=0 width=0)
-> Seq Scan on titles (cost=64.10 size=699 width=12)

EXPLAIN
apod=> explain select count(*) from titles where adate = 'today'::Date;
NOTICE: QUERY PLAN:

Aggregate (cost=2.04 size=0 width=0)
-> Index Scan using idx_adate on titles (cost=2.04 size=1 width=12)

EXPLAIN
apod=> select version();
version
------------------------------------------------------------------
PostgreSQL 6.4.2 on i586-pc-linux-gnu, compiled by gcc egcs-2.91.5
(1 row)

apod=> \d titles

Table    = titles
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| url                              | char()                           |    13 |
| adate                            | date                             |     4 |
| atitle                           | text                             |   var |
+----------------------------------+----------------------------------+-------+
Indices:  idx_adate
          idx_atitle
          idx_url
a

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Thomas Lockhart

lockhart@alumni.caltech.edu

almost 27 years ago

In reply to: Oleg Bartunov (#1)

Re: [HACKERS] adate::Date is equiv. to adate if adate is type of Date ?

btw, how I can find 'something' older than a month, i.e.
select * from titles
where adate::date > 'today'::Date - '1 month'::timespan;

select * from titles
where adate::date < 'today'::Date - '1 month'::timespan;

- Tom

--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California

Thomas Lockhart

lockhart@alumni.caltech.edu

almost 27 years ago

In reply to: Thomas Lockhart (#2)

Re: [HACKERS] adate::Date is equiv. to adate if adate is type of Date ?

btw, how I can find 'something' older than a month

select * from titles
where adate::date < 'today'::Date - '1 month'::timespan;

this problem doesn't works:
apod=> select * from titles
apod-> where adate::date < 'today'::Date - '1 month'::timespan;
ERROR: There is more than one possible operator '<'
for types 'date' and 'datetime'
but if I specify Datetime instead of Date it works, but still doesn't
use index.
apod=> explain select * from titles
apod-> where adate::datetime < 'today'::Datetime
apod-> - '1 month'::timespan;
NOTICE: QUERY PLAN:
Seq Scan on titles (cost=64.10 size=466 width=28)

OK, try

select * from titles
where adate < date('today'::Datetime - '1 month'::timespan);

although there may (still) be problems with Postgres recognizing that
it could use an index when the "constant" is an expression.

Let us know what you find out...

- Tom

--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California

Import Notes

Reference msg id not found: Pine.GSO.3.96.SK.990504095913.23733D-100000@ra | Resolved by subject fallback

Tom Lane

tgl@sss.pgh.pa.us

almost 27 years ago

In reply to: Thomas Lockhart (#3)

Re: [HACKERS] adate::Date is equiv. to adate if adate is type of Date ?

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:

OK, try

select * from titles
where adate < date('today'::Datetime - '1 month'::timespan);

although there may (still) be problems with Postgres recognizing that
it could use an index when the "constant" is an expression.

I'm afraid I can already predict the answer: the optimizer only knows
how to use an index to constrain the scan when it finds a WHERE clause
like "var op constant" or "constant op var". What you've got there
isn't a constant.

The right solution, of course, is to put in a rewrite phase that does
constant-expression folding (probably after any rule-generated changes).
We've talked about that before, but it ain't gonna happen for 6.5.

BTW, the original question was why "where adate::date < 'today'::date"
wouldn't work. What the optimizer sees in that case is
where function(var) < constant
so it doesn't know how to use an index for that either. Now, if you
had a functional index matching the function, it would know what to do.
But it'd be pretty silly to keep a separate functional index just to let
this work, seeing as how adate is already a date.

It might be nice if the parser could drop dummy type conversions
instead of leaving them as functions in the parse tree... although
doing that as part of a general constant-expression folder is probably
a better answer.

regards, tom lane

Import Notes

Reply to msg id not found: YourmessageofTue04May1999131207+0000372EF227.A4F48886@alumni.caltech.edu | Resolved by subject fallback

Oleg Bartunov

oleg@sai.msu.su

almost 27 years ago

In reply to: Thomas Lockhart (#3)

Re: [HACKERS] adate::Date is equiv. to adate if adate is type of Date ?

On Tue, 4 May 1999, Thomas Lockhart wrote:

Date: Tue, 04 May 1999 13:12:07 +0000
From: Thomas Lockhart <lockhart@alumni.caltech.edu>
To: Oleg Bartunov <oleg@sai.msu.su>
Cc: Postgres Hackers List <hackers@postgreSQL.org>
Subject: Re: [HACKERS] adate::Date is equiv. to adate if adate is type of Date ?

btw, how I can find 'something' older than a month

select * from titles
where adate::date < 'today'::Date - '1 month'::timespan;

this problem doesn't works:
apod=> select * from titles
apod-> where adate::date < 'today'::Date - '1 month'::timespan;
ERROR: There is more than one possible operator '<'
for types 'date' and 'datetime'
but if I specify Datetime instead of Date it works, but still doesn't
use index.
apod=> explain select * from titles
apod-> where adate::datetime < 'today'::Datetime
apod-> - '1 month'::timespan;
NOTICE: QUERY PLAN:
Seq Scan on titles (cost=64.10 size=466 width=28)

OK, try

select * from titles
where adate < date('today'::Datetime - '1 month'::timespan);

although there may (still) be problems with Postgres recognizing that
it could use an index when the "constant" is an expression.

Let us know what you find out...

No, it's doing Seq Scan. I checked with 6.4.2 and current 6.5 cvs

Oleg

- Tom

--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California