Thread: Index on timestamp field, and now()
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.
webmailstation=> explain analyze select id,send_date from queue where
send_date > now()+'20 year'::interval;
NOTICE: QUERY PLAN:
Seq Scan on queue (cost=0.00..10568.09 rows=30268 width=12) (actual
time=29347.82..29347.82 rows=0 loops=1)
Total runtime: 29347.97 msec
EXPLAIN
webmailstation=> explain analyze select id,send_date from queue where
send_date > '2022-1-1';
NOTICE: QUERY PLAN:
Index Scan using queue_senddate_key on queue (cost=0.00..37.71 rows=9
width=12) (actual time=29.20..29.20 rows=0 loops=1)
Total runtime: 30.26 msec
EXPLAIN
webmailstation=> set enable_seqscan to off;
SET VARIABLE
webmailstation=> explain analyze select id,send_date from queue where
send_date > now()+'20 year'::interval;
NOTICE: QUERY PLAN:
Seq Scan on queue (cost=100000000.00..100010568.09 rows=30268 width=12)
(actual time=24775.73..24775.73 rows=0 loops=1)
Total runtime: 24775.88 msec
EXPLAIN
webmailstation=> select id,send_date from queue where send_date >
'2022-1-1'; id | send_date
----+-----------
(0 rows)
webmailstation=> select count(*) from queue;
count
-------
89461
(1 row)
This is a statistics for this column.
webmailstation=# select * from pg_statistic where starelid=16729 and
staattnum=9;
starelid | staattnum | stanullfrac | stawidth | stadistinct | stakind1 |
stakind2 | stakind3 | stakind4 | staop1 | staop2 | staop3 | staop4 |
stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 |
stavalues1
| stavalues2 | stavalues3 | stavalues4
----------+-----------+-------------+----------+-------------+----------+----
------+----------+----------+--------+--------+--------+--------+------------
-+-------------+-------------+-------------+---------------------------------
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
-------------------------------------------------------+------------+--------
----+------------ 16729 | 9 | 0 | 8 | -1 |
2 | 3 | 0 | 0 | 1322 | 1322 | 0 | 0 |
| {-0.142043} | | | {"2001-12-27
08:01:04-05","2002-02-11 07:21:53.995488-05","2002-02-11
11:11:03.998751-05","2002-02-11 17:09:21.375388-05","2002-02-12
07:50:49.80789-05","2002-02-12 23:46:06-05","2002-02-13
23:58:54-05","2002-02-15 15:13:30.445873-05","2002-02-17
15:54:10.359982-05","2002-02-26 07:41:59-05","2003-07-21 13:10:06-04"} |
(1 row)
Any ides would be appreciated.
--
Denis
> 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
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
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