Re: Index on timestamp field, and now()

On Monday 11 February 2002 23:00, Tom Lane wrote:
> Denis Perchine <dyp(at)perchine(dot)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(at)earthlink(dot)net,maurice_brem(at)hotmail(dot)com,charlee45(at)hotmail(dot)com,netbusiness(at)v21mail(dot)co(dot)uk,mauriceb9(at)att(dot)net,addnewsletter(at)webmailstation(dot)com,kym(at)webmailstation(dot)com,mcquebec(at)iquebec(dot)com,editor(at)dietforum(dot)com,webworksdirect(at)yahoo(dot)com}

|
{0.156,0.121333,0.0726667,0.064,0.0343333,0.0313333,0.0223333,0.0203333,0.0193333,0.019}
|
{Admin(at)worldchristianministries(dot)org,ashlaws(at)themail(dot)com,calbear7(at)aol(dot)com,ed_hartwell(at)hotmail(dot)com,gifts1950(at)look(dot)ca,info(at)hosting4profits(dot)com,jitaforall(at)webmailstation(dot)com,mikejones(at)hello-mail(dot)com,rc-info4u(at)mail(dot)rc-info4u(dot)com,taffi(at)webmailstation(dot)com,zerodebt2(at)home(dot)com}
| 0.133443
queue | mailto | 0 | 37 | 13014 |
{"\"Intelligent Finance E-Mail Administrator\"
<smtpadmin(at)if(dot)com>",hello(at)honeyfrog(dot)com,"\"classifieds(at)becanada(dot)com\"
<classifieds(at)becanada(dot)com>","Traffic Commander
<dreimer(at)greatheadlines-instantly(dot)com>",bizopps(at)amfgolduc(dot)com,alenslist(at)SAFe-mail(dot)net,"\"_____Get_Ready\"
<fungames_makmony(at)hotmail(dot)com>",<main1(at)nicdomainia(dot)com>,"Home Office Digest
<digest(at)homeofficedigest(dot)com>",worldsubmitter(at)apexmail(dot)com}
|
{0.0223333,0.017,0.00833333,0.00766667,0.00533333,0.00366667,0.00333333,0.00333333,0.00333333,0.00333333}
| {"\"\" <FirebrandEnterprises77(at)yahoo(dot)com>","\"DKHughes\"
<dkh4unow(at)usa(dot)net>","\"Jeff O'Quinn\" <jsonc(at)yahoo(dot)com>","\"Olesya\"
<ola(at)orion-export(dot)lviv(dot)ua>","\"Victoria A Gomez - Eviel Enteprises\"
<freeadvertising2000(at)yahoo(dot)com>",<andrea(at)mgaweb(dot)com>,Judysathome(at)hotmail(dot)com,barbjoy2000(at)yahoo(dot)com,healhty4life(at)msn(dot)com,paul418us(at)ecoquestintl(dot)com,"юв╠ш╦╝╫╛
<jjuguli5(at)dreamwiz(dot)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(at)netspy(dot)org,mauriceb9(at)att(dot)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