Message-ID: <4506A9E7.2060303@designaproduct.biz>
Date: Tue, 12 Sep 2006 14:36:55 +0200
From: Laszlo Nagy <gandalf@designaproduct.biz>
User-Agent: Thunderbird 1.5.0.5 (Windows/20060719)
MIME-Version: 1.0
To: Heikki Linnakangas <heikki@enterprisedb.com>,
	pgsql-performance@postgresql.org
Subject: Re: Poor performance on seq scan
References: <450684EC.4090304@designaproduct.biz>
	<4506902C.7090407@enterprisedb.com>
In-Reply-To: <4506902C.7090407@enterprisedb.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Heikki Linnakangas wrote:
>
> Is there any other columns besides id and name in the table? How big 
> is products.txt compared to the heap file?
Yes, many other columns. The products.txt is only 59MB. It is similar to 
the size of the index size (66MB).
>
>> Another question: I have a btree index on product(name). It contains 
>> all product names and the identifiers of the products. Wouldn't it be 
>> easier to seq scan the index instead of seq scan the table? The index 
>> is only 66MB, the table is 1123MB.
>
> Probably, but PostgreSQL doesn't know how to do that. Even if it did, 
> it depends on how many matches there is. If you scan the index and 
> then fetch the matching rows from the heap, you're doing random I/O to 
> the heap. That becomes slower than scanning the heap sequentially if 
> you're going to get more than a few hits.
I have 700 000 rows in the table, and usually there are less than 500 
hits. So probably using a "seq index scan" would be faster. :-) Now I 
also tried this:

create table test(id int8 not null primary key, name text);
insert into test select id,name from product;

And then:

zeusd1=> explain analyze select id,name from test where name like 
'%Tiffany%';
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..26559.62 rows=79 width=40) (actual 
time=36.595..890.903 rows=117 loops=1)
   Filter: (name ~~ '%Tiffany%'::text)
 Total runtime: 891.063 ms
(3 rows)

But this might be coming from the disk cache. Thank you for your 
comments. We are making progress.

   Laszlo