From: Laszlo Nagy <gandalf@designaproduct.biz>
To: Heikki Linnakangas <heikki@enterprisedb.com>
To: pgsql-performance@postgresql.org
Subject: Re: Poor performance on seq scan
Date: Tue, 12 Sep 2006 14:36:55 +0200
Message-ID: <4506A9E7.2060303@designaproduct.biz> (raw)
In-Reply-To: <4506902C.7090407@enterprisedb.com>
References: <450684EC.4090304@designaproduct.biz>
<4506902C.7090407@enterprisedb.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gandalf@designaproduct.biz, heikki@enterprisedb.com
Subject: Re: Poor performance on seq scan
In-Reply-To: <4506A9E7.2060303@designaproduct.biz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox