From: Charlie Savage <cfis@interserv.com>
Subject: Re: Index Scan Costs versus Sort
Date: Thu, 10 Nov 2005 17:11:48 -0700
Organization: Hub.Org Networking Services
Lines: 33
Message-ID: <dl0nk3$9lg$1@news.hub.org>
References: <dkvu9b$305t$1@news.hub.org> <10461.1131645881@sss.pgh.pa.us>
	<437398E3.2020900@interserv.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Thunderbird 1.5 (Windows/20051025)
In-Reply-To: <437398E3.2020900@interserv.com>
To: pgsql-performance@postgresql.org

Following up with some additional information.

The machine has 1Gb physical RAM.  When I run the query (with sort and 
seqscan enabled), top reports (numbers are fairly consistent):

Mem:   1,032,972k total, 1,019,516k used, 13,412k free, 17,132k buffers

Swap:  2,032,140k total, 17,592k used, 2,014,548k free, 742,636k cached

The postmaster process is using 34.7% of RAM - 359m virt, 349 res, 319m. 
  No other process is using more than 2% of the memory.

 From vmstat:

r  b      swpd     free      buff        cache
1  0     17592    13568     17056        743676

vmstat also shows no swapping going on.

Note that I have part of the database, for just Colorado, on my Windows 
XP laptop (table size for completechain table in this case is 1Gb versus 
18Gb for the whole US) for development purposes.  I see the same 
behavior on it, which is a Dell D6100 laptop with 1Gb, running 8.1, and 
a default postgres.conf file with three changes (shared_buffers set to 
7000, and work_mem set to 8192, effective_cache_size 2500).

Out of curiosity, how much longer would an index_scan expected to be 
versus a seq scan?  I was under the impression it would be about a facto 
of 4, or is that not usually the case?

Thanks for the help,

Charlie