Message-ID: <42ADD8DC.1000001@alteeve.com>
Date: Mon, 13 Jun 2005 15:05:00 -0400
From: Madison Kelly <linux@alteeve.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.7.8) Gecko/20050513 Debian/1.7.8-1
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Bruno Wolff III <bruno@wolff.to>
Subject: Re: Index ot being used
References: <1825.216.209.137.1.1118419819.squirrel@mail.alteeve.com>
	<20050610155535.GO8451@tobias.nordicbet.com>
	<42AC42CB.8050703@alteeve.com> <24636.1118591783@sss.pgh.pa.us>
	<42ACBC95.9020404@alteeve.com> <20050613030001.GB17491@wolff.to>
	<20050613031317.GA18988@wolff.to> <42AD008D.4080600@alteeve.com>
	<20050613035346.GA20703@wolff.to> <42AD0B94.9090508@alteeve.com>
	<20050613132852.GA31142@wolff.to>
	<42ADC77B.7000007@alteeve.com> <22127.1118686403@sss.pgh.pa.us>
In-Reply-To: <22127.1118686403@sss.pgh.pa.us>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Tom Lane wrote:
> Madison Kelly <linux@alteeve.com> writes:
> 
>>Bruno Wolff III wrote:
>>
>>>Please actually try this before changing anything else.
> 
> 
>>   If I follow then I tried it but still got the sequential scan.
> 
> 
> Given the fairly large number of rows being selected, it seems likely
> that the planner thinks this is faster than an indexscan.  It could
> be right, too.  Have you tried "set enable_seqscan = off" to see if
> the index is used then?  If so, is it faster or slower?  Comparing
> EXPLAIN ANALYZE results with enable_seqscan on and off would be useful.

Wow!

With the sequence scan off my query took less than 2sec. When I turned 
it back on the time jumped back up to just under 14sec.


tle-bu=> set enable_seqscan = off; SET
tle-bu=> EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_display 
FROM file_info_7 WHERE file_type='d' ORDER BY file_type ASC, 
file_parent_dir ASC, file_name ASC;
 
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
  Index Scan using file_info_7_display_idx on file_info_7 
(cost=0.00..83171.78 rows=25490 width=119) (actual 
time=141.405..1700.459 rows=25795 loops=1)
    Index Cond: ((file_type)::text = 'd'::text)
  Total runtime: 1851.366 ms
(3 rows)


tle-bu=> set enable_seqscan = on; SET
tle-bu=> EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_display 
FROM file_info_7 WHERE file_type='d' ORDER BY file_type ASC, 
file_parent_dir ASC, file_name ASC;
                                                          QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=14810.92..14874.65 rows=25490 width=119) (actual 
time=13605.185..13728.436 rows=25795 loops=1)
    Sort Key: file_type, file_parent_dir, file_name
    ->  Seq Scan on file_info_7  (cost=0.00..11956.84 rows=25490 
width=119) (actual time=0.048..2018.996 rows=25795 loops=1)
          Filter: ((file_type)::text = 'd'::text)
  Total runtime: 13865.830 ms
(5 rows)

   So the index obiously provides a major performance boost! I just need 
to figure out how to tell the planner how to use it...

Madison

-- 
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU, The Linux Experience; Back Up
http://tle-bu.thelinuxexperience.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-