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
