Re: trouble with (lack of) indexing

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