Re: [PERFORM] Too slow querying a table of 15 million records

[John A Meinel]

Tobias Brox wrote:


[EMAIL PROTECTED] - Tue at 08:33:58PM +0200]



I use FreeBSD 4.11 with PostGreSQL 7.3.8.



(...)



database= explain select date_trunc('hour', time),count(*) as total from
test where p1=53 and time  now() - interval '24 hours' group by
date_trunc order by date_trunc ;




I haven't looked through all your email yet, but this phenomena have been up
at the list a couple of times.  Try replacing now() - interval '24 hours'
with a fixed time stamp, and see if it helps.

pg7 will plan the query without knowledge of what now() - interval '24
hours' will compute to.  This should be fixed in pg8.




The grandparent was a mailing list double send. Notice the date is 1
week ago. It has already been answered (though your answer is still
correct).

John
=:-



signature.asc
Description: OpenPGP digital signature

Re: [PERFORM] Too slow querying a table of 15 million records

[Christopher Kings-Lynne]

database= explain select date_trunc('hour', time),count(*) as total from
test where p1=53 and time  now() - interval '24 hours' group by
date_trunc order by date_trunc ;


Try going:

time  '2005-06-28 15:34:00'

ie. put in the time 24 hours ago as a literal constant.

Chris


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] Too slow querying a table of 15 million records

[PFC]

database= explain select date_trunc('hour', time),count(*) as total from
test where p1=53 and time  now() - interval '24 hours' group by
date_trunc order by date_trunc ;


	1. Use CURRENT_TIMESTAMP (which is considered a constant by the planner)  
instead of now()
	2. Create a multicolumn index on (p1,time) or (time,p1) whichever works  
better


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [PERFORM] Too slow querying a table of 15 million records

[Tom Lane]

PFC [EMAIL PROTECTED] writes:
   1. Use CURRENT_TIMESTAMP (which is considered a constant by the 
 planner)  
 instead of now()

Oh?

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])