From: Richard Huxton <dev@archonet.com>
To: Jeroen van Iddekinge <iddekingej@lycos.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problem with pg8.0
Date: Mon, 07 Nov 2005 09:46:38 +0000
Message-ID: <436F227E.3060805@archonet.com> (raw)
In-Reply-To: <436E453F.3060606@lycos.com>
References: <436E453F.3060606@lycos.com>
Jeroen van Iddekinge wrote:
> Hello,
>
> I have some strange performance problems with quering a table.It has
> 5282864, rows and contains the following columns : id
> ,no,id_words,position,senpos and sentence all are integer non null.
>
> Index on :
> * no
> * no,id_words
> * id_words
> * senpos, sentence, "no")
> * d=primary key
>
> "select count(1) from words_in_text" takes 9 seconds to compleet.
Because it's reading through the whole table. See mailing list archives
for discussion of why it doesn't just use an index.
> The query 'select * from words_in_text' takes a verry long time to
> return the first record (more that 2 minutes) why?
A long time for the first row, hardly any time for the others. That's
because it assembles all the rows and returns them at the same time. If
you don't want all the rows at once use a cursor.
> Also the following query behaves strange.
> select * from words_in_text where no <100 order by no;
> explain shows that pg is using sequence scan. When i turn of sequence
> scan, index scan is used and is faster. I have a 'Explain verbose
> analyze' of this query is at the end of the mail.
It's just the "explain analyze" that's needed - the "verbose" gives far
more detail than you'll want at this stage.
> The number of estimated rows is wrong, so I did 'set statistics 1000' on
> column no. After this the estimated number of rows was ok, but pg still
> was using seq scan.
I don't see the correct row estimate - it looks like it's getting it
wrong again to me.
> Can anyone explain why pg is using sequence and not index scan?
There's one of two reasons:
1. It thinks it's going to fetch more rows than it does.
2. It has the relative costs of a seq-scan vs index accesses wrong.
Can you try an "EXPLAIN ANALYZE" of
select * from words_in_text where no < 100 AND no >= 0 order by no;
Substitute whatever lower bound is sensible for "no". Let's see if that
gives the system a clue.
Then, we'll need to look at your other tuning settings. Have you made
any changes to your postgresql.conf settings, in particular those
mentioned here:
http://www.powerpostgresql.com/PerfList
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, iddekingej@lycos.com
Subject: Re: Performance problem with pg8.0
In-Reply-To: <436F227E.3060805@archonet.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