Index on timestamp field, and now()

I have quite interesting problem. I have a table with a timestamp field.
I have an index on it. When I use constant date in where clause, PostgreSQL
uses index. But when I try to use now() there, it uses a sequence scan.
As far as I can understand in inside any query now() is a constant.
What is the problem here.

You did not specify what version of PostgreSQL you are running, but it
may be that now() is returning abstime, not timestamp.

Use the constant "timestamp 'now'" instead; seems to work for me in
PgSQL 7.1.

- Thomas

Hello,

I have quite interesting problem. I have a table with a timestamp field.
I have an index on it. When I use constant date in where clause,
PostgreSQL uses index. But when I try to use now() there, it uses a
sequence scan. As far as I can understand in inside any query now() is a
constant. What is the problem here.

You did not specify what version of PostgreSQL you are running, but it
may be that now() is returning abstime, not timestamp.

Oops. Sorry. 7.2.

Use the constant "timestamp 'now'" instead; seems to work for me in
PgSQL 7.1.

No luck.

webmailstation=> explain select * from queue where send_date > timestamp
'now';
NOTICE: QUERY PLAN:

Seq Scan on queue (cost=0.00..10114.06 rows=80834 width=190)

EXPLAIN

Although exact search uses index scan:

webmailstation=> explain select * from queue where send_date = timestamp
'now';
NOTICE: QUERY PLAN:

Index Scan using queue_senddate_key on queue (cost=0.00..5.95 rows=1
width=190)

EXPLAIN

--
Denis

Denis Perchine <dyp@perchine.com> writes:

webmailstation=> explain select * from queue where send_date > timestamp
'now';
NOTICE: QUERY PLAN:

Seq Scan on queue (cost=0.00..10114.06 rows=80834 width=190)

EXPLAIN

Although exact search uses index scan:

webmailstation=> explain select * from queue where send_date = timestamp
'now';
NOTICE: QUERY PLAN:

Index Scan using queue_senddate_key on queue (cost=0.00..5.95 rows=1
width=190)

EXPLAIN

The second case proves that it's not a datatype or not-a-constant
problem. I'd guess that the failure of the first case indicates you've
never ANALYZEd the table, and so you're getting a default selectivity
estimate for the inequality operator (which is way too high to allow an
indexscan). If that's not so, what do you get from

select * from pg_stats where tablename = 'queue';

regards, tom lane

On Monday 11 February 2002 23:00, Tom Lane wrote:

Denis Perchine <dyp@perchine.com> writes:

webmailstation=> explain select * from queue where send_date > timestamp
'now';
NOTICE: QUERY PLAN:

Seq Scan on queue (cost=0.00..10114.06 rows=80834 width=190)

EXPLAIN

Although exact search uses index scan:

webmailstation=> explain select * from queue where send_date = timestamp
'now';
NOTICE: QUERY PLAN:

Index Scan using queue_senddate_key on queue (cost=0.00..5.95 rows=1
width=190)

EXPLAIN

The second case proves that it's not a datatype or not-a-constant
problem. I'd guess that the failure of the first case indicates you've
never ANALYZEd the table, and so you're getting a default selectivity
estimate for the inequality operator (which is way too high to allow an
indexscan). If that's not so, what do you get from

I do vacuum analyse each day.

select * from pg_stats where tablename = 'queue';

Here it is:

webmailstation=> select * from pg_stats where tablename = 'queue';
tablename | attname | null_frac | avg_width | n_distinct |

most_common_vals

|
most_common_freqs |

histogram_bounds

| correlation
-----------+------------+-----------+-----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
queue | id | 0 | 4 | -1 |

|
|
{2274684,6825516,7032890,7059311,7083141,7123450,7143019,7146392,7152628,7159878,7169783}

| 0.721363
queue | user_id | 0 | 4 | 1467 |
{23158,31217,31879,31887,1444,2791,31757,23999,31500,26348}

|
{0.156,0.134,0.0926667,0.064,0.0313333,0.0223333,0.0203333,0.0193333,0.019,0.0186667}
|
{74,4699,11747,15758,19281,23725,26749,29246,30014,31009,32048}

| 0.182981
queue | server_id | 0 | 4 | 1478 |
{33905,45072,46284,46309,96,1033,46054,33331,45605,37071}

|
{0.156,0.134,0.092,0.064,0.0313333,0.0223333,0.0203333,0.0193333,0.019,0.0186667}
|
{2,866,11782,19199,26852,32932,37710,41764,43258,44708,46579}

| 0.187472
queue | inbox_id | 0 | 4 | 19104 |
{0,5029659,5029953,5037489,5038764,5028275,5029395,5019861,5028162,5029354}

|
{0.0193333,0.00233333,0.00233333,0.00233333,0.00233333,0.002,0.002,0.00166667,0.00166667,0.00166667}
|
{1592799,4860565,4978749,4996037,5009699,5020169,5028122,5029846,5033005,5037548,5043243}

| 0.65866
queue | mailfrom | 0 | 26 | 1456 |
{spence2396@earthlink.net,maurice_brem@hotmail.com,charlee45@hotmail.com,netbusiness@v21mail.co.uk,mauriceb9@att.net,addnewsletter@webmailstation.com,kym@webmailstation.com,mcquebec@iquebec.com,editor@dietforum.com,webworksdirect@yahoo.com}

|
{0.156,0.121333,0.0726667,0.064,0.0343333,0.0313333,0.0223333,0.0203333,0.0193333,0.019}
|
{Admin@worldchristianministries.org,ashlaws@themail.com,calbear7@aol.com,ed_hartwell@hotmail.com,gifts1950@look.ca,info@hosting4profits.com,jitaforall@webmailstation.com,mikejones@hello-mail.com,rc-info4u@mail.rc-info4u.com,taffi@webmailstation.com,zerodebt2@home.com}
| 0.133443
queue | mailto | 0 | 37 | 13014 |
{"\"Intelligent Finance E-Mail Administrator\"
<smtpadmin@if.com>",hello@honeyfrog.com,"\"classifieds@becanada.com\"
<classifieds@becanada.com>","Traffic Commander
<dreimer@greatheadlines-instantly.com>",bizopps@amfgolduc.com,alenslist@SAFe-mail.net,"\"_____Get_Ready\"
<fungames_makmony@hotmail.com>",<main1@nicdomainia.com>,"Home Office Digest
<digest@homeofficedigest.com>",worldsubmitter@apexmail.com}
|
{0.0223333,0.017,0.00833333,0.00766667,0.00533333,0.00366667,0.00333333,0.00333333,0.00333333,0.00333333}
| {"\"\" <FirebrandEnterprises77@yahoo.com>","\"DKHughes\"
<dkh4unow@usa.net>","\"Jeff O'Quinn\" <jsonc@yahoo.com>","\"Olesya\"
<ola@orion-export.lviv.ua>","\"Victoria A Gomez - Eviel Enteprises\"
<freeadvertising2000@yahoo.com>",<andrea@mgaweb.com>,Judysathome@hotmail.com,barbjoy2000@yahoo.com,healhty4life@msn.com,paul418us@ecoquestintl.com,"О©╫в╠ш╦О©╫О©╫О©╫
<jjuguli5@dreamwiz.com>"} | 0.0239602
queue | subject | 0 | 45 | 9577 |
{"L@@K--SECRETS OF MAKING $5000 WEEKLY- GUARANTEED - FREE--EASY
","**SECRETS!! MAKE $5000+ WEEKLY - GUARANTEED-FREE-","SET UP YOUR OWN
MARKETING CAMPAIGN-TRACK ADS-AUTOMATIC.ALLY.","MONEY-MAKERS SECRETS - Learn
how to make at least $5000 weekly. ","GET YOUR BUSINESS WORLD LIST TO
Instantly Improve Any Business!","INSTANT SOLUTIONS TO YOUR BUSINESS
PROBLEMS, FOR FREE","Re: unique SFI co-op opportunity","**;MAKE $5000 WEEKLY.
STEP BY STEP INFO...GUARANTEED..FREE","Re:Was that a personal email?","Re:
Free Access to Porn!"} |
{0.0583333,0.0333333,0.0306667,0.0253333,0.0236667,0.021,0.0186667,0.017,0.017,0.0163333}
| {"","Berita laman web Tenaga Dalam","Diet Forum Mini Course
- Day 4","JaG , A FREE personalized & Automated e-commerce ","Re: Pay
Pal!",Re:AutoResponse,"Re:Get your LOVE Coupons from
CoolSavings!","Re:Secrets To Success! And The Magic Of Spillover!","Stacey
Bradley why not Name A Star?","You might be interested in this ...","О©╫О©╫О©╫О©╫О©╫О©╫О©╫О©╫
О©╫О©╫О©╫О©╫О©╫О©╫О©╫"}
| 0.00167003
queue | body | 0 | 4 | -1 |

|
|
{5145,2094923,2302297,2328718,2352548,2392857,2412426,2415799,2422035,2429285,2439191}

| 0.721358
queue | send_date | 0 | 8 | -1 |

|
|
{"2001-12-27 21:58:24-05","2002-02-12 08:48:18.967111-05","2002-02-12
15:14:51.89063-05","2002-02-13 04:06:19.979181-05","2002-02-13
16:20:37.753221-05","2002-02-14 12:03:09.714262-05","2002-02-15
15:15:58.04151-05","2002-02-17 11:06:16.964311-05","2002-02-20
08:40:57.795043-05","2002-03-12 07:25:46-05","2003-10-28 14:58:58-05"}
| -0.359735
queue | returnpath | 0.980667 | 23 | 2 |
{cyber2001@netspy.org,mauriceb9@att.net}

|
{0.0186667,0.000666667}
|

| 0.940939
queue | header | 0.432667 | 29 | 29 |
{"Content-Type: text/plain
"}

| {0.564667}

| {"Content-Type: text/html
","Content-Type: text/html
","Content-Type: text/html
","MIME-Version: 1.0
Content-Type: multipart/mixed;
boundary=\"200202111242480600811/webmailstation.com\"
Content-Transfer-Encoding: 8bit
Content-Disposition: inline
"}
| 0.992527
queue | counter | 0 | 4 | 41 | {0}

| {0.944333}
|
{1,7,10,12,14,16,18,19,19,19,24}

| 0.971716
(12 rows)

--
Denis

Denis Perchine <dyp@perchine.com> writes:

webmailstation=> explain select * from queue where send_date > timestamp
'now';
NOTICE: QUERY PLAN:

Seq Scan on queue (cost=0.00..10114.06 rows=80834 width=190)

EXPLAIN

queue | send_date | 0 | 8 | -1 |

|
|
{"2001-12-27 21:58:24-05","2002-02-12 08:48:18.967111-05","2002-02-12
15:14:51.89063-05","2002-02-13 04:06:19.979181-05","2002-02-13
16:20:37.753221-05","2002-02-14 12:03:09.714262-05","2002-02-15
15:15:58.04151-05","2002-02-17 11:06:16.964311-05","2002-02-20
08:40:57.795043-05","2002-03-12 07:25:46-05","2003-10-28 14:58:58-05"}
| -0.359735

According to this histogram, 90% of your table has send_date in the
future. Accordingly, seqscan is the right plan for the above query.

regards, tom lane

On Tuesday 12 February 2002 20:48, Tom Lane wrote:

Denis Perchine <dyp@perchine.com> writes:

webmailstation=> explain select * from queue where send_date > timestamp
'now';
NOTICE: QUERY PLAN:

Seq Scan on queue (cost=0.00..10114.06 rows=80834 width=190)

EXPLAIN

queue | send_date | 0 | 8 | -1 |

{"2001-12-27 21:58:24-05","2002-02-12 08:48:18.967111-05","2002-02-12
15:14:51.89063-05","2002-02-13 04:06:19.979181-05","2002-02-13
16:20:37.753221-05","2002-02-14 12:03:09.714262-05","2002-02-15
15:15:58.04151-05","2002-02-17 11:06:16.964311-05","2002-02-20
08:40:57.795043-05","2002-03-12 07:25:46-05","2003-10-28 14:58:58-05"}

| -0.359735

According to this histogram, 90% of your table has send_date in the
future. Accordingly, seqscan is the right plan for the above query.

But I use a comparison with now() + '20 years'::interval, not with now()...
And as I have mentioned, there is no any entries more than 20 years in the
feature there.

--
Denis

Denis Perchine <dyp@perchine.com> writes:

webmailstation=> explain select * from queue where send_date > timestamp
'now';

But I use a comparison with now() + '20 years'::interval, not with now()...

That's not the example you quoted. More to the point, if you write it
that way then it isn't a constant, at least not in 7.2's limited
understanding of what constant values are. You could work around that
with a custom function marked isCachable; see the archives.

regards, tom lane

On Tuesday 12 February 2002 21:34, Tom Lane wrote:

Denis Perchine <dyp@perchine.com> writes:

webmailstation=> explain select * from queue where send_date > timestamp
'now';

But I use a comparison with now() + '20 years'::interval, not with
now()...

That's not the example you quoted. More to the point, if you write it
that way then it isn't a constant, at least not in 7.2's limited
understanding of what constant values are. You could work around that
with a custom function marked isCachable; see the archives.

Oups. Sorry my mistake. But 7.2's limited understanding is enough to do
constant propagation as far as I can see. :-)))

webmailstation=> explain select * from queue where send_date > timestamp
'now' + '20 years'::interval;
NOTICE: QUERY PLAN:

Index Scan using queue_senddate_key on queue (cost=0.00..30.55 rows=8
width=192)

EXPLAIN

Thanks for the hint.

--
Denis

Denis Perchine <dyp@perchine.com> writes:

Oups. Sorry my mistake. But 7.2's limited understanding is enough to do
constant propagation as far as I can see. :-)))

Correct, it's the now() function that's not cachable --- but you can get
around that with the "timestamp 'now'" kluge.

regards, tom lane

Tom Lane wrote:

Denis Perchine <dyp@perchine.com> writes:

Oups. Sorry my mistake. But 7.2's limited understanding is enough to do
constant propagation as far as I can see. :-)))

Correct, it's the now() function that's not cachable --- but you can get
around that with the "timestamp 'now'" kluge.

Isn't CURRENT_TIMESTAMP the preferred method for this?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

Correct, it's the now() function that's not cachable --- but you can get
around that with the "timestamp 'now'" kluge.

Isn't CURRENT_TIMESTAMP the preferred method for this?

Doesn't make any difference as far as this point is concerned.
CURRENT_TIMESTAMP isn't cachable either.

regards, tom lane

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

Correct, it's the now() function that's not cachable --- but you can get
around that with the "timestamp 'now'" kluge.

Isn't CURRENT_TIMESTAMP the preferred method for this?

Doesn't make any difference as far as this point is concerned.
CURRENT_TIMESTAMP isn't cachable either.

My point was that CURRENT_TIMESTAMP in the more standard way; 'now' is a
PostgreSQL-ism. Both work fine.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026