Re: trouble with (lack of) indexing

From: Søren Boll Overgaard <postgres(at)fork(dot)dk>
To: Scott Marlowe <scott(dot)marlowe(at)ihs(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: trouble with (lack of) indexing
Date: 2002-05-09 22:59:34
Message-ID: 20020509225934.GC20986@treebeard.tolkien.dk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Thu, May 09, 2002 at 03:38:20PM -0600, Scott Marlowe wrote:
> On Thu, 9 May 2002, Søren Boll Overgaard wrote:
>
> > Hello
> >
> > I am currently involved in a rather large project relying heavily on the use of
> > postgresql[1], but we have run into a rather annoying snag.
> > We currently have two databases set up. One for testing, and one for production.
> > Both run on FreeBSD, and perform very well since the last upgrade.
> > However, here is the problem. When executing a certain select statement (shown
> > below) on the production database, we get a sequential table scan (of a rather
> > large table), which causes the machine on which it runs to max out all possible
> > disk I/O. However, when the excact same query is executed on the test
> > database, we get an index scan instead of a sequential one. Obviously,
> > something differes between the two databases, but we simply cannot track down
> > what it is. I would greatly appreciate any input you might be able to offer.
> > Here are the queries and their accompanying query plans:
>
> Couple of quick questions...
>
> Have you run vacuum analyze on both databases?

Just did. Since I am not the one maintaining the databases on a day to day
basis I wasn't sure when it had last been done. I am pretty much the dimwit who
was convinced to go and find a solution to the problem :)
Oddly enough, after vacuum analyzing the two tables involved on the development
database, it now performs sequential scans on bot tables, in excactly the same
way as the production database:

explain SELECT ht.id,hq.ip,hq.id FROM
hostsqueue as hq,hoststests as ht WHERE ht.hostsqueue_id=hq.id;
NOTICE: QUERY PLAN:

Hash Join (cost=776.05..2904.36 rows=84 width=24)
-> Seq Scan on hoststests ht (cost=0.00..2126.84 rows=84 width=8)
-> Hash (cost=776.04..776.04 rows=4 width=16)
-> Seq Scan on hostsqueue hq (cost=0.00..776.04 rows=4 width=16)

EXPLAIN

> What are your settings in the postgresql.conf for cpu_tuple_cost and such?

On both databases:
NOTICE: cpu_tuple_cost is 0.01
NOTICE: cpu_index_tuple_cost is 0.001

Again, any input would be greatly appreciated.
Perhaps we are missing an index somewhere?

--
Søren O. ,''`.
: :' :
public key: finger boll <at> db.debian.org `. `'
`-

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2002-05-09 23:38:06 Re: trouble with (lack of) indexing
Previous Message Trond Eivind =?iso-8859-1?q?Glomsr=F8d?= 2002-05-09 22:57:18 Re: Psql 7.2.1 Regress tests failed on RedHat 7.3