Re: Index on timestamp field, and now()

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