Re: Poor performance on seq scan

Laszlo Nagy wrote:
> I made another test. I create a file with the identifiers and names of
> the products:
>
> psql#\o products.txt
> psql#select id,name from product;
>
> Then I can search using grep:
>
> grep "Mug" products.txt | cut -f1 -d\|
>
> There is a huge difference. This command runs within 0.5 seconds. That
> is, at least 76 times faster than the seq scan. It is the same if I
> vacuum, backup and restore the database. I thought that the table is
> stored in one file, and the seq scan will be actually faster than
> grepping the file. Can you please tell me what am I doing wrong? I'm
> not sure if I can increase the performance of a seq scan by adjusting
> the values in postgresql.conf. I do not like the idea of exporting the
> product table periodically into a txt file, and search with grep. :-)

Is there any other columns besides id and name in the table? How big is
products.txt compared to the heap file?

> 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.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com