Обсуждение: Checking = with timestamp field is slow

Hi all,
   I have a table which have more than 200000 records. I need to get
the records which matches like this

where today::date = '2004-11-05';

This is the only condition in the query. There is a btree index on the
column today.
Is there any way to optimise it.

rgds
Antony Paul

On Nov 5, 2004, at 4:16 PM, Antony Paul wrote:
> where today::date = '2004-11-05';
>
> This is the only condition in the query. There is a btree index on the
> column today.
> Is there any way to optimise it.

I'm sure others out there have better ideas, but you might want to try

where current_date = date '2004-11-05'

Might not make a difference at all, but perhaps PostgreSQL is coercing
both values to timestamp or some other type as you're only providing a
string to compare to a date. Then again, it might make no difference at
all.

My 1 cent.

Michael Glaesemann
grzm myrealbox com

On Nov 5, 2004, at 5:14 PM, Michael Glaesemann wrote:

>
> On Nov 5, 2004, at 4:16 PM, Antony Paul wrote:
>> where today::date = '2004-11-05';
>>
>> This is the only condition in the query. There is a btree index on the
>> column today.
>> Is there any way to optimise it.
>
> I'm sure others out there have better ideas, but you might want to try
>
> where current_date = date '2004-11-05'

Ach! just re-read that. today is one of your columns! Try

where today::date = date '2004-11-05'

On Fri, Nov 05, 2004 at 12:46:20PM +0530, Antony Paul wrote:

>    I have a table which have more than 200000 records. I need to get
> the records which matches like this
>
> where today::date = '2004-11-05';
>
> This is the only condition in the query. There is a btree index on the
> column today.  Is there any way to optimise it.

Is the today column a TIMESTAMP as the subject implies?  If so then
your queries probably aren't using the index because you're changing
the type to something that's not indexed.  Your queries should speed
up if you create an index on DATE(today):

CREATE INDEX foo_date_today_idx ON foo (DATE(today));

After creating the new index, use WHERE DATE(today) = '2004-11-05'
in your queries.  EXPLAIN ANALYZE should show that the index is
being used.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Fri, 2004-11-05 at 12:46 +0530, Antony Paul wrote:
> Hi all,
>    I have a table which have more than 200000 records. I need to get
> the records which matches like this
>
> where today::date = '2004-11-05';
>
> This is the only condition in the query. There is a btree index on the
> column today.
> Is there any way to optimise it.

Hi Antony,

I take it your field is called "today" (seems dodgy, but these things
happen...).  Anywa, have you tried indexing on the truncated value?

  create index xyz_date on xyz( today::date );
  analyze xyz;

That's one way.  It depends on how many of those 200,000 rows are on
each date too, as to whether it will get used by your larger query.

Regards,
                    Andrew.

-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ  Ltd,  PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/            PHYS: Level 2, 150-154 Willis St
DDI: +64(4)803-2201      MOB: +64(272)DEBIAN      OFFICE: +64(4)499-2267
      When in doubt, tell the truth.
                -- Mark Twain
-------------------------------------------------------------------------

On Fri, Nov 05, 2004 at 05:32:49PM +0900, Michael Glaesemann wrote:
>
> On Nov 5, 2004, at 5:14 PM, Michael Glaesemann wrote:
>
> >
> >On Nov 5, 2004, at 4:16 PM, Antony Paul wrote:
> >>where today::date = '2004-11-05';
> >>
> >>This is the only condition in the query. There is a btree index on the
> >>column today.
> >>Is there any way to optimise it.
> >
> >I'm sure others out there have better ideas, but you might want to try
> >
> >where current_date = date '2004-11-05'
>
> Ach! just re-read that. today is one of your columns! Try
>
> where today::date = date '2004-11-05'

Casting '2004-11-05' to DATE shouldn't be necessary, at least not
in 7.4.5.

test=> EXPLAIN ANALYZE SELECT * FROM foo WHERE today::DATE = '2004-11-05';
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..268.00 rows=50 width=16) (actual time=0.592..50.854 rows=1 loops=1)
   Filter: ((today)::date = '2004-11-05'::date)


As you can see, '2004-11-05' is already cast to DATE.  The sequential
scan is happening because there's no index on today::DATE.


test=> CREATE INDEX foo_date_idx ON foo (DATE(today));
CREATE INDEX
test=> EXPLAIN ANALYZE SELECT * FROM foo WHERE DATE(today) = '2004-11-05';
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Index Scan using foo_date_idx on foo  (cost=0.00..167.83 rows=50 width=16) (actual time=0.051..0.061 rows=1 loops=1)
   Index Cond: (date(today) = '2004-11-05'::date)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

After a long battle with technology, antonypaul24@gmail.com (Antony Paul), an earthling, wrote:
> Hi all,
>    I have a table which have more than 200000 records. I need to get
> the records which matches like this
>
> where today::date = '2004-11-05';
>
> This is the only condition in the query. There is a btree index on the
> column today.
> Is there any way to optimise it.

How about changing the criterion to:

  where today between '2004-11-05' and '2004-11-06';

That ought to make use of the index on "today".
--
"cbbrowne","@","ntlug.org"
http://www.ntlug.org/~cbbrowne/sgml.html
"People need to quit pretending they can invent THE interface and walk
away from it, like some Deist fantasy." -- Michael Peck

On Fri, Nov 05, 2004 at 07:47:54AM -0500, Christopher Browne wrote:
>
> How about changing the criterion to:
>
>   where today between '2004-11-05' and '2004-11-06';
>
> That ought to make use of the index on "today".

Yes it should, but it'll also return records that have a "today"
value of '2004-11-06 00:00:00' since "x BETWEEN y AND z" is equivalent
to "x >= y AND x <= z".  Try this instead:

  WHERE today >= '2004-11-05' AND today < '2004-11-06'

In another post I suggested creating an index on DATE(today).  The
above query should make that unnecessary, although in 8.0 such an
index would be used in queries like this:

  WHERE today IN ('2004-09-01', '2004-10-01', '2004-11-01');

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/