Because there is no MVCC information in the index.
cug
2006/9/12, Piotr Kołaczkowski <[EMAIL PROTECTED]>:
On Tuesday 12 September 2006 12:47, Heikki Linnakangas wrote:
> Laszlo Nagy wrote:
> > I made another test. I create a file with the identifiers and names of
> > the products:
> >
> > psql#
Hi, Piotr,
Piotr Kołaczkowski wrote:
> Why match rows from the heap if ALL required data are in the index itself?
> Why look at the heap at all?
Because the index does not contain any transaction informations, so it
has to look to the heap to find out which of the rows are current.
This is one
On Tuesday 12 September 2006 12:47, Heikki Linnakangas wrote:
> 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"
[EMAIL PROTECTED] wrote:
> This board has Intel chipset. I cannot remember the exact type but it
> was not in the low end category.
> dmesg says:
>
>
> kernel: ad4: 152626MB at ata2-master SATA150
> kernel: ad4: 152627MB at ata3-master SATA150
There have been reported problems with ICH7 on Fr
Lazlo,
>> Thank you for your suggestions. Looks like I need to buy SCSI disks.
>
> Well before you go do that try the areca SATA raid card
Yes, by all means spend $200 and buy the Areca or 3Ware RAID card - it's a
simple switch out of the cables and you should be golden.
Again - you should onl
On 13-Sep-06, at 6:16 AM, Laszlo Nagy wrote:
I have had extremely bad performance historically with onboard
SATA chipsets
on Linux. The one exception has been with the Intel based
chipsets (not the
CPU, the I/O chipset).
This board has Intel chipset. I cannot remember the exact type but
I have had extremely bad performance historically with onboard SATA chipsets
on Linux. The one exception has been with the Intel based chipsets (not the
CPU, the I/O chipset).
This board has Intel chipset. I cannot remember the exact type but it
was not in the low end category.
dmesg says:
Laszlo Nagy wrote:
Craig A. James wrote:
There IS a bug for SATA disk drives in some versions of the Linux
kernel. On a lark I ran some of the I/O tests in this thread, and
much to my surprise discovered my write speed was 6 MB/sec ... ouch!
On an identical machine, different kernel, the w
Lazlo,
On 9/12/06 2:49 PM, "Laszlo Nagy" <[EMAIL PROTECTED]> wrote:
> I'm using FreeBSD, and not just because it dynamically alters the
> priority of long running processes. :-)
Understood.
Linux and FreeBSD often share some driver technology.
I have had extremely bad performance historically
Craig A. James wrote:
There IS a bug for SATA disk drives in some versions of the Linux
kernel. On a lark I ran some of the I/O tests in this thread, and
much to my surprise discovered my write speed was 6 MB/sec ... ouch!
On an identical machine, different kernel, the write speed was 54 MB
tin tout KB/t tps MB/s KB/t tps MB/s us ni sy in id
1 14 128.00 1 0.10 128.00 1 0.10 5 0 94 1 0
0 12 123.98 104 12.56 123.74 104 12.56 8 0 90 2 0
0 12 125.66 128 15.75 125.26 128 15.68 10 0 85 6 0
0 12 124.66 129 15.67 124.39 129 15.64 12 0
Laszlo Nagy <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Why is that showing 85+ percent *system* CPU time??
> I'm sorry, this was really confusing. I don't know what it was -
> probably a background system process, started from cron (?). I retried
> the same query and I got this:
> [ around
Tom Lane wrote:
Why is that showing 85+ percent *system* CPU time?? I could believe a
lot of idle CPU if the query is I/O bound, or a lot of user time if PG
was being a hog about doing the ~~ comparisons (not too unlikely BTW).
I'm sorry, this was really confusing. I don't know what it was -
Laszlo Nagy <[EMAIL PROTECTED]> writes:
> Meanwhile, "iostat 5" gives something like this:
> tin tout KB/t tps MB/s KB/t tps MB/s us ni sy in id
>1 14 128.00 1 0.10 128.00 1 0.10 5 0 94 1 0
>0 12 123.98 104 12.56 123.74 104 12.56 8 0 90 2 0
>0 12 125.66
@postgresql.org
Subject:Re: [PERFORM] Poor performance on seq scan
Luke Lonergan írta:
> Lazlo,
>
>
>> Meanwhile, "iostat 5" gives something like this:
>>
>> tin tout KB/t tps MB/s KB/t tps MB/s us ni sy in id
>>1 14 128.00 1 0.10 12
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Are you saying that an indexscan "Filter" only acts after getting the
> heap tuple?
Correct.
> If that's the case, then there's room for optimization
> here, namely if the affected column is part of the index key, then we
> could do the filtering befor
Guillaume Cottenceau wrote:
Laszlo Nagy writes:
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 s
Alvaro Herrera wrote:
Are you saying that an indexscan "Filter" only acts after getting the
heap tuple? If that's the case, then there's room for optimization
here, namely if the affected column is part of the index key, then we
could do the filtering before fetching the heap tuple.
That's righ
Heikki Linnakangas wrote:
> Guillaume Cottenceau wrote:
> >Laszlo Nagy writes:
> >
> >>>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 rand
Laszlo Nagy writes:
> > 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
Laszlo Nagy writes:
> This is a mirrored disk with two SATA disks. In theory, writing should
> be slower than reading. Is this a hardware problem? Or is it that
> "sync" did not do the sync?
SATA disks are supposed to be capable of lying to pg's fsync (pg
asking the kernel to synchronize a write
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 prod
Luke Lonergan írta:
Lazlo,
Meanwhile, "iostat 5" gives something like this:
tin tout KB/t tps MB/s KB/t tps MB/s us ni sy in id
1 14 128.00 1 0.10 128.00 1 0.10 5 0 94 1 0
0 12 123.98 104 12.56 123.74 104 12.56 8 0 90 2 0
This is your problem. Do t
Lazlo,
> Meanwhile, "iostat 5" gives something like this:
>
> tin tout KB/t tps MB/s KB/t tps MB/s us ni sy in id
>1 14 128.00 1 0.10 128.00 1 0.10 5 0 94 1 0
>0 12 123.98 104 12.56 123.74 104 12.56 8 0 90 2 0
This is your problem. Do the following and repo
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
25 matches
Mail list logo
