pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Checking = with timestamp field is slow
8+ messages / 5 participants
[nested] [flat]

* Checking = with timestamp field is slow
@ 2004-11-05 07:16  Antony Paul <antonypaul24@gmail.com>
  0 siblings, 4 replies; 8+ messages in thread

From: Antony Paul @ 2004-11-05 07:16 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Checking = with timestamp field is slow
@ 2004-11-05 08:14  Michael Glaesemann <grzm@myrealbox.com>
  parent: Antony Paul <antonypaul24@gmail.com>
  3 siblings, 1 reply; 8+ messages in thread

From: Michael Glaesemann @ 2004-11-05 08:14 UTC (permalink / raw)
  To: Antony Paul <antonypaul24@gmail.com>; +Cc: pgsql-performance


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




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Checking = with timestamp field is slow
@ 2004-11-05 08:32  Michael Glaesemann <grzm@myrealbox.com>
  parent: Michael Glaesemann <grzm@myrealbox.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Michael Glaesemann @ 2004-11-05 08:32 UTC (permalink / raw)
  To: Michael Glaesemann <grzm@myrealbox.com>; +Cc: Antony Paul <antonypaul24@gmail.com>; pgsql-performance


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'




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Checking = with timestamp field is slow
@ 2004-11-05 08:34  Michael Fuhr <mike@fuhr.org>
  parent: Antony Paul <antonypaul24@gmail.com>
  3 siblings, 0 replies; 8+ messages in thread

From: Michael Fuhr @ 2004-11-05 08:34 UTC (permalink / raw)
  To: Antony Paul <antonypaul24@gmail.com>; +Cc: pgsql-performance

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/



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Checking = with timestamp field is slow
@ 2004-11-05 08:48  Andrew McMillan <andrew@catalyst.net.nz>
  parent: Antony Paul <antonypaul24@gmail.com>
  3 siblings, 0 replies; 8+ messages in thread

From: Andrew McMillan @ 2004-11-05 08:48 UTC (permalink / raw)
  To: Antony Paul <antonypaul24@gmail.com>; +Cc: pgsql-performance

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

Attachments:

  signature.asc (application/pgp-signature, 188B) download

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Checking = with timestamp field is slow
@ 2004-11-05 09:13  Michael Fuhr <mike@fuhr.org>
  parent: Michael Glaesemann <grzm@myrealbox.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Michael Fuhr @ 2004-11-05 09:13 UTC (permalink / raw)
  To: Michael Glaesemann <grzm@myrealbox.com>; +Cc: Antony Paul <antonypaul24@gmail.com>; pgsql-performance

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/



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Checking = with timestamp field is slow
@ 2004-11-05 12:47  Christopher Browne <cbbrowne@acm.org>
  parent: Antony Paul <antonypaul24@gmail.com>
  3 siblings, 1 reply; 8+ messages in thread

From: Christopher Browne @ 2004-11-05 12:47 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Checking = with timestamp field is slow
@ 2004-11-05 17:45  Michael Fuhr <mike@fuhr.org>
  parent: Christopher Browne <cbbrowne@acm.org>
  0 siblings, 0 replies; 8+ messages in thread

From: Michael Fuhr @ 2004-11-05 17:45 UTC (permalink / raw)
  To: Christopher Browne <cbbrowne@acm.org>; +Cc: pgsql-performance

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/



^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2004-11-05 17:45 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-05 07:16 Checking = with timestamp field is slow Antony Paul <antonypaul24@gmail.com>
2004-11-05 08:14 ` Michael Glaesemann <grzm@myrealbox.com>
2004-11-05 08:32   ` Michael Glaesemann <grzm@myrealbox.com>
2004-11-05 09:13     ` Michael Fuhr <mike@fuhr.org>
2004-11-05 08:34 ` Michael Fuhr <mike@fuhr.org>
2004-11-05 08:48 ` Andrew McMillan <andrew@catalyst.net.nz>
2004-11-05 12:47 ` Christopher Browne <cbbrowne@acm.org>
2004-11-05 17:45   ` Michael Fuhr <mike@fuhr.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox