From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Laszlo Nagy <gandalf@designaproduct.biz>
Cc: pgsql-performance@postgresql.org
Subject: Re: Poor performance on seq scan
Date: Tue, 12 Sep 2006 11:47:08 +0100
Message-ID: <4506902C.7090407@enterprisedb.com> (raw)
In-Reply-To: <450684EC.4090304@designaproduct.biz>
References: <450684EC.4090304@designaproduct.biz>
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
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: heikki@enterprisedb.com, gandalf@designaproduct.biz
Subject: Re: Poor performance on seq scan
In-Reply-To: <4506902C.7090407@enterprisedb.com>
* 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