Re: Poor performance on seq scan

From: Laszlo Nagy <gandalf(at)designaproduct(dot)biz>
To: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Poor performance on seq scan
Date: 2006-09-12 12:36:55
Message-ID: 4506A9E7.2060303@designaproduct.biz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Guillaume Cottenceau 2006-09-12 12:46:18 Re: Poor performance on seq scan
Previous Message Guillaume Cottenceau 2006-09-12 12:36:22 Re: Poor performance on seq scan