Re: [GENERAL] Question about a query plan

[Thomas O'Connell]

On Sep 16, 2005, at 9:18 AM, Bill Moseley wrote:


First question is why the planner is not using an index scan when I
use now() or CURRENT_TIMESTAMP?


It also used to be the case (pre-8.0; I couldn't find in the release  
notes whether this was an 8.0 or 8.1 fix) that now() and  
CURRENT_TIMESTAMP were not indexable, I think because of mutability.


For older versions of postgres, it's recommended that you determine  
the time in the client and use constant data in your query.


--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)
---(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: [GENERAL] Ranking Results

[Thomas O'Connell]

On Sep 13, 2005, at 3:07 PM, Craig wrote:  Hi   I have a query that is grouping by 3 fields and returning a sum of another field. I would also like a further field to be returned that shows a ranking for the records based on the sum field. This should really only be a numbering of the rows returned, since I will do the ordering in an ORDER BY clause   A Simplistic example:   SELECT f1, f2, f3, sum(f4) FROM TableA GROUP BY f1, f2, f3 ORDER BY f2,f1,f3   Now I would like to add another field that simply return 1,2,3,4,5,etc.. for each row that is returned.   Can this be done?   Thanks CraigIf you're talking about row numbering rather than ranking, you can try a temporary sequence:http://archives.postgresql.org/pgsql-general/2005-02/msg01412.php--Thomas F. O'ConnellCo-Founder, Information ArchitectSitening, LLCStrategic Open Source: Open Your i™http://www.sitening.com/110 30th Avenue North, Suite 6Nashville, TN 37203-6320615-469-5150615-469-5151 (fax)