Horton

Thread

  1. Checking = with timestamp field is slow

    Antony Paul <antonypaul24@gmail.com> — 2004-11-05T07:16:20Z

    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

  2. Re: Checking = with timestamp field is slow

    Michael Glaesemann <grzm@myrealbox.com> — 2004-11-05T08:14:01Z

    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

  3. Re: Checking = with timestamp field is slow

    Michael Glaesemann <grzm@myrealbox.com> — 2004-11-05T08:32:49Z

    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'

  4. Re: Checking = with timestamp field is slow

    Michael Fuhr <mike@fuhr.org> — 2004-11-05T08:34:01Z

    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/

  5. Re: Checking = with timestamp field is slow

    Andrew McMillan <andrew@catalyst.net.nz> — 2004-11-05T08:48:59Z

    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
-------------------------------------------------------------------------

  6. Re: Checking = with timestamp field is slow

    Michael Fuhr <mike@fuhr.org> — 2004-11-05T09:13:06Z

    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/

  7. Re: Checking = with timestamp field is slow

    Chris Browne <cbbrowne@acm.org> — 2004-11-05T12:47:54Z

    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

  8. Re: Checking = with timestamp field is slow

    Michael Fuhr <mike@fuhr.org> — 2004-11-05T17:45:34Z

    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/